Following is a list of best practices for SQL Server

1) Use correct formatting
   Following is a sample select query for reference:
           

SELECT
           X.column1,
          X.column2,
           X.column3,
           Y.column1,
           Y.column2,
           Y.column3   
    FROM
           dbo.table_X_with_long_name AS X
    INNER JOIN
           dbo.table_Y_with_long_name AS Y
    ON
           X.column1 = Y.column1
           OR X.column2 = Y.column2
           OR X.column3 = Y.column3
    WHERE
           X.column1 >= 5
           AND X.column1 < 10

Table aliases should be meaningful as per context, X and Y used above is for illustration  only

          Following is a sample nested IF ELSE query for reference:

IF @OuterIfCondition = 1
    IF @InnerIfCondition = 2
        SET @InnerIfStatement = 3
    ELSE IF @SecondInnerIfCondition = 4
        SET @InnerSecondIfStatement = 5
    ELSE
        SET @InnerElseStatement = 6
ELSE
    SET @OuterElseStatement = 7

2) Capitalize SQL special words.

3) Minimize holding locks on database objects.   

4) Minimize calls to database. Use complex stored procedures instead of  multiple retrievals.

5) Never use SELECT *,always be explicit in which columns to retrieve.

6) Refer to fields implicitly; do not reference fields by their ordinal placement in a recordset.

7) Use stored procedures instead of SQL statements in source code to leverage the performance gains provided by them.

8) Use a stored procedure with output parameters instead of single-record SELECT statements when retrieving one row of data.

9) Verify the row count when performing DELETE operations.

10) Avoid using functions in WHERE clauses.

11) If possible, specify the primary key in the WHERE clause when updating a single row.

12) When using LIKE, do not begin the string with a wild card character because SQL Server will not be able to use indexes to search for matching values.

13) Use triggers only for data integrity enforcement and business rule processing and not to return information.

14) Implement appropriate error handling.

15) Use SQL bulk copy for saving multiple records.

16) Use uncorrelated sub-queries instead of correlated sub-queries. Uncorrelated sub-queries are those where the inner SELECT statement does not rely on the outer SELECT statement for information. In uncorrelated sub-queries, the inner query is run once instead of being run for each row returned by the outer query.

17) Use try-catch in your data layer to catch all database exceptions. This exception handler should record all exceptions from the database. The details recorded should include the name of the command being executed, stored proc name, parameters, connection string used etc. After recording the exception, it could be re thrown so that another layer in the application can catch it and take appropriate action.

18) Avoid cursors. Try to replace with set based operation, in case looping is the only option use WHILE loop.

19) Avoid dynamic SQL. In case required use sp_executesql for dynamic code

20) Avoid not equal to operator,  it leads to table scan.

21) Be sure to select the correct data types for each column. For example, if a varchar column can be used as opposed to a BLOB data type, in general the performance implications will be less.

22) Always specify the schema prefix (dbo.procedure_name)when creating stored procedures.

23) Always add SET NOCOUNT ON; as the very first line of the procedure (after BEGIN of course).

24) Use table aliases in queries.

25) Always include a WHERE clause in your SELECT statement to narrow the number of rows returned. Only return those rows you need.

25) Avoid expensive operators such as NOT LIKE.

26) Avoid long actions in triggers.

27) Ensure that all tables have a primary key (preferably of integer type), and all foreign keys are indexes with a non clustered index.

28) Developers should make efforts in identifying heavy queries within their applications, and review accordingly on how to optimize the query if needed.

29) Temporary tables vs table variable, as a general rule of thumb use a table variable for relatively small data-sets, and use a temporary table for fairly large data-sets. Temp tables maintain statistics and can have indexes.

30) Keep transactions as short as possible. This reduces locking and increases application concurrency, which helps to boost performance.

31) Before rolling a stored procedure out into production, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were creating it,and remove them.

32) When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like record-sets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final record-set. If you know that there are duplicate records, and this presents a problem for your application, then by do use the UNION statement to eliminate the duplicate rows.

33) Carefully evaluate whether your SELECT query needs the DISTINCT clause or not. Some developers automatically add this clause to every one of their SELECT statements, even when it is not required.