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)

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

  • very nice article…….

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

    Reply
  • Nice article.

    Thanks a Lot…

    Reply
  • nice post

    Reply
  • Great Post!!!

    Reply
  • Thanks to Aaron Bertrand for their feedback in correcting few of the incorrect information. I request you to read his excellent article here – for Best Practices Checklist.

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

    Reply
  • Amit M. Bhelekar
    February 24, 2012 10:55 am

    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

    Reply
  • How it is different from oracle sql?

    Reply
  • himanshu nagar
    January 20, 2013 9:49 pm

    Hi Sir ,

    What is the difference between the following two syntax :

    1. select * from Table and
    2. Select (*) from Table

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

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

    Reply
  • It’s not correct. It only searches for system SP if you prefix with sp_. Nevertheless, prefixes are useless at all. If one uses auto-completion, the first keystrokes are of no earthly use. Search results on objects (sys.objects, DMVs etc.) are returned in prefix order and not, what is in every case preferrable, in context order. Ordering with respect to context is heavily complicated by using prefixes.
    Every programming language has bowed out of hungarian notation decades ago – why not SQL?

    Reply
  • Singular names for database tables are a sign of mixing conceptual / logical design with physical design. In conceptual / logical design the focus is on the entities, which in fact are designed in singular. Tables will in most cases hold a lot of these entities. That’s why tables should be named in plural.

    Reply
  • Amol Khadbade
    June 22, 2016 3:49 pm

    table name should start with tbl i think

    Reply

Leave a Reply

Menu