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
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"
The database engine is optimized for Set based SQL. Hence, Procedural approach (use of Cursor or UDF to process rows in a result set) should be avoided when large result sets (more than 1000) have to be processed.
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.
http://blog.sqlauthority.com/2007/09/07/sql-server-correlated-and-noncorrelated-subquery-introduction-explanation-and-example/
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
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.
Try to avoid the use of temporary tables
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.
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
Use VARCHAR(MAX), VARBINARY(MAX), and NVARCHAR(MAX)
Implement the following good practices in User Defined Functions
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.
Implement the following good practices in Stored Procedures
Do not use "SP_XXX" as a naming convention. It causes additional searches and added I/O (because the system Stored Procedure names start with "SP_"). Using "SP_XXX" as the naming convention also increases the possibility of conflicting with an existing system Stored Procedure.Use "Set Nocount On" to eliminate extra network trip.Use the WITH RECOMPILE clause in the EXECUTE statement (first time) when the index structure changes (so that the compiled version of the Stored Procedure can take advantage of the newly created indexes).Use default parameter values for easy testing
Implement the following good practices in Triggers
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.
Implement the following good practices in Views
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.
Implement the following good practices in Transactions
Prior to SQL Server 2005, after BEGIN TRANSACTION and each subsequent modification statement, the value of @@ERROR had to be checked. If its value was non-zero, then the last statement caused an error, and if an error occurred, the transaction had to be rolled back and an error had to be raised (for the application). In SQL Server 2005 and onwards, the Try...Catch block can be used to handle transactions in TSQL. So try to use Try...Catch based transactional code.Try to avoid nested transactions. Use the @@TRANCOUNT variable to determine whether a transaction needs to be started (to avoid nested transactions).Start a transaction as late as possible and commit/rollback the transaction as fast as possible to reduce the time period of resource locking.
Wondershare EdrawMind
