SQL Server : Best Practices

This series of post consolidates links and materials for the best practices applicable to MS SQL Server for different stack holders point of view like Developers, DBA etc.  

SQL Server Best Pratices cum  DBA Checklist

On Redgate I found a very good link that covers wide spectrum of SQL Server Best Pratices check out here 

Developers Best Practices and Programming Tips

Using commas source code
When breaking lines for readability, it is a good idea to place commas at the beginning of the next line, rather than the end of the previous line, as shown in the preceding code sample. When you do this, you can remark out a line more easily for testing or troubleshooting. This practice also decreases the chances of parsing errors caused by missing or extra commas.

BETWEEN clause :

It is important to remember that the BETWEEN clause is inclusive of the outer values specified

in the range. For example, BETWEEN 1 AND 5 includes 1, 1.001, 1.11, and so on through 4.9, 4.99, and 5.0. This can sometimes be confusing when working with alphanumeric fields. If you are querying a book title column and you search BETWEEN 'S' AND 'Z', all book titles starting with S, even if only the single letter S, are returned, but at the other end of the range, a book with a title of simply Z is returned, but Zebras 101 is not returned.

WITH CUBE :

Avoid using the WITH CUBE operator on large tables where more than three columns exist in the GROUP BY clause. The WITH CUBE operator returns summary information for every column listed in the GROUP BY clause. These result sets can grow very quickly when additional columns are added to the GROUP BY clause and may slow performance drastically.

DML best Practices 

1. To minimize the possibility of data loss when issuing DML commands, you can follow several precautions.
2.  First, develop and test all code on a nonproduction server. Second, build the logic for the UPDATE, INSERT, or DELETE command as a SELECT statement to verify the correct result set is being returned.
3.   In addition, either executes the command as part of an explicit transaction and do not commit the transaction until you verify success, or turn on implicit transactions while you are building ad hoc queries to change data.  Remember that with implicit transactions, a DML statement automatically starts a transaction, but you must execute a COMMIT or ROLLBACK statement manually.

Table best Practices

    

      Naming Guidelines

§  Use PascalCasing (also known as upper camel casing).

§  Avoid abbreviations.

§  A long name that users understand is preferred over a short name that users might not understand.

    Choosing Data Types

§  Always use the data type that requires the least amount of disk space

§  In most cases use a variable-length data type, such as nvarchar, rather than a fixed- length data type, such as nchar.

§  fixed-length data type is preferred over a variable-length data type is if the column’s value changes frequently. If the column’s value is updated frequently

§  Avoid using the datetime and smalldatetime because they use more disk space and provide less precision than the new date, time, and datetime2 data types.

§  Use the varchar(max), nvarchar(max), and varbinary(max) data types instead of the text, ntext, and image data types, which might not be available in future releases of SQL Server.

§  Use the rowversion data type instead of the timestamp data type because the timestamp data type may not be available in future releases of SQL Server.

§  Only use the varchar(max), nvarchar(max), varbinary(max), and xml data types if a data type with a specified size cannot be used. This is because using the data types prevents you from being able to rebuild indexes online and because these data types cannot be used in the key of an index.

§  In general, never allow NULL because it is the simplest way to design the table. Allowing NULLs where you don’t need to do so greatly increases the potential for problems when querying your tables.

§  Data integrity needs be a part of your table definition from the beginning to make sure that you protect your data from faults.

Function and Where Clauses

You should not create queries that use a function in the WHERE clause because the function would have to execute for each potential row returned from the results of the FROM clause.

Use Log on Triggers to Maintenance Window

One of the more difficult tasks that database administrators face, particularly with Web- based applications, is getting all the users out of a database so that maintenance can be performed. Best method is to create a logon trigger that rejects logon attempts during your maintenance window. Then, all you need to do is disable the logon trigger following maintenance to return access to normal.

Measuring Query execution time

When using query execution times as a performance metric, it is typically a good idea to execute each query a few times and use either the lowest execution time or the median as the metric. Also, note that metrics in a test environment might not accurately reflect performance in a production environment. This depends on a number of factors, including how users actually interact with real-life data.

Index on Frequently Updated Tabled

Do not defines multiple index on most frequently updated columns because it degreed write performance