The * Operator
Do not use the * operator in your SELECT statements. Instead, use the column names.
Reason: SQL Server scans for all column names and replaces the * with all the column names of the table(s) in the SQL SELECT statement. Providing column names avoids this search-and-replace, and enhances performance.
 Nullable Columns
Do not use NOT IN when comparing with nullable columns. Use NOT EXISTS instead.
Reason: When NOT IN is used in the query (even if the query doesn’t return rows with null values), SQL Server will check each result to see if it is null or not. Using NOT EXISTS will not do the comparison with nulls.
 Table Variables and Joins
Do not use table variables in joins. Use temporary tables, CTEs (Common Table Expressions), or derived tables in joins instead.
Reason: Even though table variables are very fast and efficient in a lot of situations, the SQL Server engine sees it as a single row. Due to this, they perform horribly when used in joins. CTEs and derived tables perform better with joins compared to table variables.
 Stored Procedure Names
Do not begin your stored procedure’s name with sp_.
Reason: When the stored procedure is named sp_ or SP_, SQL Server always checks in the system/master database even if the Owner/Schema name is provided. Providing a name withoutSP_ to a stored procedure avoids this unnecessary check in the system/master database in SQL Server.
 Use SET NOCOUNT ON
Use SET NOCOUNT ON with DML operations.
Reason: When performing DML operations (i.e. INSERT, DELETE, SELECT, and UPDATE), SQL Server always returns the number of rows affected. In complex queries with a lot of joins, this becomes a huge performance issue. Using SET NOCOUNT ON will improve performance because it will not count the number of rows affected.
 Avoid Using GROUP BY, ORDER BY, and DISTINCT
Avoid using GROUP BY, ORDER BY, and DISTINCT as much as possible
Reason: When using GROUP BY, ORDER BY, or DISTINCT, SQL Server engine creates a work table and puts the data on the work table. After that, it organizes this data in work table as requested by the query, and then it returns the final result.
Use GROUP BY, ORDER BY, or DISTINCT in your query only when absolutely necessary.