This page describes how to document your Microsoft SQL Server database. The principles are similar for all versions of Microsoft' SQL Server, although SQL Server 2005 and above contain a number of neat enhancements to database documentation capabilities. The article includes database naming conventions, adding object descriptions, documenting stored procedures, and how to create technical documentation for your Microsoft SQL Server databases.
Much of this article can also apply equally to other relational databases such as Oracle, MySQL, PostgreSQL and Microsoft Access.
Database Naming Conventions
If you're designing a new database, then it makes sense to follow a logical database entity naming convention. The following are a few suggestions for naming the objects in your new database.
General naming conventions
Database names should where possible use alphanumeric characters only. Avoid using hyphens as they can subsequently make it difficult to compose certain Transact SQL queries.
If possible, also try to avoid using spaces in database entities, particularly in table names and column names. While Transact SQL can still refer to these entities if they are enclosed in square brackets, it can sometimes lead to confusion and coding errors.
It is also a good idea to avoid using table and column names that are reserved words in Transact SQL, such as month, year or user.
Table naming conventions
Tables should where possible be given names that relate to the data stored within them. For example, employee data should be stored in a table called Employees. The plural form of the word is used, as there will more than likely be more than one employee stored in the table.
Some developers prefix table names with something like t_. Such prefixes can be particularly useful if the tables are referenced from application source code, as it makes it more obvious to the software development team that a table rather than a view or some other entity is being referenced.
Giving a table a prefix related to its function (e.g. EmployeeData_ can help to group tables into related categories. SQL Server 2005 introduces the concept of schemas. This allows tables to be grouped accordingly. For example, the AdventureWorks sample database contains a HumanResources schema, and the associated tables (Employee, EmployeeAddress, EmployeeDepartmentHistory etc.) are all listed under this schema in the SQL Server Management Studio table list.
Foreign key naming conventions
It is particularly useful to be able to identify the foreign keys within a database table. Prefixing them with something like fk_ makes it much more straightforward to identify table relationships just by looking at the table's columns.
Stored Procedure naming conventions
It is always useful to name stored procedures according to their use. For example: GetUserID, InsertUserDateOfBirth or UpdateCardPaymentInfo.
On occasions, it is useful to add a suffix to show the stored procedure's input parameters. For example the stored procedure GetUserIDByUserNameAndPassword will return take a user name and password as input parameters and return a user ID . This can be used to differentiate stored procedures that have similar functionality but have different parameters. This would allow other related stored procedures to be added, e.g. GetUserIDByGUID and GetUserIDByApplicantID. The downside to this naming convention is that the stored procedure names can become quite long.
Some developers prefix all their stored procedures with certain tags. A popular convention is to name a stored procedure with a sp_ prefix. However, this is not recommended best practice for two reasons. Firstly, there is a slight reduction in database performance, as the SQL Server will check for stored procedures with this prefix in the master database first. This performance reduction is small, but it may be significant in high end enterprise systems. Secondly, since Microsoft uses this prefix for system stored procedures, there is always the chance that you could give your own stored procedure the same name as a system stored procedure. It is also possible that a future version of SQL Server could introduce a new system stored procedure with the same name as one of your existing user stored procedures.
If you do want to prefix your stored procedures then it is recommended to use something like usp_ or sproc_. Likewise a function could be prefixed with something like func_. Such prefixes can be particularly useful if the stored procedures are called from application source code, as it makes it more obvious to the software development team that a stored procedure is being called.
Documenting SQL Stored Procedures
Remember also to document the Transact SQL code of stored procedures and functions. While simple queries should be self explanatory, larger queries will benefit from documentation. Stored procedures will also benefit from a standard header, which at the very least should describe the procedure's functionality. Including a change log will also help to track changes, particularly if you don't have any source control system in place.
An example header for a stored procedure is shown below:
/*
Description: Gets a user's UserID
Author: Geoff Titman
Create Date: 15/09/2001
Param: @UserName = User's login name
Param: @Password = User's password
Return: UserID of the user
Modified Date: 10/10/2007
Modification: Added to check to see if the user's account has been suspended
*/
Note that adding comments to stored procedures has no affect on their performance.
Database Object Descriptions
Being able to give database objects descriptions goes some way towards being able to create a self-documenting database.
Microsoft's SQL Server 7.0 introduced the facility to add a Description to a table through the table design window. SQL Server 2000 enhanced this functionality by introducing extended properties. Unfortunately the SQL Server 2000 Enterprise Manager has limited capabilities for allowing these properties to be edited. However, in SQL Server 2005 the SQL Server Management Studio GUI allows extended properties to be edited. Most objects in a database (e.g. tables, table columns, views, functions, stored procedures, the database itself) have extended properties that may be edited. By default there is only a single extended property, MS_Description. Even more limiting is that although you can use the MS_Description extended property to add descriptions to objects, without 3rd party add-ons such as the SQL Documention Tool, there isn't actually a lot else can do with them.