(1)Always use ANSI92 syntax avoid to use the old syntax.
(2)Use SQL keyword in capital letters to increase readability.(3)Use few possible variables to increase cache memory.
(4)Try to avoid dynamic queries if we are not using dynamic query there is no recompilation of execution plan but on the other side if we are using dynamic query every time we need recompile of plan.(5)Use SET NOCOUNT ON this will helps us to get number of row effected without network traffic.
(6)To avoid recompilations use OPTION KEEPFIXED PLAN.(7)In Select and Set use select to assign value to variable it is much faster than multiple set statement.
(8)Try to avoid IN. IN counts Null values so use EXISTA there. EXISTS which return only boolean value and IN return heavier result than EXISTS.(9)In CAST and CONVERT always try to use CAST it is ASNI92 standard.Use convert in case of datetime.
(10)Avoid Distinct and Order by clause.These class needs extra space.(11)Avoid cursor so use while loop for that and temparory tables.
(12)Avoid to use * in select statement.(13)Avoid correlated sub queries.
(14)Avoid select * into for large tables it locks the system objects.(15)Avoid temporary tables because it will recompile the procedure.
