SQL SERVER – Guidelines and Coding Standards Part – 1

• Use “Pascal” notation for SQL server Objects Like Tables, Views, Stored Procedures. Also tables and views should have ending “s”.

Example:

UserDetails

Emails

• If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.

Example:

Page_ UserDetails

Page_ Emails

• Use following naming convention for Stored Procedure. sp<Application Name>_[<group name >_]<action type><table name or logical instance> Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb

Example:

spApplicationName_GetUserDetails

spApplicationName_UpdateEmails

• Use following Naming pattern for triggers: TR_<TableName>_<action><description>

Example:

TR_Emails_LogEmailChanges

TR_UserDetails_UpdateUserName

• Indexes : IX_<tablename>_<columns separated by_>

Example:

IX_UserDetails_UserID

• Primary Key : PK_<tablename>

Example:

PK_UserDetails

PK_ Emails

• Foreign Key : FK_<tablename_1>_<tablename_2>

Example:

FK_UserDetails_Emails

• Default: DF_<table name>_<column name>

Example:

DF_ UserDetails _UserName

• Normalize Database structure based on 3^rd Normalization Form. Normalization is the process of designing a data model to efficiently store data in a database. (Read More Here)

• Avoid use of SELECT * in SQL queries. Instead practice writing required column names after SELECT statement.

Example:

SELECT Username, Password
FROM UserDetails

• Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure. (Read More Here)

• Properly format SQL queries using indents.

Example: Wrong Format

SELECT Username, Password FROM UserDetails ud INNER JOIN Employee e ON e.EmpID = ud.UserID

Example: Correct Format

SELECT Username, Password
FROM UserDetails ud
INNER JOIN Employee e ON e.EmpID = ud.UserID

• Practice writing Upper Case for all SQL keywords.

Example:

SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE.

• It is common practice to use Primary Key as IDENTITY column but it is not necessary. PK of your table should be selected very carefully.

• If “One Table” references “Another Table” than the column name used in reference should use the following rule :

Column of Another Table : <OneTableName> ID

Example:

If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table.

• Columns with Default value constraint should not allow NULLs.

• Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.

• Always create stored procedure in same database where its relevant table exists otherwise it will reduce network performance.

• Avoid server-side Cursors as much as possible, instead use SELECT statement. If you need to use cursor then replace it next suggestion.

• Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement. (Read More Here)

INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE ....

• Avoid using spaces within the name of database objects; this may create issues with front-end data access tools and applications. If you need spaces in your database object name then will accessing it surround the database object name with square brackets.

Example:

[Order Details]

• Do not use reserved words for naming database objects, as that can lead to some unpredictable situations. (Read More Here)

• Practice writing comments in stored procedures, triggers and SQL batches, whenever something is not very obvious, as it won’t impact the performance.

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

• Indent code for better readability. (Example)

• While using JOINs in your SQL query always prefix column name with the table name. (Example). If additionally require then prefix Table name with ServerName, DatabaseName, DatabaseOwner. (Example)

• Default constraint must be defined at the column level. All other constraints must be defined at the table level. (Read More Here)

• Avoid using rules of database objects instead use constraints.

• Do not use the RECOMPILE option for Stored Procedure unless there is specific requirements.

• Practice to put the DECLARE statements at the starting of the code in the stored procedure for better readability (Example)

• Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure. (Example)

© Copyright 2000-2008 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (https://blog.sqlauthority.com)