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

Get reliable execution results

To get komparable results remember to execute:
DBCC DROPCLEANBUFFERS
(Empty data cache)

and:
DBCC FREEPROCCACHE
(Empty SP cache)

Just a reminder!

Don't forget that SQL server can work as your simple calculator or runtime environment to test out small pieces of code.

As calculator:

print 135 * 3.24

Prints:
437.40

Or figuring out if your idea of calculating factorial is ok:

declare @l int
set @l = 0

while @l <= 100
begin
 declare @res float
 set @res = 1.0

 declare @i int
 set @i = 1
 while @i <= @l
 begin
  set @res = @res * @i
  set @i = @i + 1
 end
 print str(@l)+'! = ' + convert(varchar(50),@res)
 set @l = @l + 1
end


Prints:
         0! = 1
         1! = 1
         2! = 2
         3! = 6
         4! = 24
         5! = 120
         ...

Batch aborting errors

Some errors are so serious that they can't be handled like normal errors. Example:

begin try
   -- Do something (the task you want to perform)
end try
begin catch
   -- Do something (log error, rollback transaction etc)
end catch

You might think that any error will be caught in the catch-clause allowing you to log the error and rollback any open transaction. This is WRONG!!! Some errors are batch aborting, this means that immediatly when they are encountered they stop the processing, rollback any transaction and close the connection. An example of a batch aborting error is to access an object that doesn't exist.

This shouldn't be an issue since SQL Server will do necessary cleaning, only problem is that your fancy code for logging the error will not be executed.

I've seen one example when SQL Server for some reason doesn't manage to abort the transaction, this was a big problem. Taking a backup and restore it would solve this problem, strange... no errors where reported by DBCC CHECKDB but the restore helped.

More information on:
http://www.sommarskog.se/error-handling-I.html

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