SQL Server Best Practices from Microsoft

I was just reviewing a document from Microsoft and came across these SQL Server 2008 / 2005 Best Practices to be adopted during Data Connectivity, Authentication, Development and Query Writing etc..

  1. Use Windows Authentication to connect to SQL Server if your environment supports the use of Windows or Active Directory security credentials, otherwise use SQL Server authentication. If you use SQL Server Authentication, encrypt your connection strings to protect them.
  2. When constructing a connection string, always specify the Initial Catalog attribute to ensure your application is in the correct database context. Do not rely on default database settings.
  3. When accessing data in SQL Server, production applications should not use server-level principals that are members of the sysadmin server role (like sa), or database-level principles that are members of the db_owner role (like dbo). Designing a secure application requires the developer to employ the principle of least privilege. To implement this, create database roles that mirror the different types of users in your application, and grant only the privileges that are required to those roles.
  4. When accessing data in SQL Server, production applications should not use server-level principals that are members of the sysadmin server role (like sa), or database-level principles that are members of the db_owner role (like dbo). Designing a secure application requires the developer to employ the principle of least privilege. To implement this, create database roles that mirror the different types of users in your application, and grant only the privileges that are required to those roles.
  5. When opening connections in C#, do so in the scope of a using statement to ensure that the connection is properly disposed of. The failure to dispose of connections could result in running out of available connections and runtime errors.
  6. When writing a Transact-SQL SELECT statement, always constrain the results using a WHERE clause to avoid returning data to your application that will not be used. Tables can get very large, and processing result sets requires memory and CPU resources both in SQL Server and your application.
  7. When designing your database, make sure to create a primary key for each table in your database. A primary key is a single column or combination of columns that can be used to uniquely identify each row in your table. Primary keys are implemented using a PRIMARY KEY constraint defined when a table is created or when adding an index to your table. SQL Server’s query optimizer can produce more efficient execution plans on tables that have primary keys.
  8. When designing your database, consider creating an index on SQL Server columns that are frequently referenced in the WHERE clause of SELECT statements. This may yield a tremendous performance improvement depending upon the size of your table, the distribution of data in your column and the frequency of updates. The Database Engine Tuning Advisor tool can provide recomendations on which columns should be indexed and the types of indexes to create.
  9. Use of parameterized commands is recommended for security. Hackers can use an approach called SQL injection to insert malicious SQL statements into Transact-SQL commands. To guard against SQL injection, never directly execute text entered by a user as a Transact-SQL command. Instead, only use pre-defined Transact-SQL commands or stored procedures and substitute data values provided by the user using parameters.
  10. Use of parameterized commands is also recomended for performance. SQL Server uses a sophisticated cost-based query optimizer to determine the most efficient execution plan for commands at run time. Using parameterized queries helps SQL Server re-use these plans from execution to execution which can save query optmization CPU cycles.
  11. When retrieving only a single value from SQL Server, use ExecuteScalar.  This is more efficient than using the ExecuteReader.
  12. When designing your application, create a class to work with your data as a business object in ASP.NET. This will make it easier to leverage powerful web server controls that support data binding. Classes will also make it easier to work with your data in code because of Visual Studio features like IntelliSense and AutoComplete.
  13. Use the SQL Server IDENTITY property to generate unique identifiers when you want SQL Server to manage the creation of unqiue identifiers that are pneumonic.
  14. To improve the multi-user concurrency of your application, limit the scope and duration of transactions to short operations that do not require user intervention to complete. Avoid using distributed transactions except in situations where they are absolutely necessary. If you must use them, strictly limit their scope and duration. Distributed transactions can introduce additional latency and can be complicated to configure, monitor and debug.