Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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]