HEXAWARE TECHNOLOGIES

SQL SERVER Coding

Standards
Draft version 1.0

Hexaware Technologies

 Copyright 2008 by Hexaware Technologies Limited

All rights reserved. All text and figures included in the manual are the exclusive property of Hexaware
Technologies, and may not be copied, reproduced, or used in any way without the express permission in writing of
Hexaware Technologies.
 SQL Server Coding Standards
Version 1.0

Release Version Summary of Changes

Date No.

1.0

1.1

2
 SQL Server Coding Standards
Version 1.0

Table of Contents

1. INTRODUCTION .............................................................................................4
2 NAMING STYLES..............................................................................................4
2.1.1 Pascal Casing .........................................................................................................4
2.1.2 Camel Casing..........................................................................................................4
2.1.3 Upper Case..............................................................................................................4
3 NAMING..........................................................................................................5
3.1 TABLE NAMING STYLES............................................................................................................5
3.2 STORED PROCEDURE...............................................................................................................5
3.3 USER DEFINED FUNCTION........................................................................................................10
3.4 TRIGGER...........................................................................................................................10
3.5 INDEX..............................................................................................................................11
3.6 VIEWS.............................................................................................................................11
3.7 COLUMN...........................................................................................................................12
3.8 USER DEFINED DATA TYPE.....................................................................................................12
3.9 PRIMARY KEY.....................................................................................................................12
3.10 FOREIGN KEY..................................................................................................................13
4 CODING ........................................................................................................13
5 FORMATTING.................................................................................................16
6 GENERAL.......................................................................................................17
7 EXCEPTION HANDLING...................................................................................18

3
 SQL Server Coding Standards
Version 1.0

1. Introduction

Superior coding techniques and programming practices are hallmarks of a

professional programmer. The bulk of programming consists of making a large number
of small choices while attempting to solve a larger set of problems. How wisely those
choices are made depends largely upon the programmer's skill and expertise. This
document defines a set of guidelines as a way to help class library designers more fully
understand the trade-offs between different solutions. Furthermore this document
addresses some fundamental coding techniques and provides a collection of coding
practices from which to learn. The coding techniques are primarily those that ensure a
high level of quality (with respect to maintainability, extensibility, modularity, security,
testability, documentation, etc.) of the code, whereas the programming practices are
mostly performance enhancements. Hence by following a common coding standard
ensures that all the code in the system looks as if it was written by a single — very
competent — individual and the code looks familiar, in support of collective ownership.

2 Naming Styles

2.1.1 Pascal Casing

This convention capitalizes the first character of each word.

 Use this style for type and method names

2.1.2 Camel Casing

This convention capitalizes the first character of each word except the first one.
 Use this for local variables

2.1.3 Upper Case

Only use all upper case for identifiers if it consists of an abbreviation, which is
one or two characters long, identifiers of three or more characters should use
Pascal Casing instead.

4
 SQL Server Coding Standards
Version 1.0

3 Naming
3.1 Table Naming Styles

 Use Pascal Casing.

 Prefix table name with Project name and TBL keyword and suffix with the
type of table.

Rule: PROJECT_TBL_TABLENAME_MST for Master tables

Example: AMS_TBL_CUSTOMER_MST

Rule: PROJECT_TBL_TABLENAME_DTL for transaction/detail tables

Example: AMS_ TBL_CUSTOMER_DTL

 If the table is a composite of Table 1 and Table 2 then name it as follows

Rule: TBL_TABLENAME1_TABLENAME2_DTL
Example: AMS_ TBL_CUSTOMER_ADDRESS_DTL

Where AMS_ TBL_CUSTOMER_ADDRESS_DTL is the composite

form of Customer and Address table

 If the table is for audit trail purpose add _LOG at the end of the table name.

Rule: TBL_TABLENAME_LOG
Example: AMS_TBL_FIXEDASSETADDITION_LOG

3.2 Stored Procedure

 Prefix Stored Procedure name with Project name and the keyword SP.

Rule: PROJECT_SP_STOREDPROCEDURENAME
Example: AMS_SP_GETCUSTOMERDETAILS

 All Stored Procedure should have the details as follows.

/*
***********************************************************************************************

5
 SQL Server Coding Standards
Version 1.0

** SP Name : BANK_SP_MONTHWISEBALANCE **
** Created By : xxxxxx **
** Date : dd-mm-yyy **
** Inputs : LedgerId As Int **
** CompanyId As Int **
** FinancialYear As Varchar(7) **
** FromDate As DateTime **
** ToDate As DateTime **

** Outputs : (Output in the form of table) **

** MonthName As VarChar **
** CrBalanceSum As Decimal(18,2) **
** DrBalanceSum As Decimal(18,2) **
** ClosingBalance As Decimal(18,2) **
** CrDrType As Varchar **
** Purpose : Calculate Monthwise Balance **
** Callee : [Link]
** **
************************************************************************************************
************************************************************************************************
** Change History
**
************************************************************************************************
** Date: Author: Description:
**
** -------- -------- -----------------------
---------------- **
** dd/mm/yyyy xxxxx Save Transaction details for
the Company
*************************************************************************************************

NOTE: This stored proc returns a table with all the month names. The First
record alone will be the Opening Balance. Other records mention the Month
Name.
*/

 Use SET NOCOUNT ON at the beginning of stored procedures. This

suppresses messages like '(1 row(s) affected)' after executing INSERT,
UPDATE, DELETE and SELECT statements. This inturn improves the
performance of the stored procedures by reducing the network traffic.

 Return multiple result sets from one stored procedure to avoid trips
from the application server to SQL server.

 Do not use the RECOMPILE option for stored procedures.

6
 SQL Server Coding Standards
Version 1.0

 Avoid using cursor or application loops to do inserts instead use

INSERT INTO.

 Place all DECLARE statements before any other code in the procedure.

Example:

Create Procedure <Procedure Name>

as
Declare @FltOpeningBalance Decimal(18,2)
Declare @BooEntryType Varchar(25)
Declare @IntCount Integer
Declare @StrMonthName Varchar(30)
Declare @FltCrSum Decimal(18,2)
Begin
<Select Statement>
End
 Check the global variable @@ERROR immediately after executing a
data manipulation statement (like INSERT/UPDATE/DELETE), so that you can
rollback the transaction if an error occurs

Example:

Insert into BANK_TBL_ GLFixedAsset

(
AuthId,
SATransactionTypeId,
[Group],
SAUserId,
Priority,
AuthStatus,
Summary
)
Set @Error = @@Error
Set @Rowcount = @@Rowcount
If (@Error <> 0 Or @Rowcount = 0)
Begin
<RollBack Transaction>
End

 Avoid using TEXT or NTEXT datatypes for storing large textual

[Link] the maximum allowed characters of VARCHAR instead.
 Do not call functions repeatedly within your stored procedures,
triggers, functions and batches.
 The RETURN statement is meant for returning the execution status
only, but not data.

Example:

7
 SQL Server Coding Standards
Version 1.0

Set @Error = @@Error

Set @Rowcount = @@Rowcount

If (@Error <> 0 Or @Rowcount = 0)

Begin

return 1

End

 Do not use SELECT * in your queries. Always write the required

column names after the SELECT statement, like SELECT CustomerID,
CustomerFirstName, City. This technique results in less disk IO and less
network traffic and hence better performance.

 Do not call functions repeatedly within stored procedures.

For example

The need to find the length of a string variable in many places of

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 prefix stored procedure names with 'sp_'. The prefix sp_ is
reserved for system stored procedure that ship with SQL Server. Whenever
SQL Server encounters a procedure name starting with sp_,, it first tries to
locate the procedure in the master database, then looks for any qualifiers
(database, owner) provided, then using dbo as the owner. This can really
save time in locating the stored procedure by avoiding sp_ prefix. But there is
an exception! While creating general purpose stored procedures that are
called from all your databases go ahead and prefix those stored procedure
names with sp_ and create them in the master database.

8
 SQL Server Coding Standards
Version 1.0

 Add a @Debug parameter to stored procedures. This can be of bit

data type. When a 1 is passed for this parameter, print all the intermediate
results, variable contents using SELECT or PRINT statements and when 0 is
passed do not print debug information. This helps in quick debugging of
stored procedures, as the need to add and remove these PRINT/SELECT
statements before and after troubleshooting problems.

 If stored procedure returns a single row resultset, consider returning

the resultset using OUTPUT parameters instead of a SELECT statement, as
ADO handles output parameters faster than resultsets returned by SELECT
statements.

 Always access tables in the same order in all stored procedures

consistently. This helps in avoiding deadlocks.

 Keep transactions as short as possible. Do not wait for user input in

the middle of a transaction.

 Do not use higher level locking hints or restrictive isolation levels

unless they are absolutely needed.

 Front-end applications should be deadlock-intelligent, that is, these

applications should be able to resubmit the transaction incase the previous
transaction fails with error 1205. Process all the results returned by SQL
Server immediately, so that the locks on the processed rows are released,
hence no blocking.

 Try to avoid server side cursors as much as possible.

 Avoid the creation of temporary tables while processing data, as much

as possible, as creating a temporary table means more disk IO.

9
 SQL Server Coding Standards
Version 1.0

 Make sure stored procedures always return a value indicating the

status. Standardize on the return values of stored procedures for success and
failures. The RETURN statement is meant for returning the execution status
only, but not data. Use OUTPUT parameters to return data.

3.3 User defined function

 Prefix Function Name with Project name and the keyword FN.

Rule: PROJECT_FN_FUNCTIONNAME

Example: BANK_FN_GETLASTDAYOFMONTH

 Avoid using Functions within Stored Procedure.

 Avoid using cursor inside a function.

Example:

create FUNCTION [FNSAAuthAwaited](@SAAuthId int)

RETURNS char(256)
as
Begin
DECLARE @Summary varchar
<select Statement>
return @Summary
End

3.4 Trigger

 Prefix Triggers with the Project name and the keyword TRG.

Rule: PROJECT_TRG_TABLENAME_<’_’Separated
Actionnames>_TriggerName

Example: AMS_TRG_CUSTOMER_INS_VALIDATECUSTOMER

VALIDATECUSTOMER is the trigger name executed on table

CUSTOMER when inserting a row. AMS is the project name.

 Use SET NOCOUNT ON at the beginning of triggers in production

environments, as this suppresses messages like '(1 row(s) affected)' after

10
 SQL Server Coding Standards
Version 1.0

executing INSERT, UPDATE, DELETE and SELECT statements. This inturn

improves the performance of the triggers by reducing the network traffic.

 Perform all referential integrity checks, data validations using constraints

(foreign key and check constraints). These constraints are faster than
triggers. So, use triggers only for auditing, custom tasks and validations that
can not be performed using these constraints. These constraints save time
as well, as don't have to write code for these validations and the RDBMS will
do all the work.

3.5 Index
 Prefix Index with Project name and Index Type and suffix with the Column
on which the index has to be set.

Rule: PROJECT_INDEXTYPE_ TABLENAME_COLUMNNAME

Example: AMS_UNQ_CUSTOMER_CODE
AMS_CLS_CUSTOMER_CODE (for Clustered)
AMS_NCL_CUSTOMER_CODE(for non-clustered)

 Try to avoid wildcard characters at the beginning of a word while searching

using the LIKE keyword, as that results in an index scan, which is defeating
the purpose of having an index. The following statement results in an index
scan, while the second statement results in an index seek.

1. SELECT LocationID FROM Locations WHERE Specialities LIKE

'%pples'
2. SELECT LocationID FROM Locations WHERE Specialities LIKE 'A%s'

 All Queries should do 'Index seeks' instead of 'Index scans' or 'Table scans'.

3.6 Views
 Prefix Views with Project name and keyword VW

Rule: PROJECT_VW_TABLENAME
Example: AMS_VW_CUSTOMER

 Views are also used to restrict access to the base tables by granting
permission on only views.

11
 SQL Server Coding Standards
Version 1.0

 Frequently required complicated joins and calculations can be

simplified into a view, and instead use the view.

CREATE VIEW [Link] [(Column[,n])]

select statement
[WITH CHECK OPTION]
Example:
CREATE VIEW uk_employee_view
SELECT * FROM employee WHERE country = 'UK'
WITH CHECK OPTION

3.7 Column
 Prefix Column with Project name and Table Name and suffix with Data Type.

Rule: PROJECT_TBL_COLUMNNAME_DATATYPE
Example: AMS_CUST_CODE_INT

3.8 User Defined Data Type

 Prefix User Defined Data Type name with Project and UDT keyword and
Database name and suffix with data type.

Rule:PROJECT_UDT_DATABASENAME_TYPENAME_BASEDATATYPE
Example: AMS_UDT_MDL_SSN_VCH
- UDT Created in Model DB
- Type Name = SSN of Varchar Datatype

 Use 'User Defined Datatypes', if a particular column repeats in a lot of

tables, so that the datatype of that column is consistent across all your
tables.
 Avoid user-defined data types. While these are legitimate database
constructs, opt for constraints and column defaults to hold the database
consistent for development and conversion coding.
 Once the user-defined data type is referred by other tables it cannot
be changed or deleted.

3.9 Primary Key

 Prefix Primary Key with Project name and PK keyword and Table
name.

Rule: PROJECT_PK_TABLENAME_COLUMNNAME
Example: AMS_PK_CUST_CODE

 Each table should have a Primary Key field.

12
 SQL Server Coding Standards
Version 1.0

 Primary key will create clustered index by default.

 Only one Primary key can be created for a table.
 Multiple columns can be consolidated to form a single primary key.
 Primary key does not allow null values.

3.10 Foreign Key

 Prefix Foreign Key with Project name and FK keyword and Table name.

Rule: PROJECT_FK_TABLENAME_COLUMNNAME
Example: AMS_FK_CUST_DEPT

 Foreign key is a column or set of columns in one table that refer to the
primary key of another table.

 The reference from the foreign key to the primary key is the mechanism
whereby the relationships between the occurrences of rows in the two
tables are enforced.

 The foreign key column establishes a direct relationship with a primary

key or unique key column (referenced key) usually in another table.

 The table containing the foreign key is referred to as the child, and the
table containing the referenced key is the parent table.

 The foreign key and referenced key can be in the same table (parent
and child are the same table); otherwise, the parent and child tables must be
in the same database.

4 Coding

 Do not use SELECT *. Always write the required column names after
the SELECT statement, like SELECT CustomerID, CustomerFirstName, City.
This technique results in less disk IO and less network traffic and hence better
performance.
 Try to avoid server side cursors as much as possible. Cursors can be
easily avoided by SELECT statements in many cases. If a cursor is
unavoidable, use a simpleWHILE loop instead, to loop through the table.
 Use 'Derived tables' wherever possible, as they perform better.
Consider the following query to find the second highest salary from
Employees table.

13
 SQL Server Coding Standards
Version 1.0

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
 Use the more readable ANSI-Standard Join clauses instead of the old
style joins. With ANSI joins the WHERE clause is used only for filtering data.
Where as with older style joins, the WHERE clause handles both the join
condition and filtering data. The first of the following two queries shows an
old style join, while the second one shows the new ANSI join syntax.

SELECT a.au_id, [Link]

FROM titles t, authors a, titleauthor ta
WHERE a.au_id = ta.au_id AND
ta.title_id = t.title_id AND
[Link] LIKE '%Computer%'

SELECT a.au_id, [Link]

FROM authors a
INNER JOIN
titleauthor ta
ON
a.au_id = ta.au_id
INNER JOIN
titles t
ON
ta.title_id = t.title_id
WHERE [Link] LIKE '%Computer%'

 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

14
 SQL Server Coding Standards
Version 1.0

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 so
dynamic SQL will not work.
 Use variables instead of constant values within SQL statements,
improves readability and maintainability of code.
Example:
UPDATE [Link]
SET OrderStatus = 5
WHERE OrdDate < '2001/10/25'

The same update statement can be re-written in a more readable form as

shown below

DECLARE @ORDER_PENDING int

SET @ORDER_PENDING = 5

UPDATE [Link]
SET OrderStatus = @ORDER_PENDING
WHERE OrdDate < '2001/10/25'

 Avoid using <> as a comparison operator. Use ID IN(1,3,4,5) instead

of ID <> 2.
 Do not use cursors or application loops to do inserts. Instead use
INSERT INTO.
 Fully qualify tables and column names in JOINs.
 Always use a column list in your INSERT statements. This helps avoid
problems when the table structure changes (like adding or dropping a
column).
 Do not use COLUMN numbers in the ORDER BY clause as it impairs the
readability of the SQL statement. Further, changing the order of columns in
the SELECT list has no impact on the ORDER BY when the columns are
referred by names instead of numbers. Consider the following example, in
which the second query is more readable than the first one

Example:
SELECT OrderID, OrderDate
FROM Orders
ORDER BY 2

SELECT OrderID, OrderDate

FROM Orders
ORDER BY OrderDate

15
 SQL Server Coding Standards
Version 1.0

 Avoid using GOTOs. GOTO statement is considered unstructured and

there is always a possibility of jumping out of hands. But there are
circumstances where using GOTO statement cannot be avoided.
 Always use the schema also while calling sql objects.
Example:
EXEC [Link].
 Also, use only necessary columns.
Example: If there are columns A & B and there is a need to store there sum
in another column C. Instead of creating a Column named C use the following
query.
SELECT A, B, (A+B) AS C FROM TABLE1.
 Always use column names in SELECT, INSERT, UPDATE statements.
Avoid using asterisks –(*).

5 Formatting
 Use upper case for all SQL keywords
SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.

 To make SQL Statements more readable, start each clause on a new

line and indent when needed.

Example

SELECT title_id, title

FROM titles
WHERE title LIKE 'Computing%' AND
title LIKE 'Gardening%'
 Comment code blocks that are not easily understandable
 Use single-line comment markers(--).
 Reserve multi-line comments (/*.. ..*/) for blocking out
sections of code.

 Keep all the statement in upper case.

 Use lowercase for data-types.
 Use Upper Camel notations (also known as Pascal Casing) for all user
created objects.
Example: @EmployeeCode
 Use meaningful user-defined identifiers. Use only names which contain
A-Z, a-Z, 0-9 and underscore character. Do not use regional special
characters.
 Use BEGIN and END to specify block of statements

16
 SQL Server Coding Standards
Version 1.0

 Use proper commenting. Also add the purpose, author, date, version
etc. details on top of all the procedures, functions etc.
 Add test data with statements inside stored procedures and comment
it. This helps you in future debugging. Also this helps another developer to
get a quick start.
Example:
--Exec dbo.TRI_ProfitLoss 5,'2004-05',0,0,0,0,0,0,0,0,0,0
--Exec TRI_MonthwiseBalance 261,25,'2004-05','04/1/2004','11/30/2004',0,0,0,0

6 General
 Divide your large table to small multiple tables. (In SQL terminology it
is called as Normalization).

 The performance of your database increases if the primary key is

numeric or small in data width.

 Use the right data types and widths when defining columns.

Example:

To store the 'age', then no need to use the VARCHAR field since a TINYINT
can do the job. (TINYINT can store integer data from 0 to 255 as the fact
that 'age' value does not exceeds 255).

 Use stored procedures and functions instead of writing all the messy
code in the program itself. It not only improves the performance but also a
matter of security.

 Proper INDEX-ing. There will be noticeable difference when applying

INDEX on very large tables.

 Avoid using CURSORs. Use only when there is no other way exists. In
most cases CURSOR consumes much time since it is a record-by-record
process.

 Use transactions to avoid loss of data while the execution of a stored

procedure. The possibility of failure is high. There can be a data truncation
problem, network problem etc. In MSSQL there are statements like BEGIN
TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION which
needs to be used along with the error handling methods in such
[Link] performance TRANSACTION is one step down when
comparing with ordinary statements but when considering quality of the
product it is very high.

17
 SQL Server Coding Standards
Version 1.0

 Avoid using dynamic SQL statements inside stored procedures. i.e., Do

not use SQL statements to create SQL statements.

 Use the lookup tables. This will help reduce the overload of tables.

Example:

If there is a product-sales table and there is a need to store 3 pictures

of the same product, rather than adding three columns to the table
use one lookup table. This also helps to add any number of photos for
the same product.

 Always try to keep one error table to log all the errors which comes
from the stored procedures or functions. In T-SQL (version < 2000) the global
variable @@ERROR can be used to detect errors. From 2005 or greater
versions TRY/CATCH is available. Its time saving and helps in finding the error
easily.

 Avoid IF and start using CASE.

7 Exception Handling
 TRY/CATCH block whenever you use an explicit transaction and whenever
you modify data. Some practitioners advocate using TRY/CATCH blocks in
every stored procedure in order to log any exception that occurs in the
database.

Example:

BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT 'Error Caught'
SELECT
ERROR_MESSAGE(),
ERROR_NUMBER()
END CATCH

Using Try Catch in Transaction

BEGIN TRY
BEGIN TRANSACTION
INSERT Funds VALUES (10)
INSERT Funds VALUES (-1)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK

18
 SQL Server Coding Standards
Version 1.0

END CATCH

19
 SQL Server Coding Standards
Version 1.0

20
 SQL Server Coding Standards
Version 1.0

21
 SQL Server Coding Standards
Version 1.0

22