Tips for Advanced MS SQL Server Developers

Hi Guys, Here, some basic tips about How to write optimized Stored Procedure.

• Its always better to use table owner in query like

  SELECT col1,col2….coln FROM dbo.Tablename

• Due to this, SQL query optimizer does not have to decide whether to retrieve from dbo.Tablename or other owner’s table and avoids recompilation.
• Always Use TRUNCATE TABLE statement instead of DELETE clause if its needed to delete all rows from a table. Its more faster then DELETE statement without any conditions. TRUNCATE TABLE always releases all the space occupied by that table’s data and indexes, without logging the individual row deletes.
• Don’t use the “sp_” prefix in a stored procedure name as the “sp_” prefix is reserved for system stored procedures. Any stored procedure that has the “sp_” prefix will cause an extra lookup in the MASTER database If a stored procedure uses same name in both the user database and a system database, the stored procedure in the user database will execute but first it will find the procedure in resource database and then the user database (for SQL server 2005/2008/2012) hence causing an extra burden to the server.
• To get number of rows from table never use “SELECT COUNT(*)” statement, There is other way for much faster to achieve this goal as

  SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘Table_Name’) AND indid <2

• Include “SET NOCOUNT ON” statement in your stored procedures to greatly reduce network traffic.
• Try to use table variables instead of Temporary Tables – Temp tables can cause stored procedures to recompile. (From SQL 2005 onwards, using temporary table not always causing recompilations. But adding rows to temporary tables may cause recompilations). But table variables were designed specifically to guard against stored procedure recompiles during execution. If the result set is not containing a huge number of records then you should stick to table variable, otherwise temp table has its advantages. There is a misconception that temp tables always use the tembdb database but table variable do not. Table variables also use tempdb after a certain size be careful to use them only with not very huge amount of data that you want to allocate in temp tables, otherwise you can easily get the server down.