Common Best Practises

1. Never ever use Select * in your query.
- If you use *, the engine has to get all the column information from the table. If your table is huge and the column contain huge data, it may lead to IO bottleneck.
- There is a good prone to break your code if some one adds a column to the table when you use select * in insert statement.
- Try to get only the required information from the table.Take this as a first thumb rule.
2. Never use sp_ prefix to user procedures
- When a proc has been prefixed with sp_, the optimizer looks at the procedure in master db first and then flow goes to the current database. This unnecessary check can be eliminated by avoiding the sp_ prefix to all user procedures.
Note: Procedures can be prefixed with sp_ when it creates in master database that can be accessed through all other database.
3. Try to avoid functions with a join condition keys.
- When we use functions with join keys, the index created on the keys will not have any effect on the join.

4. Avoid Select count(*) to check record existence

-Record existence check has always to be replaced with exists one unless the check does not do anything to do with the data. Either (*) or (1) can be used in the statement inside of exists as it does not have any difference. As long as the query optimizer finda a value, optimizer quits from the execution satisfying the condition. Try with a larger table to get understand the difference between the logical reads.

Example:

/*------------------------
Declare @Count int
Select @Count = COUNT(1) From Test_Zealot_Count
If @Count>0
Select 'Proceed'
IF Exists(Select 1 From Test_Zealot_Count)
Select 'Proceed'
------------------------*/

Table 'Test_Zealot_Count'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)

Table 'Test_Zealot_Count'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)