T-SQL Queries Optimization

1. Create index on any key column such as foreign key.
2. SET Transaction Isolation Level Read Uncommited at the top of SP
3. Use transaction when appropriate
4. Avoid temporary table if possible.
5. Always use profiling before and after of each change
6. Look for every possible way to reduce no of round trips to server
7. Avoid Index and join hints
8. Avoid functions in where clause
9. Every SQL query is broken down in to series of execution steps called as operators. Each operator performs basic operations like insertion, search, scan, updating, aggregation etc. There are 2 kinds of operators Logical operators and physical operators.
10. Creating covering index instead of Composite NCI
11. Unique column and preferred data types
12. The execution plan describes the sequence of operations, physical and logical, that SQL Server will perform in order to fulfill the query and produce the desired resultset. 
13. Use SP instead of separate statements
14. Do not use * operators
15. Never use Computed by, cross join and cross join
16. Never use LIKE operator when there is an exact match
17. Make use of variables
18. Use two part naming conventions
19. Partition table and indexes
20. Use union all instead of union
21. Check out slow running query using query optimizer
22. Use SQL profiler for trace files and DTA for Advise for better performance