DISTINCT and ORDER BY	Minimize usage of the DISTINCT/ORDER BY clause; only use it if necessary. DISTINCT/ORDER BY clauses cause extra work for the database engine and reduce performance. In some rare cases, ORDER BY can help speed up the operation.
CURSORS	Using cursors makes the program slower, as it works against SET-based SQL. Try to use Temporary table/Table variables with an identity column and then iterate all the rows using a WHILE loop and a looping counter, which will map with the identity column.
Calculations & Convert Functions	Avoid calculations at the left end a WHERE clause. Such calculations don't use the Index even if the Data column has been indexed; instead, they may do an Index or Table scan. For example, use SELECT COUNT (*) FROM dbo.SomeLogTable WHERE DateColumn = '2009-10-11T00:00:00.000' instead of SELECT COUNT (*) FROM dbo.SomeLogTable WHERE CONVERT(CHAR (8), DateColumn, 112) = '20091011’
CAST & CONVERT	Try to use CAST instead of CONVERT. CAST is ANSI-92 standard, but CONVERT works in MS SQL server only. Also, some CONVERT styles may be deprecated in future MS SQL releases. Use CONVERT only when you need to format the DATETIME data type which CAST cannot do.
Like Operator	Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while the second statement results in an index seek: SELECT LocationID FROM Locations WHERE Specialities LIKE ‘%Clinics’ SELECT LocationID FROM Locations WHERE Specialities LIKE ‘Cl%s’
APPLY joins	Use CROSS/OUTER APPLY when performing JOIN against table valued functions. Example: Imagine dbo. FnGetQty() is a table valued function: SELECT M.ID, M.NAME, C. PERIOD, C.QTY FROM MASTER M CROSS APPLY dbo. FnGetQty(M.ID) C
JOIN Order Sequence	The order in which the tables on your queries are joined have a dramatic effect on how the query performs. If your query happens to join all the large tables and then join to a smaller table this can cause a lot of unnecessary processing and overhead by the SQL Engine
Data Types	Try to define data types to use minimal storage. ·       Do not use the CHAR data type for a Null column. If a column can ·       Avoid Unicode data types, like NCHAR, NVARCHAR, or NTEXT. They Only use Unicode types if your database will be storing international characters (not just English letters). Use these data types only when they are absolutely needed. ·       Use Varchar (max) instead of image or text for the fields that you want to use as the base for your  full text index.
Dynamic Queries	Try to minimize usage of dynamic queries. A dynamic query like SELECT * FROM emp where empid = @eid is no problem; you can supply a value for the @eid parameter and there is no recompilation of the execution plan in the database cache. But if you are using a SQL query like : SELECT * FROM emp where empid = " + @eid and supply a parameter (say 100), then the cache will keep the execution plan for the value of 100 only. If the ID changes (to 101), it will recompile the statement, which is a slower action than the previous example. However, when you decide to go with dynamic query, please minimize the   usage of concatenation.
Fully Qualified Names	Always use the fully qualified name when calling stored procedures. For example :  use "EXEC dbo.<Proc_name>" instead of "EXEC <Proc_name>" This is a very common mistake that causes an extra trip to the procedure cache to get the execution plan. Also try to use the schema name while creating a procedure. CREATE PROCEDURE dbo.<Proc_name> instead of CREATE PROCEDURE <Proc_name> It's good practice to start with ‘usp_’ to identify user-defined stored procedures.
Keywords	Use SQL keywords in Capital letters and with Proper indentation to increase readability.
Proper Case While Coding	Although the SQL environment is not case-sensitive, always be mindful of using the correct casing when referencing object names and field names. SQL commands should be in UPPER CASE.
Lock Hints	Avoid using query hints unless you know what exactly you are doing. Specifying table hints by using the WITH keyword. Support for use of the READUNCOMMITTED and NOLOCK hints in the Target table for UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.
MERGE Statements	Use MERGE in stored procedures where you would normally perform INSERT, UPDATE and/or DELETE against the same table. This enables you to perform these as a single action. But also check the performance against simple INSERT/UPDATE statements while using for larger tables.
SELECT Statements	Use only the required number of columns in a SELECT clause, instead of using *. Using * returns all columns, which unnecessarily creates a fat record set. Not only does it create more overhead and resource wastage, it also considerably slows down the speed.
SELECT vs. SET	A single SELECT statement can assign values to different variables and is faster than using multiple SET statements to assign them. Use SELECT @Var1 = @Var1 + 1, @Var2 = @Var2 - 1 instead of SET @Var1 = @Var1 + 1 SET @Var2 = @Var2 – 1
SQL-92 Standards	Always follow ANSI 92 join standards. The old syntax will be deprecated in a future release of MS SQL Server. Always use explicit joins (such as INNER JOIN and LEFT OUTER JOIN, rather than just JOIN) For example, use the following query: SELECT * FROM employee e1 INNER JOIN employee_dtl e2 ON e1.id = e2.id instead of SELECT * FROM employee e1, employee_dtl e2 WHERE e1.id = e2.id
CTE ,Derived Tables, Subqueries	When to Use CTE       Use for recursive query or queries or doing aggregation or calculations with self-reference.         CTE is easy to implement compared to complex queries which involves several sub-queries. Derived Table ·       When you are required to perform some complex query and result of inner query work  as table for outer query. Subquery ·       When you require that inner query or condition must be fulfilled before generating any result. ·       Use subquery when the result that you want requires more than one query and each subquery provides a subset of the table involved in the query. ·       If the query requires a NOT EXISTS condition, then you must use a subquery because  NOT EXISTS operator only work with subquery.
Parameter Sniffing	The problem arises with Parameter Sniffing is when a query uses a previously generated plan optimized for a different data distribution. Parameter sniffing performance problems can affect all sorts of queries, but queries that use LIKE  are especially prone to this class of problem. One of the Workaround is to  assign the input parameters to Use local variable while passing from the Stored procedures. Before Change   CREATE PROC GetCustOrders (@FirstCust int, @LastCust int) AS SELECT CustomerName,SalesItem FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @ FirstCust AND @ LastCust   After Change CREATE PROC GetCustOrders (@FirstCust int, @LastCust int) AS DECLARE @FC int DECLARE @LC int SET @FC = @FirstCust SET @LC = @LastCust SELECT CustomerName,SalesItem FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FC AND @LC
Table variables vs. Temporary Tables	If the result set doesn't contain a vast number of records, stick to a table variable; otherwise a temp table has its advantages. In  SQL 2014, Memory Optimized Table Variable was introduced and the variables are only stored in memory. Data Access is more efficient and They don’t use TempDB.
Transaction Scope	Try to minimize the transaction scope within SQL statements. This reduces locking and increases application concurrency.
UNION vs UNION ALL	Only Use UNION if you really need to. UNION provides distinct results, but is a slow process. If you already know that the results will be distinct, use UNION ALL.
Undocumented Functionality	Do not use any functionality that is undocumented. You will find many undocumented commands on various web sites, but ·       Microsoft will not support them in case of any issues, or ·       Even if it currently supports the command, Microsoft may stop     support in a future release.
Deprecated, Discontinued Features	With every release of a SQL Server version, Microsoft announces the ‘Backward Compatibility’ with prior versions. This is typically categorized into 4 primary categories — Deprecated, Discontinued, Breaking and Behavior changes. If a feature is listed as Deprecated, then it indicates that Microsoft is stating its disapproval in the usage of that feature in any future development work. These features are scheduled to be removed in a future version of SQL Server and hence during the review process makes sure that you are not using it in new application development work. One such deprecated feature which lot of legacy applications still use are the data types NTEXT, TEXT and IMAGE. You should avoid using these data types in new development activities and plan to modify applications that currently use them. Instead, you can use NVARCHAR() ,  VARCHAR() and VARBINARY(). Another deprecated syntax is the use of old-style join syntax (Mentioned above)
SET NOCOUNT ON	This command suppresses the message that shows the number of rows affected by a SQL statement. Always use SET NOCOUNT ON unless you have a very good reason not to; otherwise, this process causes extra network traffic and can impact performance when the procedure is called frequently.
Usage of Stored Procedures	Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. Stored procedures offer many benefits:          They result in reduced network traffic and latency, boosting application performance.          Stored procedure execution plans can be reused, staying cached in SQL Server’s memory and reducing server overhead.      Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column without using a stored procedure, it must convert the binary value to a character string (doubling its size); when SQL Server receives it, it must then convert back to the binary format. A stored procedure eliminates this overhead, as parameter values stay in the binary format all the way from the application to SQL Server.         Directly boost an application’s performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.         Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don’t remove any result-set columns).  This saves developer time. ·       Stored procedures provide better security to your data. ·       In Sql Server 2014 introduced concept of Native Compilation that access memory-optimized tables Stored procedures that are marked with NATIVE_COMPILATION is natively compiled. This means the Transact-SQL statements in the procedure are all compiled to native code for efficient execution of performance-critical business logic.
WHERE Clause Hints	Try to avoid IN. While checking the existence of some values, use EXISTS instead. IN counts null values, where EXISTS doesn't and returns a simple Boolean (Yes/No) rather than returning all values. The result set for IN is always heavier than for EXISTS. Example: SELECT name FROM emp WHERE empid IN (SELECT empid FROM empdetails) returns a bulkier set of results than SELECT name FROM emp WHERE EXISTS (SELECT 1 FROM empdetails Where emp. empid = empdetails. empid)
CLR Routine	·       Don’t turn on the CLR unless you will be using it. Use the CLR to complement Transact-SQL code, not to replace it. Version 3 standard data access—such  as SELECT, INSERTS, UPDATES, and DELETEs—are best done via Transact-SQL, not CLR. ·       Computationally or procedurally intensive business logic can often be encapsulated as functions running  in the CLR. ·       If a procedure includes both significant data access and computation and logic, consider separating the procedure code in the CLR that calls the Transact-SQL stored procedure that does most of the data access. ·       Use the CLR for error handling, as it is more robust than what Transact-SQL offers. ·       Use the CLR for string manipulation, as it is generally faster than using Transact-SQL. ·       Use the CLR when you need to take advantage of the large base class library. ·       Use the CLR when you want to access external resources, such as the file system, Event Log, a web service, or the registry.
OUTPUT Clause	Always use a column list in the insert queries. If you need the output of the inserted values, use an OUTPUT clause instead of going back to the database. Example: DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100)) INSERT TestTable (ID, TEXTVal) OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable SELECT ID, Value FROM TestTable2 where ID=2 select * from @TmpTable
Security	While reviewing the SQL code, try to check for possible scenarios which are susceptible to SQL Injection attack – a security vulnerability issue where malicious code is inserted into strings and passed to SQL server instance for execution. If you dynamically build your SQL queries by concatenating strings and not use parameterized queries or stored procedures, you are susceptible to SQL Injection attack. suggest you review your ad hoc SQL queries, and evaluate if it can be converted into a stored procedure.