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 (http://blog.SQLAuthority.com)

About these ads

36 thoughts on “SQL SERVER – Guidelines and Coding Standards Part – 1

  1. 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”)

    Like

  2. 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;
    ——–

    Like

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

    Like

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

    Like

  5. Pingback: SQL SERVER - Guidelines and Coding Standards Complete List Download Journey to SQL Authority with Pinal Dave

  6. > 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:)

    Like

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

    Like

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

    Like

  9. “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.

    Like

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

    Like

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

    Like

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

    Like

  13. Hi Pinal,

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

    A great post

    Devaraj Diraviyam
    System Architect

    Like

  14. Hi Dave,

    Very interesting to see your blog.I m a beginner of Sql Server.when i search queries in Google, your blog will come in the top.most probably i ll prefer your blog to learn.So keep share your knowledge with all and i proud to say u r my role model for my sql knowledge..

    thanks a lot

    Like

  15. hi ,
    this is Deepak,i want to know can we write two queries in single query i.e. counting number of rows and selecting total rows and putting that query in resultset is it possible?
    thanks in advance

    Like

  16. You are one of the best . like a god help to me in learning sql in detail in a simplest, easiest way. I cannot express everything in words but you are simply great. Mr. rather I would say SQL. Pinal Dave instead of Mr……
    Regards Amit M Bhelekar

    Like

  17. I was told always never user pularl names for database names so I disagree with you on this
    This comes from the fact that one row is a object, not a collection of objects.

    Like

  18. If you are using ‘sp’ prefix to your stored procedures for naming conventions then it will first search at the system stored procedures and it may cost the reduce in speed.
    For instance if the normal time of procedure is 1 second it may take 3 to 4 seconds.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s