- Try to restrict the queries result set by using the WHERE clause.
- Try to restrict the queries result set by returning only the particular columns from the table, not all the table’s columns.
- Use views and stored procedures instead of heavy-duty queries.
- Whenever possible, try to avoid using SQL Server cursors.
- If you need to return the total table’s row count, you can use an alternative way instead of the SELECT COUNT(*) statement.
- Try to use constraints instead of triggers, whenever possible.
- Use table variables instead of temporary tables.
- Try to avoid the HAVING clause, whenever possible.
- Whenever possible, try to avoid using the DISTINCT clause.
Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
- Use select statements with the TOP keyword or the SET ROWCOUNT statement if you need to return only the first n rows.
- Use the FAST number rows table hint if you need to quickly return ‘number rows’ rows.
- Try to use UNION ALL statement instead of UNION, whenever possible.
- 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