Suggested Database Standards Sample
Table Names
1. The table name must clearly identify the collection of data the table represents or contains: e.g., Activity, Addresses.
2. Table names are the only database objects not prefixed since they often represent objects, private fields, and public properties. Most standards call for tables to be plural like a collection of row objects similar to instances of a class: e.g., Clients. This is not a static rule, however.
3. Only the first letter of each whole word in a table name should be capitalized: e.g., BusinessContacts.
4. Functional table names (for many-to-many relationships) should be a combination of the two tables referenced. For example, indexes the many-to many relationships between Subprojects and Documents.
5. Lookup tables should be grouped together. They can be grouped at the end by a name like ‘_lkup’ or ‘_LookUp’: e.g., NamePrefixes_LookUp or NamePrefixes_lkup.
6. Names of all database objects and constraints all prefixed with lower case identifier
Object Type Examples:
Views vw_ + TableName or v + ViewName(If it is combination of more than one table)
Stored Procedures usp_ + ”procedure_name”
Primary Keys pk_ + TableName
Foreign Keys fk_ + ForeignKey + _ + Ref TableName
Index ix_ + TableName + _ + ColumnName
7. Triggers are named to indicate the table they are for and the type of trigger. The purpose of the trigger is identified in the prefix to the name. All triggers should be prefixed with the letters 'tr_', letters designating the type, an underscore, and the table name. The type should be designated as 'Insert' = insert, 'Update' = update, 'Delete' = delete. Example tr_Insert_Products (Insert trigger for Products Table)
Column Names
1. Each column name must be derived from the business name identified during the business/data analysis process. If the column was not identified during the analysis of business data, it must still be given a spelled-out logical name; e.g., FirstName, MiddleInitial, PhoneAreaCode, etc.
2. Column names should be descriptive and clearly understandable by someone who looks at them for the first time.
3. Each word in a column name is capitalized, and, for the sake of clarity, can be separated by an underscore: e.g.,
4. Column names should contain letters, numbers, and underscore characters (“_”) only: e.g., UserHistory_2008.
5. If multiple fields in a table reference the same primary key in another table, the field names should be followed by a suffix. (Example: fk_ProviderID_From fk_ProviderID_To)
6. Column names should be unique throughout the database, if possible.
7. The primary key Column should end with “ID” and begin with “pk_”. If the Column is not auto-numbered, all relationships to that Column must have referential integrity with a cascading update.
8. A Column referencing a primary key Column in another table should have exactly the same name as the Column it is referencing from the reference table plus “fk_” as a prefix to denote foreign key.
9. Column names should not be abbreviated. (Exception if the Column name is too long to be understood)
Stored Procedures
1. All application level and user defined stored procedures are prefixed with the constant 'usp_' with a description of what the stored procedure does. Example: usp_GetLastModifiedDate.
2. Use upper case for all SQL keywords: e.g., SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.
3. Liberally comment code blocks not easily understood by non-technical persons.
4. Use single-line comment markers (--) for comments and headers.
5. Reserve multi-line comments (/*.. ..*/) for blocking out sections of code.
6. Indent SQL code 4 spaces per appropriate line to improve readability.
7. Use single quote characters to delimit strings.
8. Use parentheses to increase readability: e.g., WHERE (Color=’red’ AND (Size = 1 OR Size = 2)).
9. Use BEGIN…..END blocks when multiple statements are present within a conditional code segment.
10. Use one blank line to separate code sections.
11. Use spaces so that expressions read like sentences;
e.g., fillfactor = 25, not fillfactor=25.
12. Place SET statements before any executing code in the procedure.
13. Use SET NOCOUNT ON at the beginning of stored procedures.
14. Tables, views, and stored procedures are always fully qualified to ensure SQL Server follows a direct path to find it rather than searching through the master catalog.
15. Do not call functions repeatedly within your stored procedures, triggers, functions and batches. For example, you might need the length of a string variable in many places of your procedure, but don't call the LEN function whenever it's needed. Instead, call the LEN function once, and store the result in a variable for later use.
Code Readability
1. Write comments in your stored procedures, triggers and SQL batches generously; especially when the meaning of the code logic is not obvious. This helps other programmers and DBAs understand the SQL code.
2. Do not use column numbers in the ORDER BY clause. In the following examples, note that the second query is more readable than the first.
Example 1:
SELECT OrderID, OrderDate FROM Orders ORDER BY 2
Example 2:
SELECT OrderID, OrderDate FROM Orders ORDER BY OrderDate
3. Use the more readable ANSI-Standard Join clauses instead of the old style joins. With ANSI joins, the WHERE clause is used only for filtering data. With older style joins, the WHERE clause handles both the join condition and filtering data. The first of the following two examples shows the old style join syntax, while the second one shows the new ANSI join syntax.
Example 1:
SELECT a.au_id, t.title FROM titles t, authors a, titleauthor ta
WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND t.title LIKE 'Computer%'
Example 2:
SELECT a.au_id, t.title FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id
INNER JOIN titles t ON ta.title_id = t.title_id
WHERE t.title LIKE 'Computer%'
4. Always use a column list in your INSERT statements. This helps avoid problems when the table structure changes (like adding or dropping a column).
5. When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.
6. Avoid wild-card characters at the beginning of a word while searching using the LIKE keyword since this can result in an index scan and defeat the purpose of an index.SELECT OrderID, OrderDate FROM Orders ORDER BY OrderDate.
7. Use the more readable ANSI-Standard Join clauses instead of the old style joins. With ANSI joins, the WHERE clause is used only for filtering data. With older style joins, the WHERE clause handles both the join condition and filtering data. The first of the following two examples shows the old style join syntax, while the second one shows the new ANSI join syntax.
Example 1:
SELECT a.au_id, t.title FROM titles t, authors a, titleauthor ta
WHERE (a.au_id = ta.au_id AND ta.title_id = t.title_id) AND (t.title LIKE 'Computer%')
Example 2:
SELECT a.au_id, t.title FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id
INNER JOIN titles t ON ta.title_id = t.title_id
WHERE t.title LIKE 'Computer%'
8. Do not use SELECT *; always list all fields by name in the SELECT statement.
9. Avoid unnecessary use of temporary tables; use 'Derived tables' or CTE (Common Table Expressions) wherever possible, as they perform better.
10. To make SQL statements more readable, start each clause on a new line and indent when needed.
11. Do not use GOTO as this is bad coding by any standard.
12. Check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE), so that you can rollback the transaction if an error occurs. However, make sure that you assign @@ERROR to a variable before using an IF statement since this will cause @@ERROR to reset, e.g.;
Figure 1: Sample Formatted Stored Procedure using Suggested Standards
Miscellaneous
1. Try to avoid server side cursors unless absolutely necessary. Cursors can often be avoided by using SELECT statements instead. If a cursor is unavoidable, use a WHILE loop instead. A WHILE loop is always faster than a cursor. For a WHILE loop to replace a cursor you need a column (primary key or unique key) to identify each row uniquely. Every table must have a primary or unique key in any case.
2. Views are generally used to show specific data to specific users based on their interest. Views are also used to restrict access to the base tables by granting permission only on views. Another significant use of views is that they simplify queries. Incorporate frequently required, complicated joins and calculations into a view so that you don't have to repeat those joins/calculations in all queries. Instead, just select from the view.
3. Given a choice, do not store binary or image files (Binary Large Objects or BLOBs) inside the database. Instead, store the path to the binary or image file in the database and use that as a pointer to the actual binary file stored elsewhere on an FTP server. Retrieving and manipulating these large binary files is better performed outside the database or with specialized systems like FileNet.
4. Avoid dynamic, string concatenated SQL statements embedded in the application code or in SQL statements. The myriad possible execution plans can cause the recompiling of other static stored procedures.
5. Minimize the use of NULLs if possible. ISNULL and COALESCE functions are helpful in dealing with NULL values.
6. Always access tables in the same order in all your stored procedures and triggers consistently. This helps in avoiding deadlocks. Other things to keep in mind to avoid deadlocks are:
Keep your transactions as short as possible.
Touch the least amount of data possible during a transaction.
Do not wait for user input in the middle of a transaction.
Do not use higher level locking hints or restrictive isolation levels unless they are absolutely needed.
7. In your applications, process all the results returned by SQL Server immediately so that the locks on the processed rows are released, hence no blocking. Close all database connections in an application as soon as they are not need to allow efficient pooling.
8. Offload tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to applications if these operations are going to consume more CPU cycles on the database server. Also, do basic validations in the front-end itself during data entry. This saves unnecessary network roundtrips.
9. Always check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction in case of an error (@@ERROR will be greater than 0 in case of an error). This is important because, by default, SQL Server will not rollback all the previous changes within a transaction if a particular statement fails. This behavior can be changed by executing SET XACT_ABORT ON. The @@ROWCOUNT variable also plays an important role in determining how many rows were affected by a previous data manipulation (also, retrieval) statement, and based on that you could choose to commit or rollback a particular transaction.
Example: See Figure 1 above.
10. Always store 4 digit years instead of 2 digit years in dates (especially when using CHAR or INT data type columns) to avoid any confusion and problems. This is not a problem with DATETIME columns, as the century is stored even if you specify a 2 digit year. But it's always a good practice to specify 4 digit years even with DATETIME data type columns.