SQL Good Practices

1)Avoid use of SELECT * in SQL queries. Instead practice writing required column names after SELECT statement.
Example:
SELECT Username, Password
FROM UserDetails

2)Avoid using temporary tables and derived tables as it uses more disks I/O. Instead use CTE (Common Table Expression); its scope is limited to the next statement in SQL query.

3)Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure.

4)Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.

5)Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement.
INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE ....

6)Do not use the RECOMPILE option for Stored Procedure as it reduces the performance.

7)Always put the DECLARE statements at the starting of the code in the stored procedure. This will make the query optimizer to reuse query plans.

8)Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure.

9)Use BEGIN…END blocks only when multiple statements are present within a conditional code segment.