1)Use schema name with object name
eg. Select * from dbo.tablename
2)Do not use the prefix “sp_” in the stored procedure name:
If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database.
3)Use IF EXISTS (SELECT 1) instead of (SELECT *):
Always use select 1 instead of select * because select * takes all column.
4)Keep the Transaction as short as possible:
The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction.
5)Try to avoid using SQL Server cursors whenever possible:
Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance.