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.
Thank you for the information
ReplyDeleteThanks for your feedback
DeleteNice information
ReplyDeleteThanks for your feedback
Delete