SQL 2005 - Coding Standards - Formatting Specfics

  • Article

Formatting Specfics

Database Objects

Quote all database objects/fields using square brackets

Reference all objects by owner/schema

Eg:

[dbo].[authors]

Keywords

Use UPPERCASE for all keywords

Eg.

SELECT

    *

FROM

    [dbo].[authors]

Datatypes

Use lowercase for all datatypes

Eg.

DECLARE

    @authorID int

   ,@authorName nvarchar(max)

Table Alias

  • All tables should be aliased
  • Aliases should be lowercase
  • Ideally use the first letter of each word.
    • However choose a base alias for each table within the database.

This should therefore be consistent across all queries within the db.

    • Where this causes a clash, suffix with a term to distinguish the alias within the query.

Eg.

FROM

    [dbo].[Customer] c

FROM

    [dbo].[CustomerAddress] ca

FROM

    [dbo].[Tree] t_parent

    INNER JOIN [dbo].[Tree] t_child ON ...

Column lists

  • Each column must start on a new line
  • Indent column list from preceding keyword
  • This is required if the table contains an IDENTITY column

SELECT, UPDATE, DELETE, INSERT general rules

Within SELECT, UPDATE, DELETE and INSERT statements

  • Align FROM, WHERE, GROUP BY, HAVING and SET clauses. These should begin on a new line, with the same indent as the original select statement.

UPDATE

Always use an table alias in the UPDATE statement and reference the table in a FROM clause

Eg.

UPDATE a

SET

    [AuthorName] = @authorName

   ,[AuthorAge] = @authorAge

FROM

    [dbo].[Author] a

INSERT

  • Always include a column list
  • Obey rules regarding brackets and column lists

Eg.

INSERT INTO [dbo].[Author]

(

    [AuthorName]

   ,[AuthorAge]

)

FROM

  • Indent table list in FROM statement
  • All JOINS should begin on a new line
  • If the line is too long, ON should begin on a new line, indented from the original table name

Eg.

FROM

    [dbo].[Customer] c

WHERE, HAVING

  • Indent all clauses within a WHERE clause
  • Each clause should be on a new line
  • If there are multiple clauses, pad with an additional indent so that the first character of each statement lines up.

Eg.

 

WHERE

        table.[AuthorName] = 'Steve'

    AND table.[AuthorAge] > 30

    OR table.[AuthorName] = 'Dave'

ORDER BY, GROUP BY

  • Indent column list
  • Each column must start on a new line

Eg.

ORDER BY

    table.[AuthorLastName]

   ,table.[AuthorFirstName]

CASE

  • Indent all WHEN and ELSE keywords within a CASE statement
  • END keyword should have the same indentation as the original CASE keyword

Eg.

CASE

    WHEN t1.[Col1] = 1 THEN 'First'

    WHEN t1.[Col1] = 2 THEN 'Second'

    ELSE 'Last'

END AS [ColumnPosition]

IF, WHILE

  • Always use BEGIN and END statements with the IF or WHILE keywords – see rules for BEGIN and END
  • WHILE, IF and ELSE should always be at the start of a new line
  • Each clause should be on a new line, with the exception of the first
  • If there are multiple clauses, pad with an additional indent so that the first character of each statement lines up.

Eg.

IF EXISTS(SELECT * FROM #table)

AND @debug = true

BEGIN

   --do something

END

ELSE

BEGIN

   --do something else

END

WHILE (@rowcount > 0)

BEGIN

   --do something

END --end of @rowcount > 0

Examples

This example shows some more complex WHERE clause statements.

--Select tree structure

SELECT

    [Col1]

   ,[Col2]

   ,[Col3]

   ,CASE

        WHEN t1.[Col1] = 1 THEN 'First'

        WHEN t1.[Col1] = 2 THEN 'Second'

        ELSE 'Last'

    END AS [ColumnPosition]

FROM

    [dbo].[Tree] t_parent

    INNER JOIN [dbo].[Tree] t_child

        ON t_parent.[Row1] = t_child.[Row1]

        AND t_parent.[TreeID] = t_child.[TreeID]

WHERE

    (

        t2.[Col4] = 3

        OR

        (

                t2.[Col3] < 6

            AND t2.[Col4] > 5

        )

    )

    AND NOT EXISTS

    (

        SELECT

            *

        FROM

            [dbo].[Table5] t5

        WHERE

            t5.[Col2] = t1.[Col2]

  )

GROUP BY...

HAVING ...

--Insert into table1

INSERT [dbo].[Table1]

(

    [Column1]

   ,[Column2]

   ,[Column3]

)

SELECT

    t2.[Column1]

   ,t2.[Column2]

   ,t2.[Column3]

FROM

   [dbo].[Table] t

WHERE ....

--Insert values into table1

INSERT [dbo].[Table1]

(

    [Column1]

   ,[Column2]

   ,[Column3]

)

VALUES

(

    value1

   ,value2

   ,value3

)

--Update Author Name and Age

UPDATE a

SET

    [AuthorName] = @authorName

   ,[AuthorAge] = @authorAge

FROM

    [dbo].[Author] a

    INNER JOIN @AuthorTable at ON a.[AuthorID] = at.[AuthorID]