söndag 23 december 2007

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)
)

Inga kommentarer:

Skicka en kommentar