torsdag 22 november 2007

Temporary tables and primary keys!

Use primary keys without names for temporary tables!

create table #test
(
id int,
data int,
primary key clustered (id)
)

drop table #test


Naming the primary key causes issues because Sql Server can complain that the primary key already exists (funny for a temporary table).

Try this example in two different sessions:

Session 1:
create table #test
(
id int,
data int,
constraint PK_test primary key clustered (id)
)

Session 2:
create table #test
(
id int,
data int,
constraint PK_test primary key clustered (id)
)

Without a name for the PK it will work though.

So this is.... what!?

A small set of useful notes for a particular Sql Server developer (me).

The point?

1. To have it easily accessible.
2. To possibly get some comments on the issues, best case scenario is to get better solutions as comments than the ones I find myself.
3. To spread some knowledge (who am I kidding?)