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]