Object Prefixes: Friend or Foe?

Like This Blog 0

Added by Peter Avila March 24, 2014

Contrary to popular belief, prefixing object names isn’t always a good idea and some professionals have dropped the practice entirely. To see why this is, let’s take a look at database object names as an example. Initially, it might seem like a good idea to prefix table names with “tbl,” views with “v,” and so on, so that it’s easier to identify that, say, this query:

Uses a table, while this query:

Uses a view.

OK, that sounds reasonable enough, but consider this situation: Let’s say that changes to the mini-world one day require you to split that tblEmployee table into two or more tables (maybe new security requirements make it necessary to put some columns into one table and other columns into another table, or maybe the addition of new data into the database causes the current design to become denormalized, etc.) so that you end up with tblEmployee1 and tblEmployee2, for example, instead of tblEmployee.

After that change, any references to the original tblEmployee table (in stored procedures, functions, views, scripts, and so on) no longer work! One way to resolve this is to change all references to tblEmployee accordingly, but that might be very time consuming and even error prone, especially if you have hundreds or even thousands of references to tblEmployee out there. An easier approach might be to create a new view that joins the two new tables together, and then give that view the same name as the original table. That way, the references continue to work. But now you have a view called tblEmployee—a view with a table prefix!

Just because an object starts its life as one type of object (in our example, a table) doesn’t mean that it will remain that type forever. This is not uncommon and you can see a real-world example of it right in SQL Server. If you expand the System Stored Procedures folder of any database, you will see that most system stored procedures have a “sp_” prefix. If you scroll down to the bottom of the list, you will see some that use the prefix “xp_”.

Originally, when Microsoft developed these system stored procedures, the idea was that those that were written in Transact-SQL would use the “sp_” prefix (system procedure), and those that were written in an external language like C++  would use the “xp_” prefix (external procedure). But over time, and for good reasons that don’t matter here, some external procedures were converted to Transact-SQL. If you script out some of those external system stored procedures, you will notice that they are written in Transact-SQL (just right-click on one of them and select Modify). The result is that there are now system stored procedures with the wrong prefix!

Time has a way of changing things, and prefixes assume that an object’s type will never change. The result is that, over time, prefixes that were originally put in place to keep things nice and organized end up having the opposite effect of making things messy. Many system professionals feel that a tool that has a tendency of accomplishing the opposite effect of what it was intended to accomplish becomes untrustworthy and is not worth using at all.

And that’s it! Enjoy!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ