In the course of testing a major upgrade, one of my users in Accounting happened upon a problem with one of her scenarios. The web app we were working on had a habit of “locking up” on people when loading some pages; in most cases, it was because the server was pushing a huge HTML table to the client, and most web browsers struggle when faced with several megabytes of markup and thousands of rows in a single table.
For years I’ve looked at various queries online in sample code, diagnostic queries using DMVs, and the like and seen CROSS APPLY in the FROM clauses. But I’ve never really managed to comprehend what it was for or how it worked because I never saw a case where it was directly applied to something I was doing.
Finally, this week I had a breakthrough. I was working on updating a bunch of data but it was breaking on a small subset of that data.
This post is in support of Tim Ford’s (blog|twitter) #iwanttohelp challenge. And also written because this has burned me twice in the past 3 months and by blogging about it, hopefully it’ll stick in my mind.
Setup I’ve recently been doing a bunch of work with stored procedures, trying to improve performance that’s been suffering due to suboptimal queries. Some of this tuning has resulted in creating temporary tables. After making my changes and running the procedures in SSMS, everything looked good - data’s correct, performance metrics are all improved.
Something in SQL Server that isn’t always obvious to beginners is that when you create a default value for a column on a table, SQL Server creates a constraint (much like a primary or foreign key). All constraints must have a name, and if one isn’t specified SQL Server will generate one for you. For example:
1 2 3 4 5 CREATE TABLE [dbo].[point_types] ( [typeid] [int] NOT NULL DEFAULT(NEXT VALUE FOR [pointtypeid]) ,[typename] [nvarchar](30) NOT NULL DEFAULT 'Unspecified' ,CONSTRAINT [PK_PointType] PRIMARY KEY CLUSTERED ([typeid] ASC) ) We’ve got a simple table here and both fields have a default value set (the primary key’s value is generated from a sequence object, pointtypeid).
I have a situation where I need to retrieve the data in an encrypted column from, but don’t want to give all my users access to the symmetric key used to encrypt that column. The data is of the sort where it’s important for the application to produce the required output, but if a user runs the stored procedure to see what the application is getting from it, it’s not critical that they see this one field.
So you’ve decided that your new web application needs to record some page load time metrics so you can keep tabs on performance. Terrific! You set up a couple page load/complete functions to write to a logging table when a page request comes in, and then update the record when it finishes loading.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 INSERT INTO PageLogs ( RequestTime ,ResponseTime ,RemoteIP ,UserName ,PageId ,Parameters ,SessionId ) VALUES ( GETDATE() ,NULL ,127.