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!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ
Agile Methodology in Project Management
In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management
Creating Users and Managing Passwords in Microsoft Office 365
In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365. For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365
Detailed Forensic Investigation of Malware Infections – April 21, 2015
How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015
Write a Comment
