Chittibabu

SQL Server Database Coding Standards and Guidelines – Part 1

• Examples: Products, Customers
• Group related table names¹

• Examples: spOrders_GetNewOrders, spProducts_UpdateProduct

• Examples: TR_Orders_UpdateProducts
• Notes: The use of triggers is discouraged

• Examples: IX_Products_ProductID

• Examples: PK_Products

• Example: FK_Products_Orderss

• Example: DF_Products_Quantity

• Example: The Customers table has an ID column
• The Orders table should have a CustomerID column

• 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_’²
• Prefix table names with the owner name³

• 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
  • 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⁴
• Place SET statements before any executing code in the procedure.