Software Documentation.Info Logo

 
Subscribe to our blog and be notified about new business software resources
  home | technical writing | business software | software documentation tools | service business software | software testing | contact

Other Software Links

Home > Software Documentation Articles > Documenting your SQL Server database

Documenting your SQL Server database

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.

Creating Database Technical Documentation

Asides from creating database diagrams, there isn't much functionality in SQL Server that allows you to create documentation for your database.

Fortunately there are timesaving 3rd party SQL Server documentor tools that will automatically create comprehensive technical documentation for SQL Server databases.

For software applications that use SQL Server databases, there are various utilities that will create technical documentation for that application plus its associated SQL Server databases. Examples of this are our ASP Documentation Tool (for ASP VBScript and JScript and associated databases), .NET Documentation Tool (for .NET Framework C# and VB.NET code and associated databases), VB Documentation Tool (software documentation tool for Visual Basic 6.0 applications and their associated databases), and PHP Documentation Tool (code documentor for PHP web applications and associated databases).

Related content: sql server, documenting, documentation, how to, guide


© 2007 - 2012 Winnersh Triangle Web Solutions Limited.
Registered company number: 4493816.

Page created Monday, September 17, 2007. Last modified Monday, September 17, 2007.
About This Website | | Site Map | Privacy Policy | Add Your Site