While doing some testing on using NEWSEQUENTIALID() I came across an interesting issue that may come to bite us in the future. I was making a proof of concept of a basic script using temporary tables, something like this (I’ve taken out the unnecessary bits);

IF OBJECT_ID('tempdb..#NewIDTest') IS NOT NULL DROP TABLE #NewIDTest
CREATE TABLE #NewIDTest (RowNum int, ID uniqueidentifier)

ALTER TABLE #NewIDTest ADD CONSTRAINT DF_NewIDTest_ID DEFAULT (newsequentialid()) FOR ID

The whole test script worked just fine, until I sent it to a colleague to help him understand the concept. When he attempted to run the script he was met with the following error;

Msg 2714, Level 16, State 5, Line 4
There is already an object named ‘DF_NewIDTest_ID’ in the database.
Msg 1750, Level 16, State 0, Line 4
Could not create constraint or index. See previous errors.

Turns out that even on (local) temporary tables, constraint names still need to be unique. I ran the first line of the statement above to drop the table and it worked just fine for him. Something to keep in mind for future.