Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Naming Constraints on Temp Tables
Published: Nov 06, 2015
This post comes about due to my spending an annoying large amount of time on a code failure that turned out to be utterly infuriating, but ultimately interesting as well.

It involves the use of named constraints within temp tables.

Essentially what was happening was that someone had some code and was, quite understandably, following their normal guidelines for naming a constraint on a table when they created it.

The problem was that this code was called with a very high frequency and suddenly the application started to error.

The reason was simply down to the convention of naming your constraints.

Now, everyone knows (and if they don’t then they should), that it’s good practice to name your constraints whether they be keys, indexes etc as follows:

create table constraintNameTable
id int identity(1, 1) not null,
data varchar(100) not null,
miscValue varchar(20) not null,
constraint pk_constraintNameTable primary key clustered(id)

create table constraintNameTable2
id int identity(1, 1) not null,
data varchar(100) not null,
miscValue varchar(20) not null
alter table constraintNameTable2
add constraint pk_constraintNameTable2 primary key clustered(id)

So why wouldn’t you do this in a temporary table? Well it’s actually down to the fact that although your temp table name is uniquified when placed into tempDB:

create table #temp(id int)

select name
from tempDB.dbo.sysobjects
where name like '#temp%'

drop table #temp

SQL Server does NOT do the same process with the constraint but actually stores it as per the name you allocated.

Therefore if you create another temp table with the same constraint then you’re likely to get an error.

Run the following code in SSMS:

create table #temp
id int,
constraint pk_temp primary key clustered(id)

Now, in a new window run exactly the same code and you should see the following:

As you can see, although SQL Server would have happily created the table for you because it uniquifies the name, it hits an immediate problem with the constraint.

To get around this, simply allow SQL Server to generate the name for you. I do stress that you should only really do this for temp table constraints because I think all constraints on normal tables should be sensibly named.

For a primary key:

create table #temp
id int,
primary key clustered(id)

Or a clustered index which isn’t the primary key:

create table #temp
id int,
unique clustered(id)

Just something to note in case you come across the same issue.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.