SQL 2005 – Coding Standards – Formatting Specfics


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]

Comments (0)

Skip to main content