Skip to content

Instantly share code, notes, and snippets.

@papsl
Created March 6, 2015 10:03
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save papsl/a2fc46b42f712558db4b to your computer and use it in GitHub Desktop.
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)
-- 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