Object Prefixes: Friend or Foe?
Object Prefixes: Friend or Foe?
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!
SQL Server Instructor – Interface Technical Training
You May Also Like
Object Prefixes, prefix table names, sp_prefix, System Stored Procedures, tbl, xp_prefix
A Simple Introduction to Cisco CML20 3660 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
Configuring Windows Mobility Center and How to Turn it On and Off1 1404 1
Video transcription Steve Fullmer: In our Windows training courses, we often share information about the Windows 8.1 Mobility Center. Mobility Center was introduced for mobile and laptop devices in Windows 7. It’s present and somewhat enhanced in Windows 8. Since we don’t have mobile devices in our classrooms, I decided to take a little bit … Continue reading Configuring Windows Mobility Center and How to Turn it On and Off
OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj0 246 1
In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.