SQL Best Practices

Fiona_

Do you want to implement SQL best practices in your coding style? Check this mind map,

Similar Mind Maps

Outline

SQL Best Practices

Main Topic 1

Table should have primary keyTable should have minimum of one clustered indexTable should have appropriate amount of non-clustered indexNon-clustered index should be created on columns of table based on query which is runningFollowing priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clauseDo not to use Views or replace views with original source tableTriggers should not be used if possible, incorporate the logic of trigger in stored procedureRemove any adhoc queries and use Stored Procedure insteadCheck if there is atleast 30% HHD is empty – it improves the performance a bitIf possible move the logic of UDF to SP as wellRemove * from SELECT and use columns which are only necessary in codeRemove any unnecessary joins from tableIf there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

use indexing

best

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"

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.

subquery

nested

Correlated