Best practices for Developers

These are the common best practices that I follow durning development.

1.Database design
1.Take time and try to best understand and design database.
2.Take narrow column, this would be better for memory paging and I/O cost.
3.Try to use database built-in referential integrity.
2.Indexes
1.Using Index tuning Wizard to remove and add necessary indexes.
2.If regular DML performed on Clustered Index column then use defragmentation of indexes on regular interval.
3.Consider indexes on all columns that are frequently used in where clause, group by and top.
3.Store Procedures
1.Keep transaction as short as possible this reduce locking.
2.Always use Begin Trans, Commit and Rollback to get best result of DMLs.
3.Use least restrictive transaction Isolation level like Read Committed.
4.Always use SET NO COUNT ON in begging of your store procedure.
5.Always remove unwanted variables and comments from procedures.
6.Use same SQL connection as much you want to re-use same execution plan for a procedure.
4.SQL and T-SQL
1.Use block and Inline comments in your SQL this will improve understanding of the SQL complexity and reusability.
2.Keep in mind while using Union it will by default use select distinct clause if you want all records you must use Union all.
3.Don’t return column data that you do not need. Try to avoid select * in query when you have covering indexes.
4.Try to use where clause in your query to narrow the result and result only those columns that you need. If you have no where clause then from doing the same if all joins are inner joins but return all columns in memory.
5.If you have a choice between IN and Between, try to Use between.
6.If you want to return data from single table do not use view because that might have many table joins and cause the performance.
7.Try to avoid where clause that is non-sargable while using index columns. This cannot take advantage of indexes and seek operation. Such as “Is NULL, like, between, not , not exists, <>, !< etc.”
8.Try to Use CLR functions only for logic not for DML operations.
9.Try to avoid cursors as much as possible.
10.Better to use set based operations.
11.Try to minimize joins in query while using T-SQL.
12.To avoid deadlock use NOLOCK with table name.