Object Prefixes: Friend or Foe?

Home > Blogs > SQL Server > Object Prefixes: Friend or Foe?

Object Prefixes: Friend or Foe?

Like This Blog 0 Peter Avila
Added by 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:

001-SQL-Server-Object-Prefixes

Uses a table, while this query:

002-SQL-Server-Object-Prefixes

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_”.

003-SQL-Server-stored-procedure-sp_prefix-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

Videos You May Like

A Simple Introduction to Cisco CML2

0 3877 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Creating Dynamic DNS in Network Environments

0 641 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader teaches how to create Dynamic DNS zones in Network Environments. Video Transcription: Now that we’ve installed DNS, we’ve created our DNS zones, the next step is now, how do we produce those … Continue reading Creating Dynamic DNS in Network Environments

Cable Testers and How to Use them in Network Environments

0 724 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.