SQL Server Guidelines

Database Structure Guidelines

• Each table must have a primary key and a clustered Index. Clustered Index for any table is necessary and is created with the Primary Key (Only one can be created per table) . GUID fields should not be used for clustered indexes even if used as table’s Primary Key
• Multiple non-clustered indexes can be created for a table. Each index will be tagged to a column. Choose columns on which data will be filtered (example: create a non-clustered index for User ID column in the UserMaster table)
• Do not try to create too many non-clustered indexes. Avoid over indexing the tables. The non clustered indexed column should be updated less frequently. 
• Do not use TEXT as a data type; use the maximum allowed characters of VARCHAR instead  like varchar(50).
• Use the Database Engine Tuning Advisor to analyze your database and make index recommendations
• Any table that has a VARCHAR(MAX) column is a candidate for poor performance. Create a column of this type only if absolutely necessary . In case of selecting data from this table, avoid selecting the VARCHAR(MAX) column. If it is absolutely necessary, select this column separately on demand 
• Try to avoid duplicate values/attributes in the database. Normalize the database. Redundancy of same attributes causes data inconsistency issues hence is not recommended.

Coding Guidelines

• Write queries that insert or modify as many rows as possible in a single statement, instead of using multiple queries to update the same rows. By using only one statement, optimized index maintenance could be exploited.
• Cursors, Triggers should be avoided.
• Index Scan vs Index Seek: If we have a table with 1000 rows and we are querying all 1000 rows, then an index scan is performed, where every row of the table is parsed 

          Example: select * from UserMaster

        

          On the same table, if we are querying only 10 rows, then an index seek will be performed

 
          Example: select * from UserMaster where fkRoleId = 10 

         When any functions are used in the where clause, then an index scan is performed which results          in a very high cost for the query that may not be necessary

        Example: select * from UserMaster where LEFT(UserId) = 'pa'

       In the above example, we are using the “LEFT” function to ascertain the first two characters of           the query. However, since we are using a function on a column in the where clause, this                       function will be executed on every row of the table to check the condition – resulting in an                   Index Scan. Instead, we can re-write the query like this:

  select * from UserMaster where UserId like 'pa%'

• Do not call functions repeatedly within your stored procedures, triggers, functions and batches. While writing functions in the WHERE clause results in an Index scan and a very poor performance, functions used in the SELECT clause also affect the performance of a query at a smaller scale. Avoid Functions in SELECT clause wherever possible.
• Use Bulk Loads/Inserts for faster response times.
• Minimize the use of NULLs, as they often confuse front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form. 
• Use SET NOCOUNT ON at the beginning of stored procedures  to reduce network traffic.
• Off-load tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server 
• Avoid the use of cross joins, if possible. 
• Avoid dynamic SQL statements as much as possible. 
• In a table, if a column’s type is varchar, then use single quotes for the value. If single quotes are not provided, then SQL Server does an implicit conversion, which results in a Index Scan

         Example – Column OrgID is VARCHAR(10) 

   SELECT * FROM UserMaster WHERE OrgId = 2 

        This will result in an index scan, because SQL Server will implicitly convert the value “2” to               varchar.

SELECT * FROM UserMaster WHERE OrgId = '2' 

       This will result in an index seek, since there is no implicit conversion required.

• Avoid wildcard characters at the beginning of a word while searching using the LIKE keyword
• Avoid searching using not equals operators (<> and NOT)
• Select * from [table]  should be avoided. Always query for only the required number of rows – querying for all rows and filtering in the Business Layer or UI is a bad practice.
• When using a table variable in joins, and when the table variable is inserted with large amount of rows, use the RECOMPILE statement, so that SQL Server Optimizer will consider the large amount of rows and use the correct join method. 
• Use temporary tables for filtering /manipulating or implementation of business logic.
• While using temp tables ensure that only filtered data from the actual table is inserted into the temp table not the entire dataset. 
• Table partitioning can also have performance benefits but it needs to be evaluated before proceeding.
• Use joins instead of sub queries as the former is faster.