Inappropriate Indexes, and Programming Your Way Around Them

(This article should probably be called “Inappropriate Primary Keys…”, but that wouldn’t have the assonance…)

Seeing various questions on AskSSC about using GUIDs as indexes, including GUIDs which are actually stored in Varchar(255) fields, reminded me of this little vignette from a previous place…

A learner DBA (not me, I swear it) was writing a Server Monitoring system in SQL Server.  He created a Log table to hold messages, with the following columns:

CREATE TABLE [dbo].[Log] (
[Id] [datetime] NOT NULL,
[LogType] [char](3) NOT NULL,
[ObjectId] [smallint] NOT NULL,
[InfoText] [varchar](512) NULL)

All well and good (apart from the dubious choice of name, but hey…)

Except – wait.  The “ID” field is of “datetime” type.  This might not be a problem, but what do I see here buried in the table definition?

CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
( [Id] ASC )
WITH (
PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY]

Hmm.  Well, this could all be OK.  Except for one tiny little detail – the SP that inserts entries into the Log table is called more often than once every 300th of a second.

What would you do in this situation?  Change the PK?  Or would you, perhaps, rewrite the routine that inserts data into the Log table to include the following:

DECLARE @SQLError INT
LoopMarker:
BEGIN TRY
INSERT INTO [dbo].[Log] (LogType, ObjectId, InfoText)
VALUES (@LogType, @ObjectId, @InfoText)
RETURN
END TRY
BEGIN CATCH
SET @SQLError = @@ERROR
IF @SQLError = 2627
-- Violation of PRIMARY KEY constraint 'PK_Log'.
-- Cannot insert duplicate key in object 'dbo.Log'.
GOTO LoopMarker
END CATCH
GOTO LoopMarker

In this piece of code’s defence, it does do the job.  But it’s not the job that it should be doing, and it does chew up resources waiting for the next 300th of a second to arrive.

I never did get round to writing code to trap the number of times the error handler was called in an average day.

It’s been a long time since I saw that code, and just looking at it now makes me want to drown the memory in beer.  I may be some time…

Advertisements
This entry was posted in SQLServerPedia Syndication, Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s