- Use “Pascal” notation for SQL server Objects Like Tables, Views, Stored Procedures. Also tables and views should have ending “s”.
Example:
UserDetails
Emails
- If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.
Example:
Page_ UserDetails
Page_ Emails
- Use following naming convention for Stored Procedure. sp<Application Name>_[<group name >_]<action type><table name or logical instance> Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb
Example:
spApplicationName_GetUserDetails
spApplicationName_UpdateEmails
- Use following Naming pattern for triggers: TR_<TableName>_<action><description>
Example:
TR_Emails_LogEmailChanges
TR_UserDetails_UpdateUserName
- Indexes : IX_<tablename>_<columns separated by_>
Example:
IX_UserDetails_UserID
- Primary Key : PK_<tablename>
Example:
PK_UserDetails
PK_ Emails
- Foreign Key : FK_<tablename_1>_<tablename_2>
Example:
FK_UserDetails_Emails
- Default: DF_<table name>_<column name>
Example:
DF_ UserDetails _UserName
- Normalize Database structure based on 3rd Normalization Form. Normalization is the process of designing a data model to efficiently store data in a database. (Read More Here)
- Avoid use of SELECT * in SQL queries. Instead practice writing required column names after SELECT statement.
Example:
SELECT Username, Password
FROM UserDetails
- Avoid using temporary tables and derived tables as it uses more disks I/O. Instead use CTE (Common Table Expression); its scope is limited to the next statement in SQL query. (Read More Here)
- Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure. (Read More Here)
- Properly format SQL queries using indents.
Example: Wrong Format
SELECT Username, Password FROM UserDetails ud INNER JOIN Employee e ON e.EmpID = ud.UserID
Example: Correct Format
SELECT Username, Password
FROM UserDetails ud
INNER JOIN Employee e ON e.EmpID = ud.UserID
- Practice writing Upper Case for all SQL keywords.
Example:
SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE.
- There must be PRIMARY KEY in all the tables of database with column name ID. It is common practice to use Primary Key as IDENTITY column.
- If “One Table” references “Another Table” than the column name used in reference should use the following rule :
Column of Another Table : <OneTableName> ID
Example:
If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table.
- Columns with Default value constraint should not allow NULLs.
- Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.
- Always create stored procedure in same database where its relevant table exists otherwise it will reduce network performance.
- Avoid server-side Cursors as much as possible, instead use SELECT statement. If you need to use cursor then replace it with WHILE loop (or read next suggestion).
- Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement. (Read More Here)
INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE ….
- Avoid using spaces within the name of database objects; this may create issues with front-end data access tools and applications. If you need spaces in your database object name then will accessing it surround the database object name with square brackets.
Example:
[Order Details]
- Do not use reserved words for naming database objects, as that can lead to some unpredictable situations. (Read More Here)
- Practice writing comments in stored procedures, triggers and SQL batches, whenever something is not very obvious, as it won’t impact the performance.
- Do not use wild card characters at the beginning of word while search using LIKE keyword as it results in Index scan.
- Indent code for better readability. (Example)
- While using JOINs in your SQL query always prefix column name with the table name. (Example). If additionally require then prefix Table name with ServerName, DatabaseName, DatabaseOwner. (Example)
- Default constraint must be defined at the column level. All other constraints must be defined at the table level. (Read More Here)
- Avoid using rules of database objects instead use constraints.
- Do not use the RECOMPILE option for Stored Procedure as it reduces the performance.
- Always put the DECLARE statements at the starting of the code in the stored procedure. This will make the query optimizer to reuse query plans. (Example)
- Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure. (Example)
- Use BEGIN…END blocks only when multiple statements are present within a conditional code segment. (Read More Here)
© Copyright 2000-2008 Pinal Dave. All Rights Reserved. SQLAuthority.com
Reference: Pinal Dave (www.SQLAuthority.com)
pinaldave
Simply great!!!. I am a regular reader of this blog. Thanks for having excellent compilations like this.
Regards
Chella Ganesh
To differentiate from system stored procedures, should not we use “usp” instead of “sp” prefix.
Regards
Hi Dave,
Very nice blog post. Standards in SQL are on area that people do not think about enough, so I’m very happy to see this. I’m also happy to see that many things I personally have as standards match up well to yours.
A little feedback on a few items you mentioned:
I have always used the table name to describe what is being stored in each individual row, rather than using the name to describe the container itself. That specifically eliminates the addition of an “s” at the end (in most cases). So I refer to the “customer” table, not the “customers” table.
For SP naming conventions, I like making the names a bit shorter and in a different order. The main goal for me is that the SPs are easy to group and find in SQL Server Management Studio. I actually use camelCase:
For example, if I’m writing a SP for the Google Web site, a SP name might be “googleEmailUpdate”.
By placing the Object/Table Name before the Acton, it is much easier to group and find the SPs in Management Studio. It does not flow off the tongue quite as nicely, but finding SPs is a breeze.
I normally use a “db” prefix for system maintenance SPs, and I use a “clr” prefix for (you guessed it) CLR code.
(@Atif: in Management Studio, the system SPs are separated from the user’s SPs — they are in a different folder — and they also use the “[sys].” prefix, so there shouldn’t be any conflict with naming, even if you stick with “sp”.)
Something you didn’t mention as a standard is the use of semi-colons. My personal standard is to use a semi-colon everywhere they are optional. Once you get into the habit, it is second-nature and not a bother at all. On the contrary, it provides more robust code, because if you don’t use semi-colons, you’re relying on the parser to determine the end of your statement.
One other area I would like to comment on is your last point, about using BEGIN … END only in cases where they surround multiple statements. I humbly disagree.
I have struggled with how to write BEGIN … END over the years, because they are visually clumsy. But I finally came up with a method that has proved to be helpful and visually helpful.
I *always* use BEGIN … END now, and I use them exactly like I use “{” and “}” brackets in C++/C# code. (K&R style)
I place the BEGIN on the same line as the conditional/loop/block, I indent all the contained code, and I align the “END;” (don’t forget semi-colons) with the block start.
For example (please ignore the code itself, which would be better served with a CTE):
WHILE (@@FETCH_STATUS = 0) BEGIN
IF (NOT EXISTS (SELECT 1 FROM [Subscription] (NOLOCK) WHERE ([CustomerID]=@CustomerID))) BEGIN
INSERT [Subscription] ([Guid], [CustomerID]) VALUES (@GUID, @CustomerID);
END;
FETCH NEXT FROM [MyCursor] INTO @CustomerID;
END;
You’ll also notice that another standard of mine is to place one blank line before and after any BEGIN … END block. That greatly enhances readability.
I’d suggest taking a complex SP you’ve written, and re-write it using my suggestions about BEGIN … END. Then compare the readability to the old version.
Thanks again for putting together this fantastic blog entry Dave!
-Todd (”Speednet”)
My code indentation (tabs) did not make it through the parser. Let’s see if it will work with spaces.
——–
WHILE (@@FETCH_STATUS = 0) BEGIN
IF (NOT EXISTS (SELECT 1 FROM [Subscription] (NOLOCK) WHERE ([CustomerID]=@CustomerID))) BEGIN
INSERT [Subscription] ([Guid], [CustomerID]) VALUES (@GUID, @CustomerID);
END;
FETCH NEXT FROM [MyCursor] INTO @CustomerID;
END;
——–
No dice, I’m not sure how to indent in the comments section. Sorry for the multiple comments.
There is one intent on everything between the WHILE and the final END; and there are two indents on the INSERT.
Speednet,
I appreciate your feedback. I will follow up on it.
Kind Regards,
Pinal
I’m such a dope, I called you by your last name, sorry Pinal.
Speednet,
Your suggestions are valuable.
Kind Regards,
Pinal
I find that when coding SQL queries with multiple joins that this format is more readable.
SELECT Username, Password
FROM UserDetails ud INNER JOIN
Employee e ON e.EmpID = ud.UserID
This was I can easily see which tables are included in the query.
Another nice post.
What About composite Primary Key
[
* Primary Key : PK_
Example:
PK_UserDetails
PK_ Emails
]
??
[...] SQL SERVER - Guidelines and Coding Standards Part - 1 [...]
> If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.
> Use following naming convention for Stored Procedure. sp_[_] Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb
Use SCHEMAs, Luke :)
I’d like to see good old approach “fit all that info in that damn short name”. But I’ll not be happy if I find it in our projects. Precisely, I will be very, very unhappy :)
We prefer to spread our projects on several databases (the separation is based on domain scopes) and schemas (there are no strong rules, but we prefer to create separate schemas for each API segment).
Good luck, guys! Be happy:)
just to bring it to your attention that “Read More” link regarding SET NOCOUNT ON is pointing to “SQL SERVER - Simple Cursor to Select Tables in Database with Static Prefix and Date Created” page.