Guidelines, Tips and Tricks MSSQL
Posted by codingsense on December 23, 2008
Here are some of the Tips and Tricks to imporve the performance of MSSQL.
- Minimize the use of nulls.Because they incur more complexity in queries and updates. ISNULL and COALESCE functions are helpful in dealing with NULL values.
- Use TRUNCATE TABLE statement instead of DELETE clause if you want to delete all rows from a table
- Do not use reserved words for naming database objects, as that can lead to some unpredictable situations
- Avoid using the new bigint data type unless you really need its additional storage capacity. The bigint data type uses 8 bytes of memory verses 4 bytes for the int data type
- Don’t use “sp_“ as your prefix for stored procedures – it is a reserved prefix in MS SQL server!
- The Union All statement is much faster than Union, because Union All statement does not look for duplicate rows, and Union statement does look for duplicate rows, whether or not they exist.
- Always put the Declare statements at the starting of the code in the stored procedure. This will make the query optimizer to reuse query plans.
- Do not call functions repeatedly in stored procedures, triggers, functions and batches, instead call the function once and store the result in a variable, for later use.
- To avoid trips from application to SQL server, we should retrieve multiple resultset from single Stored procedure instead of using output param.
- If stored procedure always returns single row result set, then consider returning the result set using OUTPUT parameters instead of SELECT statement, as ADO handles OUTPUT parameters faster than result set returned by SELECT statements.
- Avoid (*), Try to restrict the queries result set by returning only the particular columns from the table, not all table’s columns.
- Use SET NOCOUNT ON statement in your stored procedures to reduce network traffic.
- Call stored procedure using its fully qualified name.The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.
- Do not query/manipulate the data directly in your front end application, instead create stored procedures, and let your applications to access stored procedure
- Except or Not Exist clause can be used in place of Left Join or Not In for better performance.
If any more additional tips or guidelines then please post a comment.
Happy Learning 🙂