Do's and Don't SQL

Do's:

• Writing comments whenever something is not very obvious, as it won’t impact the performance.
•  (--) for single line 
• (/*…*/) to mark a section
• Use proper indentation
• Use Upper Case for all SQL keywords. SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE.
• Use BEGIN... END block for multiple statements in conditional code 
• Use Declare and Set in beginning of Stored procedure
• Create objects in same database where its relevant table exists otherwise it will reduce network performance.
• Use PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.
• If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table.
• Use select column name instead of select *
• Use CTE (Common Table Expression); its scope is limited to the next statement in SQL query, instead of temporary tables and derived tables as it uses more disks I/O.
• If stored procedure always returns single row result set, then consider returning the result set using OUTPUT parameters instead of SELECT statement, as ADO handles OUTPUT parameters faster than result sets returned by SELECT statements.
• Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure.
• Incorporate your frequently required, complicated joins and calculations into a view so that you don’t have to repeat those joins/calculations in all your queries. Instead, just select from the view.
• With Begin and End Transaction always use global variable @@ERROR, immediately after data manipulation statements (INSERT/UPDATE/DELETE), so that if there is an Error the transaction can be rollback.
• The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.

Don't / Avoid  :

• Do not use reserved words for naming database objects
• Do not use wild card characters at the beginning of word while search using LIKE keyword as it results in Index scan.
• Columns with Default value constraint should not allow NULLs.
• Do not store binary or image files (Binary Large Objects or BLOBs) inside the database. Instead, store the path to the binary or image file in the database and use that as a pointer to the actual file stored on a server.
• Do not call functions repeatedly in stored procedures, triggers, functions and batches, instead call the function once and store the result in a variable, for later use.
• Do not use column numbers in the ORDER BY clause; it will reduce the readability of SQL query.
• Avoid using spaces within the name of database objects 
• Avoid using dynamic SQL statements. Dynamic SQL tends to be slower than static SQL, as SQL Server generate execution plan every time at run time.
• Avoid server-side Cursors as much as possible, instead use SELECT statement. If you need to use cursor then replace it with WHILE loop
• Avoid the use of cross joins if possible.
• Excessive usage of GOTO can lead to hard-to-read and understand code.