There are many occasions where a SQL Server Developer uses scripts which take longer to execute. I would rather say a wrong choice or approach in writing scripts caused the execution of the script longer. There are smarter ways to achieve results. In this post I will try to consolidate few tips on smarter SQL Server programming.
Do you want to delete all rows from a table?
Use Truncate instead of Delete statement. Truncate is faster and does a reseed of identity column.
Use Owner Name prefix while writing Select statement:
Select thColumnName from dbo.theTableName
If the Owner Name is specified, the SQL Query Optimizer does not have to decide whether to retrieve from dbo.theTableName or some other owner’s table and avoids recompilation.
Do not use “Select Count(*)…”
The better way to fetch the count of rows:
Select rows from sysindexes where id = Object_ID(‘theTableName’) and IndID < 2
Do not use “sp_” prefix in your Stored Procedures:
The prefix “sp_” for Stored Procedures is a reserved prefix in MS SQL Server. When a stored procedure is given a call, the SQL Server first looks for the procedure in System Procedure List and then in User Defined Procedure List; hence delaying the execution of the procedure.
I will try to add more tips like these in future.