Standards were meant to be broken. This is not a standard. This is how I like to write SQL code. Feel free to deviate when needed.
Source Code File Organization
Source code files should contain only one SQL object definition with the file name being the same name as the object name. For example, if the object name is spShackListingByID the file name would be spShackListingByID.sql.
Spaces, not tabs. Tabs are measured differently by different editors, but spaces are uniform.
Commas go at the end of a line except if used in a method.
Method commas do not need to be on their own line.
Break up lines as needed to avoid horizontal scrolling. The section on Wrapping Lines will help.
New line general principles:
- Break after a comma
- Break at the end of SELECT statement
- Break at the end of CASE
- Break at the end of WHEN THEN statement
- Break at the end of ELSE statement
- Break at the end of FROM statement
- Break at the end of JOIN statement
- Break at the end of WHERE statement
- Break at the end of AND/OR statement
- Break at the end of ORDER BY statement
- Break at the end of GROUP BY statement
- Break at the end of HAVING statement
Wrapping Lines Example
The comments I usually see involve explaining variables and why they have the value they do.
SQL queries can have two kinds of comments: block comments and line comments. Block comments are delimited by /*...*/, and line comments are --.
Avoid adding comments at the end of a line of code. Here be dragons...
Because the comment was added on the same line as the code, the WHERE clause was commented out. Now all of the shacks have water instead of just the one with ID = 5. I've seen this happen. It's not fun.
Avoid surrounding a block comment with a frame. It may look nice, but it is hard to maintain.
Comments can create zombie code. This is dead code, but if you check it in, it will live an undead existence. Remove zombie code before you check in.
One declaration per line is recommended.
Put variables only at the beginning of the query.
Blank lines go:
- Before the SELECT in a stored procedure
- Before and after a UNION
- Before and after the GO
- Before and after a block comment
- Before and after declaring local variables
- Before and after setting all of the local variables
Blank spaces go:
- After a comma
- Before and after an operator (= + - * /)
There are only two hard problems in computer science.
- Naming things
- Cache invalidation
- Off-by-one errors
Don't get stuck in analysis paralysis with names. Name the object the first thing that comes to mind and change it later.
- Objects such as stored procedures start with "sp".
- Contain no special characters such as an underscore.
- Are camel cased such as spShackListings.
- Do not use Hungarian notation for variable names.
- Start with the function name if needed. For example: AvgShackRating, SumRenterShacks, MinMoveInDate, or MaxMoveOutDate.
- Use opposites in variable names, such as Start/End.
- Collections should be named as the plural form of the singular objects that the collection contains. A collection of Shack objects is named Shacks.
- Boolean variable should imply Yes/No; True/False values, such as @HasWater, @IsFallingApart, or @ContainsAsbestos.
- When naming tables, express the name in the singular form. For example, use Shack instead of Shacks.
- When naming columns of tables, do not repeat the table name except for the primary key, for example, if the table is Shack, the primary key is ShackID. The other columns should not be named ShackName or ShackHasWater.
Microsoft SQL Server
- Do not prefix stored procedures with sp_, because this prefix is reserved for identifying system-stored procedures.
- In Transact-SQL, do not prefix variables with @@, because they should be used for variables such as @@IDENTITY.
- Pascal case
- The first letter and the first letter of each word are capitalized. For example: HasWater.
- Camel case
- The first letter of an identifier is lowercase and the first letter of each subsequent concatenated word is capitalized. For example: spShackListings.
- All Caps
- All letters in the identifier are capitalized. For example: SELECT
The following table provides rules and example for common identifiers:
|Identifier Type||Rules for Naming||Examples|
|SQL||All Caps||SELECT FROM WHERE|
|TSQL Functions||All Caps||GETDATE() MIN() MAX()|
|Stored Procedures||Camel case||spShackListings|
Find the latest shack rented for each renter.