Performance tips for sqlserver

In this article we are going to see the performance impact of some of the queries in SQL SERVER.

SELECT instead of SET:
   Always prefer to use SELECT keyword instead of SET , to assign a value for variable.Because in select we can assign a value for multiple variable at a time at a single execution. It takes a one value from a result,if it have more than one value in the result set.

Exists instead of Count():
  Always try to use the Exists keyword to check the existence of record, instead of Count(*).Count() needs a full record set to be retrieved

UnionAll instead of Union:
  Always try to use  UnionAll because Union use to return distinct values due to this some internal operations take more time.

Joins instead of SubQuery:
 Try to use Joins instead of subQuery, Because subQuery takes more time to retrieve the records in from table.

Order by :
  Try to avoid order by in Query ,  try to select the data in db and order the data in front end . it takes few second only.If you use order by in Queries. It takes more time to order and takes the data.

Functions :
  Try to avoid the usage of functions in where condition, It takes the more time to execute the function for each and every record.

Dynamic Query:
  Try to avoid the dynamic query, Because at the run time only query framed and executed so the cache is not possible for that query. 

Cursor:
 Try to use While loop instead of using Cursor , because cursor is executed in record by record in sequence.

More Joins :
  Avoid more joins in tables,because this gives very slow performance hit,so try to kept the data in optimized table structure.

Indexes :
  Try to create a indexes for the tables to retrieve the records very fast in SELECT, and Avoid the more non-clustered indexed column in Where clause.



I Hope From this article you can see some of the performance tips in SQL SERVER.