I have been reading SQL Server Secret Diary by Vijayan and Kirsh.
Here are a few snipets of performance wisdom from them.
It says where multiple variables are set for Microsoft SQL server the a SELECT statement is faster than several SET statements e.g
SET @employeeid = @employeeid+1, @a=2
Is faster than
SET @employeeid = @employeeid+1,
Here is another tip when if you want to count the rows in a table quickly. Rather than just
select count(*) from dbo.tablename
Which would do a complete table or cluster index scan. Try this to extract the data from sysindexes
SELECT rows FROM sysindexes
WHERE Id = OBJECT_ID('dbo.tablename') AND indid < 2
To help speed up the performance of a stored procedure remember to add.
SET NOCOUNT ON
This avoids sending row count information for every statement