Really basic stuff...

Remember that:
@Variable like Column
Is not the same as:
Column like @Variable

Really basic, but sometimes even basic stuff can cause a mess.

Comma separated string of values...

/*
Note that if the values that are supposed to be comma separated can contain
comma signs it is a good idea to replace those with something else before
generating the list
*/

declare @list nvarchar(max)
set @list = ''
select @list = @list + ',' + replace(ColumnName,',','.') from TableName
set @list = substring( @list , 2, 10000000 )
select @list

--Simpler versions when handling id's
--Quite quick
declare @list varchar(max)
set @list = ''
select @list = @list + ',' + convert(varchar(10), ColumnName) from TableName
set @list = substring( @list , 2, 10000000 )
select @list

--Much quicker version (notable only when generating very long lists)
declare @list varchar(max)
set @list = (select ',' + convert(varchar(10), ColumnName) from TableName for xml path(''))
set @list = substring(@list , 2, 10000000 )
select @list

Reseeding those identity fields...

DBCC CHECKIDENT ( table_name, [reseed / noreseed], [new_seed_value])

DBCC CHECKIDENT ( 'dbo.tTable', RESEED, 14000000)

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