It’s 2016. So why are we still dealing with T-SQL code and design patterns that were designed 7 versions ago?

In the 15 years I have been using databases professionally, we’re still dealing with:

• Peoples’ names are split into first name, last name and middle initial fields. Ignoring that this falls afoul of several of the myths programmers believe about names, the first name column was defined as CHAR(10) in a standard installation. How many characters are in the name Christopher (hint: I had to take off a shoe to count them all)?
• Other arbitrarily short column sizes which cause problems as the system scales out in usage. For example, an event ID field that’s 8 characters: 2 letters and a 6-digit number which is used as a sequence. Guess what happens when you hit the millionth event in that sequence.
• Processes originally developed as transactions (for good reasons), but not designed in such a way that they scale to today’s demands.
• NOLOCK hints everywhere. It’s even in newly-developed code for this application.
• Cursors used anytime a set of records has to be updated with a small bit of conditional logic built in. A set-based operation with appropriate CASE statements would work much better.

The primary system I deal with on a daily basis was originally developed as a DOS application and several of the above examples are drawn from it. Looking at the core tables and columns, it’s easy to identify those that began life in those early days - they all have 8-character names. Time moved on and the system grew and evolved. DOS to Windows. Windows to the web. But the database, and the practices and patterns used in the database, haven’t come along for the ride.

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:

This week’s #sqlnewblogger posts!

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.

This week’s #sqlnewblogger posts!

If you need to move data from one table into a new table, or even tables in a database into another database, the Import/Export Wizard in SQL Server Management Studio looks pretty tempting. Set up a source & destination, click a few buttons, kick back with a cup of tea and watch the progress bars, right?

It turns out that the wizard just isn’t as smart as it may seem. If you’re not careful, you won’t get what you’re expecting. Let’s check it out.

Ed Leighton-Dick has renewed his New Blogger Challenge this month. Here are all (I think) the posts for this week after Ed posted his announcement. If I’ve missed any, please let me know and I’ll update.

Spend any time around a 4 year old, and you will inevitably find yourself involved in a conversation which evolves into this:

• Please do this thing
• Why?
• Reasonable answer
• Why?
• Restatement of reasonable answer
• Why?
• Shorter, more frustrated restatement of reasonable answer
• Why?
• Because that’s what has to be done
• Why?
• Because
• Why?
• I give up. Go ask your other parent

It’s a simple, but powerful and important question. The trouble is that when it’s a 4 year old asking it, in a lot of cases they can’t understand the answer. More often, they aren’t interested in understanding it.

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!

Consider this the outtakes from my previous post about speaking at SQL Saturday.

It took a while for me to build up the courage to finally get up in the front of a room at SQL Saturday. As I mentioned in my prior post, I did quite a bit of studying of other peoples’ sessions, read peoples’ studies of other peoples’ sessions (Grant Fritchey’s “Speaker of the Month” series) and talked to a few people at the speakers’ dinner. Here are a few of the key things I learned which put me more at ease.