(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…