Ivor O’Connor

September 12, 2009

SQL Naming Conventions

Filed under: Uncategorized — ioconnor @ 6:50 pm

This will be an ongoing post. As more is discovered, including my personal preferences, more will be added. The first site through google seems like a good starting point. Though it comes from a M$ SQL user. I’ll use it as the template which I’ll add on to or delete out as I see fit. Just understanding all he wrote is more than I can handle. This post will change dramatically with time.

Tables: Use “Pascal” notation for SQL server Objects like Tables, Views, and Stored Procedures; end with an ‘s’. If you have many subsets of tables group them with a prefix followed by a ‘_’. Capitalize the first letter of each concatenated word otherwise.

  • Examples: Products, Customers, UserDetails, Products_USA, Products_India, Products_Mexico

Stored Procs: Rules: sp<App Name>_[<Group Name >_]<Action><table/logical instance> Action can be: Get, Delete, Update, Write, Archive, Insert or some other verb.

  • Examples: spOrders_GetNewOrders, spProducts_UpdateProduct

Triggers: Rules: TR_<TableName>_<action>

  • Examples: TR_Orders_UpdateProducts, TR_Emails_LogEmailChanges, TR_UserDetails_updateUserName
  • 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_Orders

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_’. Apparently M$ uses this to do special processing. Since I’d never use SQL Server for anything I’ll discount this. However here is the explanation: 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.
  • Prefix table names with the owner name. Some examples would have helped here. The author states: 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.

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’
  • 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 joins.
      • Example:
        old style join:
      • SELECT *
        FROM Table1, Table2
        WHERE Table1.d = Table2.c

        ANSI style join:
        SELECT *
        FROM Table1
        INNER JOIN Table2 ON Table1.d = Table2.c

  • Place SET statements before any executing code in the procedure.
Advertisements

1 Comment »

  1. All these convenstions are right except of sp prefix for stored procedure convention. That’s why, it first looks in system database(s) if we use sp prefix. Good Article!

    Comment by Manish — December 25, 2009 @ 6:04 am


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: