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.