Google+ Followers

Monday, July 28, 2014

Tips To Improve SQL Server Query Performance

Here are some tips to improve your SQL query performance.

1.    Choose Correct Data types
2.    Always avoid nchar & nvarchar if you are not going to store Unicode data
3.    Always Avoid Select * statement, it’s better to mention the column name in select statement.
4.    Use Exists instead of IN, NOT Exists instead of <> and Not Like keyword
5.    Create Clustered and Non-Clustered Index
6.    Try to keep Clustered Index Small
7.    Avoid using Cursors, try to implement using while loop
8.    Use Table Variable and Temporary table cleverly
9.    Use Union All in place of Union
10.  Define All Primary keys and Foreign Key Relationships
11.  Define All Unique and Check Constraints
12.  Partition Tables Vertically and Horizontally
13.  Avoid Expensive Operators such as Having, Not Like, Distinct, Order By
14.  Avoid Explicit or Implicit functions In Where Clause
15.  Full Qualify database Objects using Database.Schema.Objects
16.  Use Locking and Isolation Level Hints to minimize locking
17.  Use Stored Procedure or Parameterized Queries
18.  Avoid Long Actions in Triggers
19.  Avoid Long Running Transactions, Transactions that require user input to commit
20.  Use Set NOCOUNT ON in Stored Procedure
21.  Don’t use SP_(Your SP Name) for user defined stored Procedure  name
22.  Try to convert Sub Query to Join
23.  Use TRY-Catch blocks to handle errors
24.  Always use column list in your insert statements
25.  Don’t use column numbers in ORDER BY clause
26.  Try to avoid trigger on transaction table which gets many Insert, update, Delete operations. 

There are many other factors which can improve SQL query performance here I tried to collect all
important key points which can help to improve SQL query performance.

No comments: