I wrote this 2 years ago and have only now gotten back to a place where we are starting to talk about coding standards. The google doc version looks much better. Although I realistically should have written it in LaTeX to keep my nerd chops sharp.
Preface
The following is intended to provide a guideline to developers so that their code can be more consistent which will lead to lower maintenance costs and less chances for errors. The general rule of thumb is to remove the opportunity for ambiguity to exist.
Some rules may appear to be violated if this document is read from start to finish. This is intentional and is done to reduce document length.
Definitions
Camel Case, camelCase, is the practice of writing the first letter of the first word lowercased and all subsequent words with their first letter uppercased.
Pascal Case, PascalCase, is the practice of writing the first letter in the identifier and the first letter of each subsequent concatenated word capitalized.
Hard tab, ASCII character 9, \t, [Tab key]
Soft tab, ASCII character 32, [Spacebar], repeated n times. For our development purposes, n = 4
General rules
Be explicit in your code, avoid implicit behavior
Verbosity is preferred to terseness
Be consistent
Nested views are a maintenance nightmare and to be avoided
Stored procedures should have SET NOCOUNT ON as the first statement within them
Explicitly drop and recreate objects instead of using ALTER statements
Capture the value of @@ERROR and @@ROWCOUNT before inspecting them
Dynamic SQL statements will not be built nor executed programmatically
Do not use sentinel values or empty strings to get around NOT NULL column constraints
Avoid using magic numbers in code. Define constants and reference the identifier
Lookup identity values programmatically via the human readable key
Avoid using print statements within your stored procedures
Store data independent of formats (e.g. phone or social security numbers)
Layout
Hard tabs are not to be used. Configure your text editor to use soft tabs instead
Code will generally be longer than it is wide
Declare variables as early as possible
An empty line is to be used to separate statements
-- Correct
DECLARE @currentDate datetime
SELECT @currentDate = GetDate()
SELECT TOP 10 ST.sales_people INTO #EXAMPLE FROM dbo.SALES_TABLES ST
UPDATE EX SET sales_people = IsNull(sales_people, 'uknown') FROM #EXAMPLE EX
SELECT EX.sales_people FROM #EXAMPLE EX
RETURN @currentDate
-- Avoid
DECLARE @currentDate datetime
SELECT @currentDate = GetDate()
SELECT TOP 10 ST.sales_people INTO #EXAMPLE FROM dbo.SALES_TABLES ST
UPDATE EX SET sales_people = IsNull(sales_people, 'uknown') FROM #EXAMPLE EX
SELECT EX.sales_people FROM #EXAMPLE EX
RETURN @currentDate
Each element of a statement should exist on its own line, indented appropriately (awkward)
-- Correct
SELECT DISTINCT
A.column1
, B.column2
FROM
TABLEA A
INNER JOIN
TABLEB B
ON B.foreign_key = A.primary_key
AND B.foreign_key2 = A.primary_key2
WHERE
A.primary_key IN
(
'A'
, 'B'
)
OR B.column2 = 'Test'
-- Avoid
SELECT DISTINCT A.column1, B.column2 FROM TABLEA A INNER JOIN TABLEB B ON B.foreign_key = A.primary_key AND B.foreign_key2 = A.primary_key2 WHERE A.primary_key IN ('A', 'B') OR B.column2 = 'Test'
One declaration or assignment is to be used per line. However, do make declarations/assignments with as few statements as possible
-- Correct
DECLARE
@salesPreviousMonthToDate decimal(18, 2)
, @salesCurrentMonthToDate decimal(18, 2)
, @salesPreviousYearToDate decimal(18, 2)
, @salesCurrentYearToDate decimal(18, 2)
-- Avoid
DECLARE
@salesPreviousMonthToDate decimal(18, 2), @salesCurrentMonthToDate decimal(18, 2), @salesPreviousYearToDate decimal(18, 2), @salesCurrentYearToDate decimal(18, 2)
For single line statements, commas should succeed an argument with a single space after it
-- Correct
SELECT IsNull(NULL, 'Hello world')
-- Avoid
SELECT IsNull(NULL ,'Hello world')
For multi-line statements, commas should precede arguments by a soft tab at the appropriate block level
-- Correct
SELECT
column1
, column2
, column3
FROM
dbo.TEST
-- Avoid
SELECT
column1,
column2
, column3
FROM
dbo.TEST
BEGIN and END should be used to indicate block levels, even for optional locations
-- Correct
IF (@@ROWCOUNT > 0)
BEGIN
PRINT 'Blocking level'
END
-- Avoid
IF (@@ROWCOUNT > 0)
PRINT 'Blocking level'
Casing
Table names should be upper cased
-- Correct
CREATE TABLE dbo.SALES_FIGURES(sales_figures_identifier int PRIMARY KEY)
-- Avoid
CREATE TABLE dbo.sales_figures(sales_figures_identifier int PRIMARY KEY)
Column names should be lower cased
-- Correct
CREATE TABLE dbo.SALES_FIGURES(sales_figures_identifier int PRIMARY KEY)
-- Avoid
CREATE TABLE dbo.SALES_FIGURES(SALES_FIGURES_IDENTIFIER int PRIMARY KEY)
Functions and procedures should be Pascal cased
-- Correct
CREATE FUNCTION dbo.SalesGetByYear()
CREATE PROCEDURE dbo.SalesInsert
-- Avoid
CREATE FUNCTION dbo.salesGetByYear()
CREATE PROCEDURE dbo.SALES_INSERT
Parameters should be Camel cased
-- Correct
CREATE FUNCTION dbo.SalesGetByYear(@currentYear int, @salesPersonId int)
CREATE PROCEDURE dbo.SalesInsert(@currentDate, @salesPersonId int, @salesAmount decimal(18, 2))
-- Avoid
CREATE FUNCTION dbo.SalesGetByYear(@CURR_YEAR int, @ID int)
CREATE PROCEDURE dbo.SalesInsert(@CurrentDate, @SalesPersonId int, @salesamount decimal(18, 2))
Local variables should be prefaced with an underscore and Camel cased
-- Correct
DECLARE @_insuranceSalesPreviousMonthToDate decimal(18, 2
-- Avoid
DECLARE @insuranceSalesPreviousMonthToDate decimal(18, 2)
Native SQL types are to be lower cased.
-- Correct
DECLARE @_insuranceSalesPreviousMonthToDate decimal(18, 2)
-- Avoid
DECLARE @_insuranceSalesPreviousMonthToDate DECIMAL(18, 2)
User Defined Types, a.k.a. aliases, should be Pascal cased
-- Correct
CREATE TYPE dbo.SocialSecurityNumber FROM varchar(9)
-- Avoid
CREATE TYPE dbo.socialSecurityNumber FROM varchar(9)
CREATE TYPE dbo.Social_Security_Number FROM varchar(9)
CLR User Defined Types would follow the C# guidelines for casing and thus are Pascal cased
-- Correct
CREATE TYPE dbo.ClrUserDefinedType EXTERNAL NAME Assembly.ClassName
-- Avoid
CREATE TYPE dbo.clrUserDefinedType EXTERNAL NAME Assembly.ClassName
CREATE TYPE dbo.CLR_User_Defined_Type EXTERNAL NAME Assembly.ClassName
CREATE TYPE dbo.CLR_USER_DEFINED_TYPE EXTERNAL NAME Assembly.ClassName
Naming conventions
Do not abbreviate
-- Correct
DECLARE @insuranceSalesPreviousMonthToDate decimal(18,2)
-- Avoid
DECLARE @insSlsPrevMTD decimal(18,2)
Employ a consistent naming pattern within and across methods
-- Correct
DECLARE @firstName char(10), @lastName char(10), @middleName char(10)
-- Avoid
DECLARE @firstName char(10), @nameLast char(10), @mid_nm char(10)
Items should be named in a NounVerb pattern. This will ensure that like items visually appear grouped together. The four standard verbs are Insert, Update, Delete and Get. If a combined Insert/Update statement is needed, its verb should be Add.
-- Correct
CREATE PROCEDURE dbo.AdvisorInsert
CREATE PROCEDURE dbo.AdvisorUpdate
CREATE PROCEDURE dbo.AdvisorDelete
CREATE PROCEDURE dbo.AdvisorGet
-- Avoid
CREATE PROCEDURE dbo.SetAdvisor
CREATE PROCEDURE dbo.UpdateAdvisor
CREATE PROCEDURE dbo.AdvisorPurge
CREATE PROCEDURE dbo.GetAdvisorAll
Hungarian notation is not to be used. To this end, do not preface procedures and functions with usp and udf, even worse is the use of sp_. What an object is will be quite apparent when you expand the Programmability tree, when you read the code or by querying the sys.objects table
-- Correct
CREATE FUNCTION dbo.SalesGetByYear()
CREATE PROCEDURE dbo.SalesInsert
-- Avoid
CREATE FUNCTION dbo.udfSalesGetByYear()
CREATE PROCEDURE dbo.uspSalesInsert
All tables should be aliased in queries and all columns should be fully qualified. Abbreviations are preferred in aliases
-- Correct
SELECT SLS.year, SLS.month FROM dbo.SALES_INVOICES_YEAR_TO_DATE SLS
-- Avoid
SELECT SALES_INVOICES_YEAR_TO_DATE.year, SALES_INVOICES_YEAR_TO_DATE.month FROM dbo.SALES_INVOICES_YEAR_TO_DATE
Join type should be fully qualified
-- Correct
SELECT A.column1 FROM dbo.TABLEA A LEFT OUTER JOIN dbo.TABLEB B ON A.key = B.key
-- Avoid
SELECT A.column1 FROM dbo.TABLEA A JOIN dbo.TABLEB B ON A.key = B.key
ANSI joins are preferred to Transact SQL style joins
-- Correct
SELECT A.column1 FROM dbo.TABLEA A LEFT OUTER JOIN dbo.TABLEB B ON A.key = B.key
-- Avoid
SELECT A.column1 FROM dbo.TABLEA A, dbo.TABLEB B WHERE A.key *= B.key
Transact-SQL reserved words should be capitalized ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ed8b3e27-6796-40f0-aef3-0cac5e0e2418.htm
Power items
If you know about stored procedures named sp_OA*, let that knowledge remain in the recesses of your brain and do not use them here
Extended stored procedures are not to be written
Comments and versioning
Employ a consistent style of commenting
Single line comments with a trailing space are preferred
-- Correct
-- Insightful comment here
-- Avoid
--Insightful comment here
Remove old blocks of code---do not hide it via comments. If you think you might need it later, place a comment in the code referencing the last tag in version control
Code should not be checked into the repository without a useful comment. Describe why the item was changed and what the scope of the changes are. Project references are great for further reading but they should not be the sole comment.
To paraphrase Socrates, the uncommented code is not worth maintaining. At the beginning of all of your procedures and functions, you should have a statement of purpose. The header should indicate what the method does and what problem it solves. It should clearly indicate: any side-effects it will induce, what data it returns, assumptions about the values of its arguements, custom procedures and functions it invokes and the orientation towards transactions and atomicity
