Indexes speed the performance of queries. Create index considering the INSERT, UPDATE, DELETE and SELECT. If there is more use in one of those, consider it more than the others.
Using of table variable is stored in the plan cache. Temporary tables can be indexed. Check which one of them is better to a specific situation.
'OR' can be replaced by 'UNION' and vice versa.
SELECT ... WHERE ColA = 5 OR ColB = 2
SELECT ... WHERE ColA = 5 UNION SELECT ... WHERE ColB = 2
Check which one of them is better to a specific situation.
'DISTINCT' can be replaced by 'GROUP BY' and vice versa. Check which one of them is better to a specific situation.
'JOIN' can be replaced by sub-query and vice versa. Check which one of them is better to a specific situation; usually 'JOIN' is better than sub-query.
sp_executesql is usually better than EXECUTE statement.
Usually using 'IF EXISTS' is better than count by SELECT COUNT (*).
Usually using 'EXISTS' is better than using sub-query.
Check Parameter Sniffing - is sometimes good and sometimes not.
Try to update statistics, recompile stored procedure, re-build indexes. They need to be refreshed from time to time.
'SET NOCOUNT ON' can speed the performance - in queries and procedures.
If not all the objects are owned by the same schema - call the objects with the schema - SchemaName.ObjectName.
Avoid using cursors.
Don't repeatedly reuse the same function or calculation in a T-SQL code.
Sort queries (ORDER BY) only when necessary.
SELECT DISTINCT only when it necessary.
Try to avoid using 'SELECT *'. Select the columns that you need.
When using 'LIKE', try to use it with leading characters in the clause: LIKE 'm%' instead of LIKE ‘%m%’.
Check Blocking and locks.
don't return more data (rows and columns) than you need to the client or middle-tier and then the data you really need at the client. This wastes SQL Server resources and network bandwidth.
