Thursday, July 26, 2012

Neat SSMS trick

One of the developers asked me this morning if there was a way to do a replacement in Management Studio where you look for a constant string at the beginning of the line, followed by arbitrary stuff, and essentially append something else to the end of it. The use case was he had a stored procedure that was called in a bunch of places with different parameter orders that he needed to add a couple of more parameters to. Both he and I knew that SSMS has support for regular expressions, but didn't immediately know how to pull this off. Surprisingly, it's pretty easy. In the "Find what:" box, you put in something like "exec sp_myProc{.*}" and in the "Replace with:" box you put in "exec sp_myProc\1 @newParm1='blue', @newParm2='fragrant'". The "\1" refers to the first capture group within the regular expression. I found the regex reference for Visual Studio to be most instructive. You can find that here. Have fun!

Wednesday, July 18, 2012

Anti-pattern: identity insert

Something that I often see when a new table that holds reference data is added to a database is something like:
CREATE TABLE [myApp].[Status] (
    [StatusId] INT IDENTITY PRIMARY KEY CLUSTERED ,
    [StatusName] NVARCHAR(20) NOT NULL
)
GO
SET IDENTITY_INSERT [myApp].[Status] ON
GO
INSERT INTO [myApp].[Status] ([StatusId], [StatusName])
VALUES 
    (1, 'Submitted'),
    (2, 'Accepted'),
    (3, 'Denied')
SET IDENTITY_INSERT [Status] OFF
GO
The typical justification that I hear is that the ID values need to be the same across environments (i.e. development, QA, production). I say that if that's the case, don't define the table with an identity column. Identity columns are for when you don't care what value that column takes for a given row. So, if every insert to a given table looks like the above, consider dropping the identity property from the column and save yourself the hassle of dealing with IDENTITY_INSERT.