Temporary tables again!

I seem to find myself optimizing lots and lots of old Sql Server 2k code. Often these procedures are relying heavily on temporary tables.

Adding a clustered index to a temporary can be a real boost but sometimes other tools are needed. Indexes on temporary tables are possible by adding unique indexes during table definition. Unless the values are already unique this requires adding a duplicate key breaker example:

create table #temp
(
   x int,
   y int
)

Transforms into:

create table #temp
(
   id int identity(1,1) not null,
   x int,
   y int,
   primary key clustered (id),
   unique (x,id),
   unique (y,id)
)