Tuesday, June 21, 2011

SQL SERVER – database-coding-standards-and-guidelines-part-1


SQL Server Database Coding Standards and Guidelines – Part 1

Tables: Rules: Pascal notation; end with an ‘s’
  • Examples: Products, Customers
  • Group related table names1
Stored Procs: Rules: sp<App Name>_[<Group Name >_]<Action><table/logical instance>
  • Examples: spOrders_GetNewOrders, spProducts_UpdateProduct
Triggers: Rules: TR_<TableName>_<action>
  • Examples: TR_Orders_UpdateProducts
  • 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_Orderss
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_’2
  • Prefix table names with the owner name3
  • 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
  • Use upper case for all SQL keywords
  • 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 joins4
  • Place SET statements before any executing code in the procedure.
1) Group related table names:
2) 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.
3) 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.
False code:
FROM Table1, Table2
WHERE Table1.d = Table2.c
True code:
FROM Table1
INNER JOIN Table2 ON Table1.d = Table2.c


Post a Comment


Web Design Company karimnagar, Web Designing warangal, Logo Design Company nizamabad, Indian Website Design Company,