How to write efficient | optimized | tuned queries in SQL Server

For a database developer it is a skill to write a most efficient tuned query.
 While query writing we generally come across some like I have written two different queries for same requirement which one is better? Join is better or sub query? When temporary will perform better and when table variable etc. Answer is very simple compare write all possible way which you can write and compare among them. Which one enjoy least that query is better among other.

How to compare the queries? How to know cost of the queries?

 

Sql server provides some counter on the basis of which queries can be compared.  They are logical read, CPU time, IO cost etc.

To get the logical of query first enable:

SET STATISTICS IO ON  

Then execute your query. In the messages tab you will find total logical read. As shown below:

Logical Read SQL SERVER

A query which has less logical read is better than query with large logical read. Best way to compare the queries is using execution plan. For example, I want to know, which query will perform better query 1 or query 2?

--Query 1

SELECT U.ntUserID

     ,MAX(U.vcUserName) AS vcUserName

     ,COUNT(*) ntUserContact

FROM tblUser U

INNER JOIN tblUserContact UC ON U.ntUserID = UC.ntUserID

GROUP BY U.ntUserID

--Query 2

SELECT U.ntUserID

     ,U.vcUserName

     ,(

          SELECT COUNT(*)

          FROM tblUserContact UC

          WHERE U.ntUserID = UC.ntUserID

          ) ntUserContact

FROM tblUser U

Step 1: Enable the actual execution plan. (To enable it use window shortcut Ctrl + M or click on to Query -> Include Actual Execution Plan.)

ActualExecution Plan

Step 2: Execute both the queries in same query page.

Compare Query Cost Sql Server

Step 3: Click on execution plan tab. At the top of both of the execution plan you will find cost of the query relative to batch: 

  

Compare Execution Plan

So cost of query relative to batch is 59% and query 2 is 41%.  It mean query 2 is better than query 1 by (59-41) * 100/59 % = 30%.

What are measures causes of badly or inefficient written transact sql queries?  

May be developer are not familiar with all the weapons which are provided in Transact-SQL. Some of the developers are familiar with very less numbers clauses like SELECT, WHERE, FROM, TOP, GROUP BY, ORDER BY, HAVING, INTO etc. and they want to full fill the entire requirement of application by using only those operators. But they are   not familiar with clauses like OUTPUT, CROSS APPLY, PIVOT, FOR XML, MERGE etc. due to which they are not able to use appropriate clause while query writing which leads inefficient query writing.

May they are not familiar with how a query is processed and cost of each physical operators etc.

Bad database schema design:  Developer has not any alternatives and they are force to write bad script instead of correcting the schema design. 

Here we are going to discuss about how to write an efficient transact sql script. How a query is processed. While query writing what should be considered and what should be with help of some very common real world examples.