Everyone has their own way of formatting SQL, this guide tries to bring some simple rules to it.
- Do - Name object with their types at the end, except tables
- Do Not - Mix and match, once you have a style stick to it
- Why - It improves allows you to see what type of object is being used at a glance
Company --< Table
CompanyView --< View
InsertCompanyProcedure --< Procedure
GetCompanyFunction --< Function
- Do - Use singular names for tables
- Do Not - Use plural names
- Why - Singular names tend to be shorter and a bag is called a bag no matter how many items it holds
Select
*
From
dbo.CompanyEmployee
- Do - Use fullnames for objects i.e. CompanyEmployee
- Do Not - Shorten to CompanyEmp
- Why - Whist it may make sense for the original developer, it may not for subsequent developers
Select
*
From
dbo.CompanyEmployee
- Do - Start each statement on a new line and tab the lines underneath
- Do Not - Bunch code together on one line
- Why - It improves readability, and breaks the code into obvious sections
Declare
@CompanyNo Int = 1
, @Name nVarChar(100) = 'Company 1'
, @CountryNo Int = 1
, @EmailAddress nVarChar(100) = 'company@company1.com'
, @DateIncorporated Date = '2016-10-15'
, @IsActive Bit = 1
Insert Into
dbo.Company
(
CompanyNo
, Name
, CountryNo
, EmailAddress
, DateIncorporated
, IsActive
)
Values
(
@CompanyNo
, @Name
, @CountryNo
, @EmailAddress
, @DateIncorporated
, @IsActive
)
Select
c.CompanyNo
, c.Name
, c.CountryNo
, c.EmailAddress
, c.DateIncorporated
, c.IsActive
From
dbo.Company c
- Do - Indent SELECT, FROM, WHERE clauses
- Do Not - Place the where clause inline
- Why - It improves readability, and make commenting out easier
Select
c.CompanyNo
, c.Name
, c.CountryNo
, c.EmailAddress
, c.DateIncorporated
, c.IsActive
From
dbo.Company c
Where
c.IsActive = 1
- Do - Indent OR or AND statments
- Do Not - Place the where clause inline
- Why - It makes it easier to see each part of the clause and comment them out as needed
From
dbo.Company c
Where
c.IsActive = 1
And (
@Country Is Null
Or c.CountryNo = @Country
)
- Do - Specify each column name you want to select
- Do Not - Use * (unless selecting from a temp table you create in the same SP)
- Why - Changes to the underling objects can alter the results of your statement
Select
c.CompanyNo
, c.Name
, c.CountryNo
, c.EmailAddress
, c.DateIncorporated
, c.IsActive
From
dbo.Company c
- Do - Use Alias's for objects (they should normally be the first letter of the object it Company becomes c (lowercase))
- Do Not - Use the full object name, unless very short
- Why - If everything has an alias the engine does not have to look for the objects the columns are part of
Select
c.CompanyNo
From
dbo.Company c
- Do - Specify the schema the object is part of
- Do Not - Leave the schema blank
- Why - If everything has a schema the engine does not have to work out what the current one is
Select
c.CompanyNo
From
dbo.Company c
- Do - Place column alisas next to the column definition
- Do Not - Tab them inline further across the screen
- Why - It can be hard to tell which column aliases line up with which columns definitions, and as most don't need an alias it leaves gaps
Select
c.CompanyNo As Id
, c.Name
, c.CountryNo
, c.EmailAddress As Email
, c.DateIncorporated
, c.IsActive
From
dbo.Company c
- Do - Use AS for alising columns
- Do Not - Use definition space alias
- Why - Using AS makes it clear that what comes next is the alias
Select
c.EmailAddress As Email
From
dbo.Company c
- Do - Split CASE statements onto new lines
- Do Not - Set the case statement inline
- Why - It improves readability, and make commenting out easier
Select
c.CompanyNo
, Case
When c.IsActive = 1 Then 'Active'
Else 'InActive'
End As Active
From
dbo.Company c
- Do - Inline JOIN conditions unless over 3 conditions
- Do Not - Split onto new lines ever ytime
- Why - Join conditions rarely change
Select
c.CompanyNo
From
dbo.Company c
Join dbo.Country cty On c.CountryNo = cty.CountryNo And cty.IsActive = 1
- Do - Use JOIN and LEFT JOIN
- Do Not - Use INNER JOIN or LEFT OUTER JOIN
- Why - Shorter form of the same statement
Select
c.CompanyNo
From
dbo.Company c
Join dbo.Country cty1 On c.CountryNo = cty1.CountryNo
Left Join dbo.Country cty2 On c.CountryNo = cty2.CountryNo
Inner Join dbo.Country cty3 On c.CountryNo = cty3.CountryNo --< Do NOT
Left Outer Join dbo.Country cty4 On c.CountryNo = cty4.CountryNo --< Do NOT
- Do - Inline very simple 1 select/one where
- Do Not - Inline anything more complicated
- Why - Keep the line between tidy and readable
Select
c.CompanyNo
, (Select Count(*) From dbo.Company Where CountryNo = c.CountryNo) As CompanyCountryCount
From
dbo.Company c
Select
c.CompanyNo
, (
Select
Count(*)
From
dbo.Company
Where
CountryNo = c.CountryNo
And c.IsActive = 1
) As CompanyCountryCount
From
dbo.Company c
- Do - Use SELECT to set property values
- Do Not - Use SET
- Why - Select can be used both for getting values from a table and setting values, keep just one
Select
@CompanyNo = 1
, @EmailAddress = c.EmailAddress
, @DateIncorporated = '2015-06-01'
From
dbo.Company c
Where
c.IsActive = 1
- Do - Use @RowCount to combine and UPDATE / INSERT
- Why - Simplify update / insert procedures
Begin Tran
Update
c
Set
c.EmailAddress = @EmailAddress
From
dbo.Company c
Where
c.CompanyNo = @CompanyNo
If @@RowCount = 0
Begin
Insert Into
dbo.Company
(
EmailAddress
)
Values
(
@EmailAddress
)
End
Commit Tran
- Do - Leave a comment at the bottom of the Stored Procedure with example execution values
- Why - When testing or updating an sp having example values can make it alot easier
Create Proc [dbo].[GetValueProcedure]
@Value1 nVarChar(100)
As
Begin
Select
@Value1
End
/*
GetValueProcedure 'Hello World'
*/
-
Do - Use the following pattern to keep a consistent naming convention for foreign keys
-
Why - This will allow developers to see at a glance how foreign keys related to the current table
-
FK
- Denotes the use of a foreign key
-
UserNo
- The key column you are linking to
-
ApprovedBy
- The function the column is storing (this can be ignored if the table will only ever hold one foreign key to the target table and its function is impied by the name)
The column should look like the following
- FK_UserNo_ApprovedBy
It should not be
- FK_ApprovedBy
- FK_ApprovedBy_UserNo
- ApprovedByNo