SQL Server: Coding Standards and best practices

Here I am writing some of coding standard and best practices one should follow while working with database. You can also check for Query Optimization and SQL Tunning

·         Use Pascal notation for SQL server Objects like Tables, Views, Stored Procedures. Also tables and views should have ending “s”. For example: UserDetails etc. Check naming convetion table given below for more details.

·         If “One Table” references “Another Table” than the column name used in reference should use the following rule: Column of another Table: <FirstTableName> Id

·         If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _. For example: meeting_Users, meeting_Attendee etc.

SQL Object	Pattern	Example
Stored Procedure	sp<Application Name>_[<group name   >_]<action type><table name > Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb       Note: Do not prefix stored procedure names with “SP_”, as “SP_” is reserved for system stored procedures.	spApplication_GetStudents
Triggers	TR_<TableName>_<action><description>	TR_Student_UpdateFirstName
Indexes	IX_<tablename>_<columns separated by_>	IX_Student_StudentId
Primary Key	PK_<tablename>	PK_Student
Foreign Key	FK_<tablename1>_<tablename2>	FK_Student_Class
Default	DF_<table name>_<column name>	DF_Student_FirstName

Naming Conventions

·         Do not use reserved words for naming database objects, as that can lead to some unpredictable situations.

·         Avoid the creation of temporary tables while processing data, as much as possible, as creating a temporary table means more disk IO. Consider advanced SQL or views or table variables or derived tables, instead of temporary tables. Keep in mind that, in some cases, using a temporary table performs better than a highly complicated query.

·         Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from Employees table:

    

SELECT MIN(Salary) FROM Employees WHERE EmpID IN (SELECT TOP 2 EmpID FROM Employees ORDER BY Salary Desc) The same query can be re-written using a derived table as shown below, and it performs twice as fast as the above query: SELECT MIN(Salary) FROM (SELECT TOP 2 Salary FROM Employees ORDER BY Salary Desc) AS A

·         Practice writing Upper Case for all SQL keywords. For example: SELECT, UPDATE, INSERT, WHERE, INNER JOIN etc.

·         Write comments in your stored procedures, triggers and SQL batches generously, whenever something is not very obvious. This helps other programmers understand your code clearly. Don't worry about the length of the comments, as it won't impact the performance Always create stored procedure in same database where its relevant table exists otherwise it will reduce network performance.

·         Do not query / manipulate the data directly in your front end application, instead create stored procedures, and let your applications to access stored procedure.

·         If you need to write query, try to use parameterized queries.

·         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.

·         Do not use wild card characters at the beginning of word while search using LIKE keyword as it results in Index scan.

·         Avoid using ntext, text, and image data types in new development work. Use nvarchar (max), varchar (max), and varbinary (max) instead.

·         Use Unicode datatypes, like NCHAR, NVARCHAR or NTEXT if it needed, as they use twice as much space as non-Unicode datatypes.

·         Columns with Default value constraint should not allow NULLs.

·         Minimize the use of Nulls. Because they incur more complexity in queries and updates. ISNULL and COALESCE functions are helpful in dealing with NULL values

·         Perform all referential integrity checks and data validations using constraints instead of triggers, as they are faster. Limit the use of triggers only for auditing, custom tasks, and validations that cannot be performed using constraints.

·         Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure.

·         Default constraint must be defined at the column level. All other constraints must be defined at the table level.

·         Use graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze SQL queries. Your queries should do an “Index Seek” instead of an “Index Scan” or a “Table Scan”.

·         With Begin and End Transaction always use global variable @@ERROR, immediately after data manipulation statements (INSERT/UPDATE/DELETE), so that if there is an Error the transaction can be rollback.

·         The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.

·         Effective indexes are one of the best ways to improve performance in a database application.

·         Views are generally used to show specific data to specific users based on their interest. Views are also used to restrict access to the base tables by granting permission on only views. Yet another significant use of views is that, they simplify your queries. Incorporate your frequently required complicated joins and calculations into a view, so that you don't have to repeat those joins/calculations in all your queries, instead just select from the view.

·         If you have a choice, do not store binary files, image files (Binary large objects or BLOBs) etc. inside the database. Instead store the path to the binary/image file in the database and use that as a pointer to the actual binary file. Retrieving, manipulating these large binary files is better performed outside the database and after all, database is not meant for storing files. However SQL Server 2012 comes with new feature, i.e. FileTable which can help you out in such situations.

·         Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime. IF and CASE statements come in handy to avoid dynamic SQL. Another major disadvantage of using dynamic SQL is that, it requires the users to have direct access permissions on all accessed objects like tables and views. Generally, users are given access to the stored procedures which reference the tables, but not directly on the tables. In this case, dynamic SQL will not work.

·         Offload 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.

·          As is true with any other programming language, do not use GOTO or use it sparingly. Excessive usage of GOTO can lead to hard-to-read-and-understand code.

·         Make sure you normalize your data at least till 3rd normal form. At the same time, do not compromise on query performance. A little bit of denormalization helps queries perform faster.