I saw Mark Rosenberg speak on optimizing stored procedures this month at Austin Code Camp.

Things suggested by Mark Rosenberg:

• Try to use table variables instead of temp tables. Table variables are just like other declared variables save that their type is TABLE. This has this example:
  DECLARE @ProductTotals TABLE
  {
     ProductID int,
     Revenue money
  }
  When you do encounter temp tables: Try to pull temp tables out of code into real tables, and/or make SQL make temp tables and then fill them instead of using a SELECT INTO approach for doing both acts at once.
• When MSSQL Server runs a query it must first plan how to do so. The plan is called "query plan." It is stored within a stored procedure, but does not exist premade in dynamic SQL (SQL found inline in code and SQL which perhaps varies in shape from query to query based upon conditions in code). Since SQL Server 2000, if dynamic SQL of a particular shape is run enough times then its query plan will become stored, however as the query plan must be drummed up otherwise it is obvious that a stored procedure will perform better than a dynamic query holding the same query logic as it does not have to drum up a query plan on the fly.
• Use SET NOCOUNT ON in a sproc to keep MSSQL from returning a count of records from a select statement. This will make a sproc faster. One does not have to call SET NOCOUNT OFF explicitly to achieve the opposite effect. The opposite effect, in which a count of records is returned, happens by default.
• Using a qualified name for a table called in a sproc will make the sproc speedier as MSSQL Server will not have to figure out where to look.
• If returning only a single record, just return the contents as output parameters while using the return value for an error code.
• Don't start the name of a sproc with "sp_" underscore as Microsoft uses this convention for its own stored procedures and will crawl through its own stored procedures first in attempting to find your stored procedure should you use this convention.
• Use sp_executesql instead of EXECUTE to run a stored procedure. This speaks to the difference.
• sp stands for special object!
• There is a distinction between data manipulation logic and data definition logic. The latter is of making and modifying tables and columns and the former is for operations against the tables and columns crafted in data definition logic. Do not put Data Definition Language (DDL) in a stored procedure.
• Use WITH RECOMPILE in a stored procedure to escape using a query plan. There is a way to use SQL Server Profiler to view a list of stored procedures and see which ones are using query plans.
• Use a WHERE clause before a HAVING clause to filter records before aggregation.
• UNION has DISTINCT baked into it. Using UNION ALL instead will give you better performance as it does not come with this extra thing.
• When matching on LIKE '%foo%' a query will have to look at every possible string value to parse every character inwards from the beginning in attempt to find "foo" somewhere downstream from the beginning. However, when matching on LIKE 'foo%' a query will only have to look at strings starting with the letter f. If possible, avoid the leading percent symbol in LIKE matching.
• Mark suggested trying to get out of the pain of swapping between CREATE and ALTER when running stored procedure code by doing some if-exists sanity checking. This has some potential examples of that sort of thing.