A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Monday, July 27, 2009

SQL coding standards

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

 

No comments: