SQL Nomenclature

You Should Have a SQL Naming Convention

Everyone should have a naming convention for their database objects.  I’m sure I could rant on about all the reasons, but let me just draw a parallel and then give an example.  Whenever you support multiples of anything in IT, the benefits of consistency and standards will always prevail in easing their supportability.  Consider creating a desktop or server OS image if all the hardware is different.  If they were all the same you would only need one image, but if they are not you have to create an image for each underlying architecture.  The same applies with naming standards and supporting many database servers.  If you have a defined nomenclature for your database objects things are easier to find and more importantly you can write scripts that will apply across the board.  If you’ve written any code or scripts you know how much time savings can be gained from code re-use.  SQL Server also has system objects and you want to make sure you can easily identify your objects from the system objects.

Do your stored procedures start with SP_ ?  It is well known that naming your stored procedures with a prefix of SP_ is a bad idea.  Why you ask?  All of the system stored procedures start with that prefix which makes identifying your SPs from the system SPs more difficult.

Are You Enforcing Your Convention to Ensure Standards

Most standards in companies are and should be documented.  Team members are told where the document is located and that they are to follow it.  If this method works for your company then more power to you, but most of the time people rush and the rules are not followed.  So how can you mitigate that?  Here is where SQL 2008 comes to the rescue with Policy Based Management.  You can use PBM to enforce your company standards and prevent people from creating SQL objects that are not within policy.  Using PBM to enforce your naming standards is a whole other series.  Come back next week and you can find the first post here:  SQL Naming Conventions with PBM – SPs

Example

Below is a skeleton outline of a naming standard.  It is provided here as an example so it is not complete, but should give you an idea of the things you might want to build a standard around.

• Stored Procedures – usp_[procedurename]
• Tables – tbl_[table name]
• Views – vw_[view name]
• Columns – [column name]
• Triggers –
• Clustered Index – clidx_[table name]_[column name]
• Nonclustered Index – nclidx_[table name]_[column name]
• Primary Keys – PK_[tablename-column]
• Foreign Keys – FK_[tablename-column_referencetablename-column]
• Constraints – [Constraint Type Identifier]_[table name]_[column name]
• Constraint Type Indentifiers
  • Default – DF

What Convention Do You Use?

I think it would be great to see the conventions that other people use.  What objects did they create a convention for and how did they decide to name them?  If you’re reading this and don’t mind, please post your convention in the comments so we can have an online brain storming session for all to see.

Other Caveats

There are several other things you should take into consideration when developing a naming standard, like avoiding reserved keywords and special characters.  For ideas on things to avoid check out Bob Pusateri’s blog post about bad table and column names HERE.