TSQL Optimization

   In every interview you might ask the basic questions related to SQL Performance Because Query optimization is very essential factor for any project.In this article I will cover most important Performance Tuning tip.

• Use WHERE expressions to limit the size of result tables that are created with joins.
• Avoid * in SELECT statement. Give the name of columns which you require. 

            SELECT * FROM TABLE1 

                   SELECT COL1,COL2,COL3 FROM TABLE1 /*This Is better*/

• Use joins instead of sub-queries.
• Better to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.
• Use WITH (NOLOCK) while querying the data from any table.

                  SELECT COL1,COL2,COL3

                  FROM TABLE1 WITH(NOLOCK) /*This Is better*/

• Use TRY- CATCH to avoid deadlock condition.
• Avoid Cursors since cursor are very slow in performance.
• Use Table variable in place of Temp table

                 INSERT INTO #TABLE

                 SELECT COL1,COL2,COL3

                 FROM TABLE1 WITH(NOLOCK) /*Instead of This*/

                INSERT INTO @TABLE

                SELECT COL1,COL2,COL3

                FROM TABLE1 WITH(NOLOCK) /* This is better handling with small row set*/

• Use UNION ALL in place of UNION if possible.
  Try to avoid triggers if possible.
• Avoid Using DISTINCT clause
• Use EXISTS instead of IN to check existence of data.
• Avoid nchar and nvarchar if possible since both the data types takes just double memory.
• Avoid Having Clause. Having clause is required if you further wish to filter the result of an aggregations.
• Use Schema name before SQL objects name
• Use Stored Procedure for frequently used data and more complex queries.
• Keep transaction as small as possible since transaction lock the processing tables data and may results into deadlocks.
• Avoid prefix “sp_” with user defined stored procedure name because SQL server first search the user defined procedure in the master database and after that in the current session database.
• Use set NOCOUNT ON at the top of each stored procedure.
• Remove calculations in your JOIN and WHERE clauses , Avoid using OR in join conditions

                  SELECT ID

           FROM TABLE1 a

                JOIN

             TABLE2 b ON

             ((year(a.COL1)* 100) + month(a.COL2)) = b.COL3

• Follow proper indexing method and remove unwanted index