T-Sql

This month’s T-SQL Tuesday is hosted by Bert Wagner and he asks us to write about code we’ve written that we would hate to live without.

First off, “hate” is a pretty strong word so let’s go with “code you couldn’t bear to live without”. The first bit of code I couldn’t live without is reviled in some circles. More often it’s misunderstood and lamented, or…well, I’ll just show it to you.

Triggers can be really useful in your database but you have to be careful with them. We often use them to record history to a separate table (at least, until we can implement temporal tables) or keep track of when a record was last updated. It’s usually implemented via an AFTER UPDATE trigger.

Several times a day, I’m alerted by our monitoring suite that two processes were deadlocked. This is a (mostly) normal event, but one that we’d like to avoid if we can. More than a few times, I observed this pattern:

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. Digging into the source code for the page and SQL Profiler (yes, I know Extended Events are a thing), we were able to isolate the query.

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. In this case, I was attempting to JOIN two tables on fields that should have been INTs, but in a very small number of cases one side was using a comma-delimited string. The user told me that someone else had done these updates in the past and didn’t encounter the problem I was having (so I knew that it was something i was doing “wrong”), but given that it was only a handful of broken updates she was OK with manually doing the updates (we were scripting it because we were updating potentially tens of thousands of records).

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. Everyone wins!

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). Time goes on, and a change in policy comes up which requires that I change the default value of typename to Unknown. To do this, I have to drop the constraint and re-create it. To find the name of the constraint, I can either ask sp_help, or run this query:

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.0.0.1
  ,'Dave'
  ,'Home'
  ,'Pd=2015Q2'
  ,'883666b1-99be-48c8-bf59-5a5739bc7d1d'
);

1
2
3
4

UPDATE PageLogs
  SET ResponseTime = GETDATE()
  WHERE
    SessionId = '883666b1-99be-48c8-bf59-5a5739bc7d1d';

You set up an hourly job to delete any logs older than 2 weeks (just to prevent information overload) and you call it a day. Each morning, you run a report to look at the previous day’s performance, watch the trends over the past week or so, and you’re pretty happy with things. Pages are loading in a fraction of a second, according to the logs. People find the application useful, word spreads around the office, and adoption takes off. The project is a success!