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

Get reliable execution results

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

and:
DBCC FREEPROCCACHE
(Empty SP cache)

torsdag 6 december 2007

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

söndag 2 december 2007

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