Created
March 6, 2015 10:03
-
-
Save papsl/a2fc46b42f712558db4b to your computer and use it in GitHub Desktop.
Example of improper creation of temporary table with Primary key (Microsoft SQL Server)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 1.) Problematic way | |
-- This example will fail on a second execution (when first table not dropped, even in case it is executed in different connection or user context). | |
-- PK_SomeTable will already exist in tempdb | |
CREATE TABLE #SomeTable | |
( | |
Id INT IDENTITY(1, 1) , | |
[Name] [varchar](255) NULL , | |
CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED ( [Id] ASC ) | |
) | |
-- Output: | |
/* | |
Msg 2714, Level 16, State 5, Line 3 | |
There is already an object named 'PK_SomeTable' in the database. | |
Msg 1750, Level 16, State 0, Line 3 | |
Could not create constraint. See previous errors. | |
*/ | |
-- 2.) safer way | |
-- Remind: always drop a temporary table when no longer needed | |
CREATE TABLE #SomeTable | |
( | |
Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, | |
[Name] [varchar](255) NULL , | |
CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED ( [Id] ASC ) | |
) | |
/* | |
Do some work | |
*/ | |
DROP TABLE #SomeTable |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment