The topic of this blog post is not something that is new, but is intended to address a common issue I have found in most database documentation. Most database documentations fail to address name resolution errors – i.e. the ability to ensure that a name uniquely identifies a database object.

For example, it is quite possible to create an Employee table in the Person or dbo schema in the AdventureWorks2008R2 database even though another table with the same name already exists in the HumanResources schema.

Good database documentation is one which leaves very little room for interpretation – especially when ownership of objects and security relationships is concerned. This post looks at the most commonly used naming conventions when documenting databases and finally comes up with a recommendation for their usage.

Three-Part Naming

The three part naming is the standard for unique identification of any database object. The three part naming convention essentially consists of:

• Database Name
• Schema Name
• Object Name

The standard representation is like: [Database Name].[Schema Name].[Object Name].

Four Part Naming

A common variant of the 3-part naming convention is the 4 part naming convention, which also adds the SQL Server instance name to the mix, making the representation like: [SQL Instance Name].[Database Name].[Schema Name].[Object Name].

This naming convention is essential when attempting to access data across remote/linked servers. However, it is strongly tied to the SQL Server instance name, and therefore I do not prefer it when documenting my databases.

Two Part Naming

Most applications do not have a restriction on the database name (this was not the case for legacy systems). In such cases, having the [Database Name] in the three part naming convention does not make sense. Most documentation therefore adopt what is called as the two-part naming standard.

Under the two-part naming standard, a database object is represented as: [Schema Name].[Object Name].

It is my humble request to the kind reader to please use the two-part naming convention at the very least (even when using the default “dbo” schema).

Other Variants

Those coming directly from the world of SQL Server 2000 or those used to working within the default “dbo” schema would omit the “Schema Name” and the representation would be like: [Database Name]..[Object Name].

I do not subscribe to this theory as it makes the documentation open to interpretation.

For example, in cases when the database design is such that all objects use a schema other than “dbo” does omitting the schema name indicate that all objects use the available user-defined schema or that they use “dbo”? It is therefore, my recommendation that when writing database documentation, a two-part naming convention should be used at the very least.

Conclusion

Name resolution is not a problem limited to the database documentation. When ambiguous naming is encountered in database code, the results can be confusing and make troubleshooting even more difficult. The recommendation for usage of naming conventions in documentation or in database code is therefore:

• The two-part naming convention is sufficient to prevent name resolution errors within the same database
• When more than one database is involved, the three-part naming convention should be used
• Similarly, when more than one SQL Server instance is involved, the full length four-part naming convention is required

All other variants leave room for interpretation, and are not something I would recommend.

I would be happy to hear about the naming convention followed by you, the kind reader. Do leave a note in the blog comments as you go.

Until we meet next time,

Be courteous. Drive responsibly.