Just a quick little post today on a little “gotcha” that may getcha.
Let’s say you’re creating a stored procedure that needs to create a #temp table that has a PRIMARY KEY. You will want to be careful about how you create this #temp table and here is why:
Open SQL Server Management Studio and run the following command:
SELECT GETDATE() AS 'TheDate' INTO #TMP1 ALTER TABLE #TMP1 ADD CONSTRAINT PK_TMP1 PRIMARY KEY CLUSTERED (TheDate) GO
Now if you open a new Management Studio query window and run the exact same code (simulating what a stored procedure would do) what do you get?
Because you created your PRIMARY KEY constraint after the fact (and had to give it a name) you lose the uniqueness of the PRIMARY KEY name.
SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME like '%#TMP1%'
So how do you get around this?
CREATE TABLE #TMP1(TheDate DATETIME PRIMARY KEY) GO SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME like '%#TMP1%'
and if you run this in 3 different query windows….
Now I used Primary Keys as my example but this same “gotcha” applies to constraints in general. If you were to add a check constraint that validated “TheDate” and gave that check constraint a name you would run into the same “cannot create constraint… duplicate object” error.
Moral of the story: When dealing with temporary tables in reuseable code (like stored procedures) don’t name your constraints.
And now you know….
Enjoy!! (Follow me on Twitter: @BenchmarkIT)