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.