SQL Optimization

1. Try to restrict the queries result set by using the WHERE clause.
2. Try to restrict the queries result set by returning only the particular columns from the table, not all the table’s columns.
3. Use views and stored procedures instead of heavy-duty queries.
4. Whenever possible, try to avoid using SQL Server cursors.
5. If you need to return the total table’s row count, you can use an alternative way instead of the SELECT COUNT(*) statement.
6. Try to use constraints instead of triggers, whenever possible.
7. Use table variables instead of temporary tables.
8. Try to avoid the HAVING clause, whenever possible.
9. Whenever possible, try to avoid using the DISTINCT clause.

10. Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.

11. Use select statements with the TOP keyword or the SET ROWCOUNT statement if you need to return only the first n rows.
12. Use the FAST number rows table hint if you need to quickly return ‘number rows’ rows.
13. Try to use UNION ALL statement instead of UNION, whenever possible.
14. Do not use optimizer hints in your queries.

What are the types of join and explain each? Inner join, union Left join, right join Full join, outer join

Ref: https://www.databasejournal.com/features/mssql/article.php/1437391/Transact-SQL-Optimization-Tips.htm