SQL Server Database Coding Standards and Guidelines – Part 1
Coding Standards
Naming
Tables: Rules: Pascal notation; end with an ‘s’
- Examples: Products, Customers
- Group related table names1
Stored Procs: Rules: sp<App Name>_[<Group Name >_]<Action><table/logical instance>
- Examples: spOrders_GetNewOrders, spProducts_UpdateProduct
Triggers: Rules: TR_<TableName>_<action>
- Examples: TR_Orders_UpdateProducts
- Notes: The use of triggers is discouraged
Indexes: Rules: IX_<TableName>_<columns separated by _>
- Examples: IX_Products_ProductID
Primary Keys: Rules: PK_<TableName>
- Examples: PK_Products
Foreign Keys: Rules: FK_<TableName1>_<TableName2>
- Example: FK_Products_Orderss
Defaults: Rules: DF_<TableName>_<ColumnName>
- Example: DF_Products_Quantity
Columns: If a column references another table’s column, name it <table name>ID
- Example: The Customers table has an ID column
- The Orders table should have a CustomerID column
General Rules:
- Do not use spaces in the name of database objects
- Do not use SQL keywords as the name of database objects
- In cases where this is necessary, surround the
- object name with brackets, such as [Year]
- Do not prefix stored procedures with ‘sp_’2
- Prefix table names with the owner name3
Structure
- Each table must have a primary key
- In most cases it should be an IDENTITY column named ID
- Normalize data to third normal form
- Do not compromise on performance to reach third normal form. Sometimes, a little de-normalization results in better performance.
- Do not use TEXT as a data type; use the maximum allowed characters of VARCHAR instead
- In VARCHAR data columns, do not default to NULL; use an empty string instead
- Columns with default values should not allow NULLs
- As much as possible, create stored procedures on the same database as the main tables they will be accessing
Formatting
- Use upper case for all SQL keywords
- SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.
- Indent code to improve readability
- Comment code blocks that are not easily understandable
- Use single-line comment markers(–)
- Reserve multi-line comments (/*.. ..*/) for blocking out sections of code
- Use single quote characters to delimit strings.
- Nest single quotes to express a single quote or apostrophe within a string
- For example, SET @sExample = ‘SQL”s Authority’
- Nest single quotes to express a single quote or apostrophe within a string
- Use parentheses to increase readability
- WHERE (color=’red’ AND (size = 1 OR size = 2))
- Use BEGIN..END blocks only when multiple statements are present within a conditional code segment.
- Use one blank line to separate code sections.
- Use spaces so that expressions read like sentences.
- fillfactor = 25, not fillfactor=25
- Format JOIN operations using indents
- Also, use ANSI Joins instead of old style joins4
- Place SET statements before any executing code in the procedure.
Reference:
1) Group related table names:
Products_USA
Products_India
Products_Mexico
2) The prefix sp_ is reserved for system stored procedures that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. Time spent locating the stored procedure can be saved by avoiding the “sp_” prefix.
3) This improves readability and avoids unnecessary confusion. Microsoft SQL Server Books Online states that qualifying table names with owner names helps in execution plan reuse, further boosting performance.
4)
False code:
SELECT *
FROM Table1, Table2
WHERE Table1.d = Table2.c
True code:
SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.d = Table2.c
Complete Series of Database Coding Standards and Guidelines
SQL SERVER Database Coding Standards and Guidelines – Introduction
SQL SERVER – Database Coding Standards and Guidelines – Part 1
SQL SERVER – Database Coding Standards and Guidelines – Part 2
SQL SERVER Database Coding Standards and Guidelines Complete List Download
Click here to get free chapters (PDF) in the mailbox
Reference : Pinal Dave (https://blog.sqlauthority.com)
19 Comments. Leave new
Of course, this mainly just personal taste. I agree with most of your conventions, with the exception of the following:
1) Plural table names – Should be Singular
2) Pks, which I agree are typically an identity, should be “Table Name” + ID.
My reasoning for this comes from a data modeling perspective. In my view, a key should change names only under rare circumstances. In your scenario, keys change names quite frequently as they become FKs. In your CustomerId example above, the key changes names from ID to CustomerId. In this case, the PK of the Customer table should be CustomerId as well.
Most Data Modeling tools will default to this behavior as well. When creating an FK, the default name of the FK is the same name as the source PK.
Under your naming convention, if you believe table names should be plural, then the name of your FK should be really be CustomersId (note the xtra ‘S’).
This is just personal opinion, but I think naming ANY column ‘ID’ is a bad idea. A “CustomerId” should be CustomerId is all tables if it contains the same thing.
I agree with Rich about the ID, but not the table name. The table is Customers and the ID should be CustomerID. The ID refers to one Customer where as the table is a collection of Customers.
I do, however, add a prefix to tables. I use tbl, tbk and tmp to separate types of tables. tbl’s are the standard data tables.
tbk’s are lookup tables or contain data which doesn’t change often, such as tbkStates or tbkAddressTypes. tbkAddressTypes might contain AddressTypeID and AddressType which would include Mailing, Billing, Warehouse, etc.
I use tmp for temporary storage tables such as importing customer information from a flat file and messaging it before entry into the tblCustomers table. By having the tmp in front of it, other developers know that this does not contain data that should be counted on for any validity.
respected sir
i am saravanan, and i have finished B.Tech(IT), i want to learn the sql server 2005 with excellent knowledge. can you help me to learn well….please send all the tips and tricks in sqlserver
Nice discussion and the feedback has been a good exchange of personal views which is always nice to see.
My two cents worth:
1. I use the Singular form for table names. Customer, Account, etc.
2. I do use numID to represent the PK of all my tables. Since the ID belongs to the table Customer, I don’t feel the need to qualify it with CustomerID. It’s given.
3. In the case of an Employee who has a supervisor reference, you still end up with:
Table: Employee
PK: numID
Col: numSupervisorID
Col: txtName
Col: txtAddress1
Col: numStateID
Col: dteCreated
Col: dteModified
etc..
In this case, numSupervisorID also references a row in the Employee table, so even with data modelling packages, the column must be renamed because it can’t pull in numID again.
One could argue that you could have an intersection table back to itself for Supervisors, but that seems like overkill in this case.
4. I like the use of table prefixes. We used them heavily at one of my other companies and it help identify a table’s intended use right away. tbli (intersection table), tbl (standard data table), tblw (working or temp table), tlbv (used for a view), etc…
It’s always good to see how others are implementing their solutions.
I never really got past the “Notes: The use of triggers is discouraged”. The reason is that I dislike and distrust statements without a reason why they are made. Triggers are *very* useful for securing data integrity, i.e. protecting the data, even though they slow down data updates.
I’m not accepting all you naming standards either, but then I feel that as long as you keep to a standard you may name things (almost) any way you like. :-) … and sometimes one needs ideas/suggestions.
And George, sometimes you have to look at the data from another viewpoint than the table. In a table the name numId makes sense, but in a query (or elsewhere) it might tell you nothing (i.e. makes it harder to make sense of it).
The Guidelines will be really helpful for a rookie programmer.
As per my personal opinion,I don’t think on any given day any DB programmer will assent the use of TRIGGERS in any perspective unless we maintain a meagre amount of records. Using tirgger is always like jogging on a sludge. The role of triggers are efficiently played by the CONSTRAINTS. A TABLE is well manipulated by using constraints and triggers should be used only as a last resort.
“In VARCHAR data columns, do not default to NULL; use an empty string instead”
Why?
Hi Dave
This is very useful one to learn more abt stopred proc.
All the best for ur future plans in SQL Server……
Regards
G Arun
We have a database where we would like to enforce naming conventions.
We have rules for tables, views, procedures …. but we just look at enforcing the naming convention on tables.
The rule is :
All table names should start with “companyName_”.
Which new SQL 2005 feature will let us do this ?
how we can write a T-SQL script that implement this rule
Is there a good reason to use uppercase names for keywords, or is it a “tradition”? Do certain SQL parsers choke on lower case? My experience is with MS SQL only, which works with any case I use.
Every other language I use, C#, VB, JS, English, uses lower case or possibly mixed case.
I’ve been trained to look at words with all uppercase as YELLING or acronyms. When I see SQL statments with all caps, it reminds me of times when character sets, keyboards and parsers couldn’t handle both upper and lower case characters. Can we move past that those traditions?
If the only reason to capitalize keywords, is because everyone is doing it, that’s not good enough. It makes yound developers think there is some mysterious magic involved in writing stored procedures.
Personally I like Sentence Case or lower case. The modern IDE’s will highlight keywords, so that they stand out.
May I replace the OR statements by UNION. I searched it on internet. and it’s useful for me, but when i run it use your script( calculate time ) on this site. it’s wrong. Could you explain to me?
Hi Dave
I am in the process of creating mailing lables list from my Customer Table..
FirstName, Last Name, Address1, Address2, City, State, Zip.. are the fields.
I would like to print the labels for each customer record like
First Name + LastName
Address1+Address2+City+State+Zip
and want them horizantally.
Can you help me?
I tried, with three list boxes, and 2nd and 3rd list box record values comes up with line feed while deploy on server..
Pls help me how to create mailing labels report using SSRS 08.
Hi, Dave and all the members.
I really like all the above benchmarks that you described above, except the note regarding ‘Triggers’.
I personally feel, triggers can really simplify many things, and can automate a lot of work, which you would otherwise would have to do through programming. In turn the load on the server will decrease, as no external connections for the database will be required and one would be relieved of the task of programming every program where database automation is required.
So, i personally would promote the use of triggers, especially for large databases, with lot of rows, to reduce the overhead , for implicit automation from the database, etc.
Do not pluralize table names.
Table names should not be pluralized, as tables are idended to hold multiple records. Pluralizing only attempts to pointlessly enforces what is already known.
Hello sir,
Could you explain this thing?
Use upper case for all SQL keywords
SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.
Hi Dave,
“Stored Procs: Rules: sp_[_]”..
I am little confuse with this statement, Specially for “sp_”. Because of my scenario, we have one application called “Business Enterprise”. But under this we have/had Sales, Purchase, Inventory and MIS etc sub modules/sub applications. while Store Procedure name why i put application name? If i put respective module name, it might be more accurate to identify the same instead of application name.