SQL Best Practices

SQL Best Practices
Main Topic 1
use indexing
Don't use "SELECT*" in a SQL query
Avoid unnecessary columns in the SELECT list and unnecessary tables in join conditions
Do not use the COUNT() aggregate in a subquery to do an existence check
use exist
Try to avoid joining between two types of columns
Try to avoid deadlocks
Always access tables in the same order in all your Stored Procedures and triggers consistently
Keep your transactions as short as possible. Touch as few data as possible during a transaction
Never, ever wait for user input in the middle of a transaction
Write TSQL using "Set based approach" rather than "Procedural approach"
How can we get rid of "Procedural SQL"? Follow these simple tricks:
Use inline sub queries to replace User Defined Functions.
Use correlated sub queries to replace Cursor based code.
If procedural coding is really necessary, at least, use a table variable instead of a cursor to navigate and process the result set
Try not to use COUNT(*) to obtain the record count in a table
Try to avoid dynamic SQL
Try to avoid the use of temporary tables
Instead of LIKE search, use full text search for searching textual data
Try to use UNION to implement an "OR" operation
Implement a lazy loading strategy for large objects
Implement the following good practices in User Defined Functions
Implement the following good practices in Stored Procedures
Implement the following good practices in Triggers
Implement the following good practices in Views
Implement the following good practices in Transactions
Dynamic SQL is hard to debug and troubleshoot.
If the user provides the input to the dynamic SQL, then there is possibility of SQL injection attacks.
Unless really required, try to avoid the use of temporary tables. Rather use table variables.
In 99% of cases, table variables reside in memory, hence it is a lot faster. Temporary tables reside in the TempDb database. So operating on temporary tables require inter database communication and hence will be slower.
Do not call functions repeatedly within your Stored Procedures, triggers, functions, and batches. For example, you might need the length of a string variable in many places of your procedure, but don't call the LEN function whenever it's needed; instead, call the LEN function once, and store the result in a variable for later use.
Try to avoid the use of triggers. Firing a trigger and executing the triggering event is an expensive process.
Never use triggers that can be implemented using constraints.
Do not use the same trigger for different triggering events (Insert, Update, Delete).
Do not use transactional code inside a trigger. The trigger always runs within the transactional scope of the code that fires the trigger.
Use views for re-using complex TSQL blocks, and to enable it for indexed views (Will be discussed later).
Use views with the SCHEMABINDING option if you do not want to let users modify the table schema accidentally.
Do not use views that retrieve data from a single table only (that will be an unnecessary overhead). Use views for writing queries that access columns from multiple tables.