SQL SERVER – Guidelines and Coding Standards Part – 1

  • 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
  • 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.

  • It is common practice to use Primary Key as IDENTITY column but it is not necessary. PK of your table should be selected very carefully.
  • 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 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 unless there is specific requirements.
  • Practice to put the DECLARE statements at the starting of the code in the stored procedure for better readability (Example)
  • Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure. (Example)

© Copyright 2000-2008 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (https://blog.sqlauthority.com)

Best Practices, Database, Software Development, SQL Coding Standards, SQL Constraint and Keys, SQL Index, SQL Joins, SQL Scripts, SQL Stored Procedure
Previous Post
SQLAuthority Author Visit – Ahmedabad SQL Server User Group Meeting – September 2008
Next Post
SQL SERVER – Guidelines and Coding Standards Part – 2

Related Posts

38 Comments. Leave new

  • Simply great!!!. I am a regular reader of this blog. Thanks for having excellent compilations like this.

    Regards
    Chella Ganesh

    Reply
  • To differentiate from system stored procedures, should not we use “usp” instead of “sp” prefix.

    Regards

    Reply
  • 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”)

    Reply
  • 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;
    ——–

    Reply
  • 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.

    Reply
  • Speednet,

    I appreciate your feedback. I will follow up on it.

    Kind Regards,
    Pinal

    Reply
  • I’m such a dope, I called you by your last name, sorry Pinal.

    Reply
  • Speednet,

    Your suggestions are valuable.

    Kind Regards,
    Pinal

    Reply
  • 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.

    Reply
  • Another nice post.

    What About composite Primary Key

    [
    * Primary Key : PK_

    Example:

    PK_UserDetails

    PK_ Emails
    ]
    ??

    Reply
  • > 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:)

    Reply
  • 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.

    Reply
  • I think that by adding sp to a stored procedure the sql engine looks first in the master db for the stored procedure.

    I do not like any prefixes – name stored procedures based on the table/object they most affect and what they do based on CRUD

    CustomerGet (get one customer)
    CustomerSelect (get more than one customer)
    CustomerUpdate
    CustomerAdd
    CustomerDelete

    The table is Customers (with the s, becuase that is what is in the table as a whole, the customers)

    a view that joins between Customers and Accounts and groups on customer (where a customer can have more than one account). Since Customer is the group by it is listed first in the name

    CustomersAccounts

    Accounts and Customers listed by account, since account is the group by it is listed first in the name

    AccountsCustomers

    Reply
  • Brian Tkatch
    March 9, 2009 7:23 pm

    “The table is Customers (with the s, becuase that is what is in the table as a whole, the customers)”

    That is not how i see it at all. The TABLE merely defines the records within it, which each are, individually, one customer.

    Reply
  • Brian Tkatch
    March 9, 2009 7:31 pm

    The SET NOCOUNT link should probably point o https://blog.sqlauthority.com/2007/04/28/sql-server-good-better-and-best-programming-techniques/ where he says: “Remember to SET NOCOUNT ON at the beginning of your SQL bataches, stored procedures, triggers to avoid network traffic. This will also reduct the chances of error on linked server.”

    Reply
  • Alex Romanov
    June 11, 2009 8:00 pm

    Microsoft does not reccomend using sp prefix for stored procedures because it is alreday used by system stored procs and user defined proc with sp in it will have additional overhead during name complie process

    Reply
  • sorry for late response, but this is an interesting thread. as for prefixes, they are used in most environments and help to keep things consistent, because when you are calling sp from application code, the prefix really helps…and for user sp, usp_ does the trick. also, yay Schemas! having to work on DBs where 1000s of tables are dumped together, and you have to figure out how they relate and what the logical groupings are, Shemas are a great underused way to manage this.

    Reply
  • hello sir,

    Can u send me the complete reference of ‘CREATE INDEX’ which session was taken by u on 3rd october.

    You have announced that, if anybody will send me request me
    i will send the comepete reference of ‘CREATE INDEX’ with Examples.

    Reply
  • With the current there is a rise in the number of facilities and a decrease in funding and staffing in the Air Inspections Program. ,

    Reply
  • Devaraj Diraviyam
    December 15, 2009 4:46 pm

    Hi Pinal,

    Is there any advantage of using ‘s’ at the end of the Table or View?

    A great post

    Devaraj Diraviyam
    System Architect

    Reply

Leave a Reply