SQL Server – SQL Coding Conventions

Microsoft SQL Server

SQL Coding Conventions

Trans Solutions Systems S.A

Version: 1.0
Creation date: Dec 2002
By: Database Team

 2002 – TRANSOLUTIONS SYSTEMS 1 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

Content

CONTENT................................................................................................................................................2

CODING CONVENTIONS.....................................................................................................................3
INTRODUCTION.......................................................................................................................................3
GENERAL GUIDELINES..........................................................................................................................3
TABS.......................................................................................................................................................4
SPACING.................................................................................................................................................5
DATABASE OBJECTS..........................................................................................................................5
TABLES...................................................................................................................................................5
Application Design............................................................................................................................5
Data Model........................................................................................................................................5
Table Structure..................................................................................................................................6
Columns.............................................................................................................................................6
Standard Columns..............................................................................................................................6
Nulls...................................................................................................................................................6
Scripts................................................................................................................................................7
VIEWS.....................................................................................................................................................7
Introduction.......................................................................................................................................7
Design................................................................................................................................................8
Script..................................................................................................................................................8
STORED PROCEDURES.........................................................................................................................8
Introduction.......................................................................................................................................8
Design................................................................................................................................................8
Default Settings..................................................................................................................................8
Structure.............................................................................................................................................9
Header Description............................................................................................................................9
CONSTRAINTS......................................................................................................................................10
Introduction.....................................................................................................................................10
Types................................................................................................................................................10
CURSORS.............................................................................................................................................11
Introduction.....................................................................................................................................11
Structure...........................................................................................................................................11
RULES AND DEFAULTS........................................................................................................................11
Introduction.....................................................................................................................................11
TRIGGERS............................................................................................................................................11
Introduction.....................................................................................................................................11
Header Description..........................................................................................................................12
INDEXES...............................................................................................................................................13
Introduction.....................................................................................................................................13
Design..............................................................................................................................................13
Script................................................................................................................................................13

 2002 – TRANSOLUTIONS SYSTEMS 2 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

Coding Conventions

Introduction
The purpose of this document is to outline the standards and conventions for writing SQL in a
SQL Server environment. This relates to the following SQL code:
 Tables
 Views
 Stored Procedures
 Constraints
 Cursors
 Rules
 Triggers
 Indexes

This document contains T-SQL structures and other topics intended to establish a consistent,
'standard' approach to developing SQL Server database applications. The focus is the Server
environment.

General Guidelines
Remember the following points:

1. At the beginning of each stored procedure and trigger should be included the SET
NOCOUNT ON statement.

2. Avoid to use cursors. They generally use a lot of SQL Server resources and reduce the
performance and scalability of the applications. When is necessary to perform row-by-row
operations, try to find another method to perform the task. Some options are to perform
the task at the client, use temporary tables, use derived tables, use a correlated sub-
query, or use the CASE statement.
In any case use lowest cost cursor required to do task, a good practice is uses a FAST
FORWARD cursor.

Example:

 2002 – TRANSOLUTIONS SYSTEMS 3 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

Declare cur_customer cursor FAST_FORWARD for

Select name from customer where customer_id < 100

3. In SQL 2000, Use table variables (data type Table) instead of temporary tables, whenever
possible. Transactions involving table variables last only for the duration of an update on
the table variable. Thus, table variables require less locking and logging resources.

4. Using sp_executesql is recommended over using the EXECUTE statement to execute a

string. Not only does the support for parameter substitution make sp_executesql more
versatile than EXECUTE, it also makes sp_executesql more efficient because it
generates execution plans that are more likely to be reused by SQL Server.

5. The use of “Select * from" will not be accepted and should be replaced by naming the
required fields.

6. When using the UNION statement, keep in mind that by default it performs the equivalent
of a SELECT DISTINCT on the results set. If you know that there will not be any
duplication of rows created as a result of using the UNION statement, then use the
UNION ALL statement instead. This variation of the statement does not look for duplicate
rows and runs much faster than the UNION, which does look for duplicate rows, whether
or not there are any

7. Avoid to use functions on a column in the WHERE clause, for example, the clause:
WHERE SUBSTRING(firstname,1,1) = 'm'
can be rewritten like this:
WHERE firstname like 'm%'

8. When is possible should be used the NOLOCK hint in the SELECT sentences. It specifies
that dirty reads are allowed, which means that no shared locks are issued.

9. Use joins in preference to sub or nested queries for improved performance.

10. Use INSERT...SELECT in preference to SELECT...INTO, to avoid extensive locks.

Tabs
Sounds like a minor item, but the formatting of anything starts with tabs, they can really help
of hurt someone when editing or even viewing the scripts. For our standard the tab is
configured to 4 spaces. Enough to see a difference and still fit fairly long statements on the
screen completely.

 2002 – TRANSOLUTIONS SYSTEMS 4 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

Spacing
The purpose is to use tabs and spaces judiciously to make each area easier to read. The
general rule is to indent using tabs, except for the major section of a query. Each of those is
one space down from the query type (insert, select, etc.). An example looks like:
Select customer_id
,customer_name
,customer_code
from customer cust
where [Link] = 5
and cust.address_name is not null

Database Objects
Tables

Introduction
Tables are the core of the database and care needs to be taken in building these. The data
model is the key source component, together with a comprehensive and current technical
design of the application. The following notes contain the standards for database tables,
encompassing for example, tables, columns, and nulls.

Application Design
An application design must exist, encompassing the business processes and data within the
scope of the application for which a database is required. This design will identify all tables
required in the database and will include business rules, entity descriptions, details of
attributes of these entities, relationships between entities, etc. The design will be supported
by definitions of data access, reporting requirements, etc.

Data Model
The logical data model - in the absence of a physical data model - is the key input to the
database build process. The logical model needs to fully reflect the scope of the database
required by the intended application. In particular the data model will be:
 Normalized (3NF) for OLTP databases. It is acceptable for OLAP (Datawarehouse
and Datamart) databases to have not normalized data. This denormalisation must
take into consideration the tradeoff between volume and performance)
 Commented in detail
 Version controlled
 Up to date

 2002 – TRANSOLUTIONS SYSTEMS 5 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

Table Structure
Tables are developed from the logical data model, as modified (ie, physical data model or
other design record). Tables should be normalized - a characteristic of normalization is that
you have one fact, one place, and one time. Typical departures from normalization rules
which maybe acceptable include:
 Use of an artificial identity column
 Time/date manipulation
 Holding derived data at a 'parent' level

The combined criteria for accepting a not normalized design are:

 Performance improvement is achieved, specific and overall.
 There is no harm to data or database integrity

Columns
Columns reflect the attributes defined in the logical data model. Columns should not be added
to a table unless first reflected in the design documentation.

Take care when defining the data type for each column, in particular to ensure the type
represents the business context, that fixed columns cannot be replaced by variable columns
and that inherent ranges (small and tiny) are adequate for future needs. Avoid use of
timestamp data type - it is easily confused with a date/time data type.
Column names will be formed using the same standard rules defined for table names.
Typically a column name will reflect the attribute name in the logical data model.

Standard Columns
For each OLTP transactional tables the following three columns will be included with the
defined default values. They are required for minimum auditing purposes. Additional columns
or tables may be required for auditing. This will be defined by business requirements

Column Name Default Value

processing_date_time getdate()
host_name host_name()
db_user_name user_name()

Nulls
Unknown values - nulls - potentially represent major problems in database applications, both
at design and when in use. An unknown value is not a dash, or a space, or a zero - it is
unknown - null.

 2002 – TRANSOLUTIONS SYSTEMS 6 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

Generally a column should not allow unknown values when data is entered. The default
column definition therefore is: 'Not Null'. Maintenance of this rule may require default values.

In SQL Server 7 if column nullability is not specified and ANSI null default is false - the
default – NOT NULL is assigned automatically to the column. However, because the SQL
Server ODBC driver and the OLE DB provider both default ANSI_NULL_DFT_ON set to ON,
the default nullability may be reversed inadvertently.
Thus all columns should be defined with explicit nullability (preferably NOT NULL) in the
CREATE TABLE script.

Scripts
An IF EXISTS and DROP process should precede each relevant CREATE TABLE statement.
Layout the create table command so that the structure is clear. Place commas at the end of
lines, not at the beginning of lines. Use tabs and white space to layout the script.
Example:

If exists(select 1 from sysobjects where name = ‘my_new_table’ and type = ‘U’)

drop table my_new_table
go
create table my_new_table
(
column_one varchar(30) not null,
column_two varchar(25) not null,
column_three varchar(25) not null
)
Add constraints, indexes, and other objects owned by the table object in a separate step
using ALTER TABLE commands.

Views
Introduction
Views serve a number of purposes, such as hiding table structures, providing horizontal and
vertical security and managing partitioned data (where partitioned data represents a not
normalized table structure). Reports, for example, can be driven by views, and export of data
can be enabled via a view. Nested views are not recommended, especially if one of the
nested views contains outer joins.
Also, it is not recommended that views be used to modify (update or delete) data; these
processes should be performed using stored procedures against tables.
So if database performance is your goal, avoid using views.

 2002 – TRANSOLUTIONS SYSTEMS 7 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

Design
It is unlikely that all view designs will be available in initial design documentation. However,
once the need for a view has been determined, the design must be documented. This design
documentation is subject to version control.

Script
An IF EXISTS and DROP process should precede each relevant CREATE VIEW statement.
Layout the create view command so that the structure is clear, using tabs and white space as
needed.

Stored Procedures
Introduction
Stored procedures are a significant component of SQL Server and provide opportunities to
utilise T-SQL, cursors and transactions, although not necessarily all together. Stored
procedures should be based on well-structured designs and should be comprehensively
commented.

Design
The design for each procedure should describe for example, purpose, output, input. The
following checklist should be used to assess adequacy of design of stored procedures:
The purpose is stated.
 User task/s relationship defined.
 Process steps are identified.
 Assumptions are documented.
 Inputs are identified.
 Outputs are identified.
 Test cases are available.
 Error management is defined.
 Processing volume is defined.
 Response/timing requirements stated.

Default Settings
In this standard, SQL Server is assumed that session settings are set as below, when
creating stored procedures. Note that the stored procedure 'remembers' the settings that
were in place when the procedure was created, and will run (execute) with those
'remembered' settings. Also note that ODBC and OLE DB session level settings override
default settings for the session; thus, in any session, explicitly set the settings as required by
the standard. If possible, ensure all sessions/connections have the same settings.

 2002 – TRANSOLUTIONS SYSTEMS 8 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

ANSI_NULLS (Do not confuse with ANSI null default setting). When true, null comparisons
will evaluate to null (unknown), this is the SQL-92 standard. Set ON.
ANSI_WARNINGS When true, warning messages are generated for null values in aggregate
functions and when divide by zero and overflow errors occur. Set ON.
QUOTED_IDENTIFIER SQL Server will follow SQL-92 rules for delimiters when true. Set ON.
Do not set these in the stored procedure; rather, set them for the session used to create the
stored procedure. Thus the settings should be included in the script that drops and creates
the stored procedure. To configure in Query Analyzer tool do the following:
SQL Server 7: Go to File/Configure menu option, choose the New connections tab and set
ON the Use ANSI quoted and Use ANSI nulls options.
SQL Server 2000: Go to Tools/Options menu option, choose the Connection properties tab
and set ON the Set ansi nulls, Set ansi warnings and Set quoted identifier options.

Structure
The following structural requirements apply:
 Ensure variables and parameters match table data columns in type and size.
 Ensure all variables and parameters are used or else deleted.
 Keep temporary objects local in scope wherever possible.
 Limit use of temporary table to that created in procedure.
 Maintain the logical unit of work requirement; do not create extensive or long running
processes where these can be shortened.
 Do not use SELECT * in any code.
 Layout the procedure with indents, blocks, tabs and white spaces.
 Use lower case in all the code.
 Comment procedures extensively to ensure the processes are identified.
 Avoid GOTO - exception is use for error handling.
 Avoid implicit resolution of object names - make sure all objects are owned by dbo.

Header Description
/*******************************************************************/
/* Name: <Name of the stored procedure> */
/* Copyright: <Company name > */
/* Written by: <Procedure author name> */
/* Creation Date: <Procedure creation date> */
/* Document: <Document - Number> */
/* */
/* Purpose: <Purpose of the script> */
/* --------------------------------------------------------------- */
/* */
/* Updates: */
/* Author/ Date/ Purpose */
/* */
/*******************************************************************/

 2002 – TRANSOLUTIONS SYSTEMS 9 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

Example:

/*******************************************************************/
/* Name: spu_generate_id */
/* Copyright: Trans Solutions Systems S.A */
/* Written by: Orlando Cacha Antigua. */
/* Creation Date: January 04, 2000 */
/* Document: API_FLSWEB - 17 */
/* */
/* Purpose: Generate the numeric sequence. */
/* ------------------------------------------------------------- */
/* Updates: */
/* 1. */
/* Orlando Cacha/ Jul 02, 2001/ Modify the procedure to update the */
/* sequence in different tables according the module. */
/* */
/* 2. */
/* David Sandoval/ Dec 02, 2002/ The output parameter has been */
/* modified from numeric(2) to int in order to avoid overflow. */
*/
/* */
/*******************************************************************/

Parameter Description
create procedure dbo.spu_bkg_sel_booking_flat_file
@book_no varchar(16),
@error_code int out,
@error_message varchar(150) out
as
/*------------------------------------------------------------------
Parameters

book_no : Booking Number

error_code: Error code
error_message: Error message
------------------------------------------------------------------
*/

Constraints
Introduction
Constraints are one of the tools available in SQL Server to enforce data integrity. This section
includes a subset - foreign keys, check and default constraints. Primary keys and unique
constraints are covered in the Indexes section, and nullability is covered in the Tables section.
The main issue with constraints is to ensure they are used appropriately to maintain data
integrity and their use is consistent and documented.
Constraints are preferred, whereas rules and defaults are excluded from the standard.

 2002 – TRANSOLUTIONS SYSTEMS 10 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

Types
Default Constraint
Use in preference to Rules or Default objects. Add Default Constraints to a table using
ALTER TABLE.
Check Constraint
Use in preference to Rules. Add Check Constraints to a table using ALTER TABLE.
Foreign Key Constraint
A Foreign Key constraint can reference a unique constraint even when it is not a Primary
key; however, if the unique column contains nulls, verification of the constraint is skipped by
SQL Server. This standard requires Foreign Key constraints to reference primary key
constraints.
A Foreign Key constraint is preferred to using triggers
Add Foreign Key constraints to a table using ALTER TABLE, following to CREATE TABLE.

Cursors
Introduction
The normal caveat in regard to cursors is - do not use them!
Cursors provide row-oriented processing, in contrast to set-oriented processing - generally,
SQL Server performs better if processes are based on sets and relationships. However, there
are processing requirements that can be addressed effectively by use of cursors, so they
should not be totally discarded; cursors are included in the SQL-92 standard, and SQL Server
T-SQL cursors have richer functionality.
Cursors typically cost more to process compared with set-oriented processes.

Structure
Some simple rules that should be applied:
 Make cursors local, the scope by default is global to the connection.
 Monitor @@FETCH_STATUS
 Monitor @@ERROR
 Use FETCH in a loop structure (the first fetch is issued outside the loop)
 Use both CLOSE and DEALLOCATE when finished
 Minimize use of scrollable cursors
 Use lowest cost cursor required to do task
 Use optimistic concurrency if possible
 Avoid to use a cursor over a temporary table.

 2002 – TRANSOLUTIONS SYSTEMS 11 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

Rules and Defaults

Introduction
This refers to SQL Server rule objects and default objects that are created in a database and
then bound to a column or user defined data type. They are not SQL-92 compliant.
These are backward compatible features and should not be used.

Triggers
Introduction
Triggers are a type of or subset of procedures. A trigger is table specific. A trigger fires when
a table-based events (insert, update, delete) are logged; thus triggers can be defined to fire
on insert, update and delete logged events.
Typically triggers are used to:
 Enforce (complex) referential integrity
 Implement cascade processes
 Maintain derived or not normalized data

Most of these functions can be contained adequately in constraints and stored procedures.
Use triggers only when the need is exceptional; an example where triggers may be
appropriate is enforcement of processing for exclusive sub-category tables.
Avoid nested and recursive triggers, unless their need is well established in design
documentation.

Warning: A delete trigger will not fire when a table is truncated.

Header Description
/*******************************************************************/
/* Name: <Name of the trigger> */
/* Copyright: <Company name > */
/* Written by: <Trigger author name> */
/* Creation Date: <Trigger creation date> */
/* */
/* Purpose: <Purpose of the script> */
/* --------------------------------------------------------------- */
/* */
/* Updates: */
/* Author/ Date/ Purpose */
/* */
/*******************************************************************/

Example:

 2002 – TRANSOLUTIONS SYSTEMS 12 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

/*******************************************************************/
/* Name: tri_equipment */
/* Copyright: Trans Solutions Systems S.A */
/* Written by: Orlando Cacha Antigua */
/* Creation Date: July 15, 2002 */
/* */
/* Purpose: Auditing purpose. */
/* --------------------------------------------------------------- */
/* Updates: */
/* 1. */
/* Israel Nizama/ Nov 10, 2002/ Include the client host name. */
/* */
/*******************************************************************/

Indexes
Introduction
Indexes - clustered and non-clustered - have a dramatic impact on performance. Use Profiler
and the Index Query wizard as the basic tool set for determining index needs.

Design
It will be difficult to identify comprehensive or exhaustive index requirements at design time.
Index design - an art - will rely on data navigation, data distribution and user needs. Identify
access patterns from the technical design as input to the initial determination of index
requirements.
Indexes created at initial deployment of a database may need to be drastically varied as data
volumes increase and business patterns evolve, thus continued monitoring is required.
Index design needs to balance the overhead cost of maintaining an index with the
performance benefit gained from the index.

Script
An IF EXISTS and DROP process should precede each relevant CREATE INDEX statement.

Example:

if exists(select 1
from sysindexes sy, sysobjects so
where [Link] = [Link]
and [Link] = 'bill_version_charge_idx1'

 2002 – TRANSOLUTIONS SYSTEMS 13 05/16/26 - 6:09 AM

SQL Server – SQL Coding Conventions

and [Link] = 'bill_version_charge')

drop index bill_version_charge. bill_version_charge_idx1

go
create index bill_version_charge_idx1 on bill_version_charge(bill_id)

 2002 – TRANSOLUTIONS SYSTEMS 14 05/16/26 - 6:09 AM