Rules for Database Coding

STORED PROCEDURES

Ownership

a)     The developer writing the Stored Procedure must provide his / her Full Name, Create Date, and brief Description of the Stored Procedure. This commented information should be the first part of the Stored Procedure.

-- Author         : Author Name

          -- Create date    : Date

      -- Description    : Brief Description      

SET Options

b)     The Following options should be set in Each Stored Procedure,

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

SET NOCOUNT ON 

Naming Conventions

a)     Stored Procedure name should start with ‘usp’. The next alphabet after ‘usp’ should be CAPITAL and each recognizable word in the name of Stored Procedure should start with CAPITAL letter. For example ‘uspInsertProcedure’ 

b)     INPUT parameters of Stored Procedure should start with ‘@p’ and end with ‘_IN’. For Example, @pInputVariable_IN.  The next alphabet after ‘@p’ should be CAPITAL and each recognizable word in the name of variable should start with CAPITAL letter. 

c)     OUTPUT parameters of Stored Procedure should start with ‘@p’ and end with ‘_OUT’. For Example, @pOutputVariable_OUT. The next alphabet after ‘@p’ should be CAPITAL and each recognizable word in the name of variable should start with CAPITAL letter. 

d)     Scope variables should start with ‘@’ sign, following with the initials of the data type of the respective variable. The next alphabet after ‘@’ + initials of the data type should be CAPITAL and each recognizable word in the name of variable should start with CAPITAL letter. For example, @intIntegerVariable, @varcVarcharVariable, @varbVarbinaryVariable, etc.

e)     Spaces should NOT be used in the Stored Procedures’ names. 

Data Types

a)     The data Types used in the Stored Procedure should be the SQL Server Native data Types. If any User Defined data type is used in the Stored Procedure, the comment must be added in the code.

b)     The length and precision should be provided in accordance with the usage of the variable.  

Return IDENTITY

a)     In order to return the IDENTITY value from INSERT Stored Procedures, SCOPE_IDENTITY should be used. 

b)     Proper OUTPUT parameter should be defined in the Stored Procedure to return the value of IDENTITY. 

Dynamic SQL

a)     Dynamic SQL should be deprecated as much as possible. But, if there is the need of the code, execute the SQL string using ‘sp_Executesql’ with proper parameters.

b)     In case of DDL Statements, EXECUTE can be used. 

Code Indenting

a)     The Code of the Stored Procedure should be properly Indented and readable. The spacing can be controlled by using TAB key (equal to 4 spaces).

b)     Each Block of code should begin with BEGIN keyword and end with END keyword. 

TRANSACTION

a)     The DML operations should be controlled by TRANSACTIONS. These Transactions should be synchronized with the Front-End transactions to avoid any conflict.

b)     Do not Set TRANSACTION ISOLATION LEVEL without consulting the Database Department 

TRY / CATCH

a)     TRY / CATCH Block should be the part of every Stored Procedure to control and record errors. 

Return ERROR_NUMBER

a)     Proper Error Number should be returned from the CATCH Block to the application.  

Nested SP calls

a)     Nested Stored Procedure calls should be deprecated. In case of necessity, the TRANSACTION should be applied to the master Stored Procedure, calling the rest of the Sps. 

  INSERT Statements

a)     INSERT Statements should contain the column names. 

  SELECT Statements

a)     SELECT Statements should contain the column names.

User-Defined Functions

Ownership

a)     The developer writing the Function must provide his / her Full Name, Create Date, and brief Description of the Function. This commented information should be the first part of the Stored Procedure.

-- Author         : Author Name

          -- Create date    : Date

      -- Description    : Brief Description    

SET Options

a)     The Following options should be set in Each Stored Procedure,

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

SET NOCOUNT ON 

Naming Conventions

a)     Function name should start with ‘fn. The next alphabet after ‘fn’ should be CAPITAL and each recognizable word in the name of Stored Procedure should start with CAPITAL letter. For example ‘fnMyFunction’ 

b)     Parameters of Function should start with ‘@p’. For Example, @pFunctionVariable.  The next alphabet after ‘@p’ should be CAPITAL and each recognizable word in the name of variable should start with CAPITAL letter. 

c)     Return variable of Function should start with ‘@pRet’. For Example, @pRetVariable_OUT. The next alphabet after ‘@p’ should be CAPITAL and each recognizable word in the name of variable should start with CAPITAL letter. 

d)     Scope variables should start with ‘@’ sign, following with the initials of the data type of the respective variable. The next alphabet after ‘@’ + initials of the data type should be CAPITAL and each recognizable word in the name of variable should start with CAPITAL letter. For example, @intIntegerVariable, @varcVarcharVariable, @varbVarbinaryVariable, etc.

e)     Spaces should NOT be used in the Function Names.

Data Types

a)     The data Types used in the Function should be the SQL Server Native data Types. If any User Defined data type is used in the Function, the comment must be added in the code.

b)     The length and precision should be provided in accordance with the usage of the variable.  

Code Indenting

a)     The Code of the Function should be properly Indented and readable. The spacing can be controlled by using TAB key (equal to 4 spaces).

b)     Each Block of code should begin with BEGIN keyword and end with END keyword.

SELECT Statements

a)     SELECT Statements should contain the column names.

b)     The Execution time of the function should not be greater that 1 sec.

Views

Schema

a)     The developer should specify the valid schema name for the view.

b)     The use of default schema ‘dbo’ should be deprecated as much as possible.

Select Columns

a)     SELECT Statements should contain the column names. The derived columns and case based values should have an understandable name and contained in [ ].

Use of Functions

a)     The Functions in the SELECT column list and WHERE clause of the VIEW should be deprecated. The SELECT list of the View should contain the values directly from the tables. The Functions should be used in the Queries on the VIEW for optimized execution.

Joins and Sub-Queries

a)     The Joins and Sub Queries should be based on the Indexed columns. If the Index does not exist on the columns used to join the tables, please contact the Database Department.

b)     The Sub Queries should be WHERE clause based. If the Sub Query is complex and contains more than 3 joins, try to make it another VIEW for the Sub Query.

Naming Conventions

a)     VIEW name should start with ‘VIEW_’. The next alphabet after ‘VIEW_’ should be CAPITAL and each recognizable word in the name of VIEW should start with CAPITAL letter. For example ‘VIEW_MyView’

b)     Spaces should NOT be used in the VIEW name.

Tables

Schema

a)     The developer should specify the valid schema name for the Table.

b)     The use of default schema ‘dbo’ should be deprecated as much as possible.

Naming Conventions

a)     Table name should start with ‘tbl’. The next alphabet after ‘tbl’ should be CAPITAL and each recognizable word in the name of VIEW should start with CAPITAL letter. For example ‘tblMyTable’.

b)     Spaces should NOT be used in the Table Names.

c)     Column name should be understandable and respective to the value stored in it. This adds complexity in query writing.

d)     Spaces should NOT be used in the column names. This adds complexity in query writing. 

Data Type

a)     The data type of the column should be according to the values stored in it. The nvarchar data type should be used instead of varchar for multi lingual support. Same is the case with text and char data types.

b)     The Integer data type should be used if the values in the table are surely to be greater than 32000. If the values will not go beyond that extent, use tinyint or smallint data type.

c)     Developer can consult the Database Department for assistance in the selection of right data type.

DEFAULT Values

a)     If the Column is set to not null, it is recommended to specify the DEFAULT Value. For example, the CREATEDATE column is made as NOT NULL most of the time. The DEFAULT value can be ‘getdate()’ for this column to avoid any insertion error. 

Maximum Length and Precision

a)     The length of varchar and nvarchar should be according to the maximum length of the expected data to be stored in the column.

b)     If the length of the column is undetermined, use varchar(max) or nvarchar(max) instead of text or ntext. The text and ntext data types will be removed in a future version of Microsoft SQL Server.

c)     The image data type should also be avoided as this data type will also be removed in the future versions of SQL Server.

d)     In case of Numeric data type (with decimal point and fixed precision and scale numbers) the storage varies as listed in the following table;

Precision	Storage bytes
1 – 9	5
10-19	9
20-28	13
29-38	17

The scale must be less than or equal to the precision and the precision can from 1 to 38 (as listed above).

e)     If the data is integral, use the following to assign data type,

Data type	Range	Storage
bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	8 Bytes
int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	4 Bytes
smallint	-2^15 (-32,768) to 2^15-1 (32,767)	2 Bytes
tinyint	0 to 255	1 Byte

  

SET Options

a)     Allow NULL option should be set according to the requirement.

b)     IDENTITY option should be set according to the requirement.

c)     In case of IDENTITY Column, remember to select SEED and INCREMENT options.

d)     DO NOT change the Collation of the column without consulting the Database department.

Description of Column

a)     Always specify brief description (not more than two lines) for each column for future reference and understanding.