Edition #151 of T-SQL Tuesday is hosted by Mala Mahadevan (b|t), and her chosen topic is to write about coding standards. Or more specifically, T-SQL coding standards.

Uhm … are you trying to tell me that there are other programming languages? 😉

The nice thing about standards …

“The nice things about coding standards is that there are so many to choose from”.

This quote, usually attributed to Andrew S. Tannenbaum, bears a lot of truth. There are indeed many standards. And the debates over which standard is better and which standard should be burned and buried can rage into the small hours. Which is fine when held with friends over a beer or two (or three), and a lot less fine when the debate is raging on the internet.

Pick one and stick to it

For most of the things regulated in coding standards (such as code formatting, standard comments, etc.), my advice is simply to pick one and stick to it.

No. Not like that. Not in the sense that you pick one and stick to it.

Every company should have their coding standards, describe them, and then stick to them. Which means that all employees who write code must follow those standards. Even if they don’t like them. Even if their previous employee had a much “better” (for whatever value of better applies) standard, that they are now totally used to.

Even if SQL Prompt cannot be configured to automatically apply the standard, heck, even when SQL Prompt is not used by the organization.

And yes, that means that for a consultant like me, who works for multiple companies, I may have to switch my coding style and formatting multiple times per day.

Opinions. We all have them.

But I bet you didn’t come here to just hear how I graciously adapt to any standard I see, no matter what that standard is. You wanted controversy. You wanted my opinion.

Opinions are, of course, like asses. We all have them. But that does not mean we should just expose them willy-nilly whenever we feel like it. We should hold back and only share our opinions when people express an interest.

Since this is my blog, and I am an opinionated person, I’ll assume you want to read my opinion. If not, stop reading now – the rest of this post will be highly opinionated!

Object name prefixing

I could probably write about dozens of standards where I have my own (100% correct, of course) opinion, and the rest of the world (100% wrong, of course) refuses to listen. But in the interest of my blood pressure, I have decided to stick to just one.

Probably the one I hate most. And one that is stubbornly persistent. Object name prefixing.

Or, to be more precise, the standard that enforces that all table names need to start with a prefix that designates them as a table, and all view names with a different prefix to clearly mark them as a view. Typically tbl_ and vw_ are used, though I have also seen just the letters t and v, and I have seen them with or without underscores.

I hate this coding standard (or rather, naming standard) with a vengeance. For a few reasons. The perceived benefit is in fact not a benefit at all. It is detrimental to a quick understanding of what I see on the screen. But my biggest objection is that it negates one of the greatest benefits of views.

Perceived benefit

The reason used by proponents of this naming standard to defend their position is that they want to make sure they know whether an object is a view or a table. Because, obviously, it is important to know what object type we are dealing with. Right?

Well, no. Not always. One of the nice aspects of views in SQL is that they behave in most ways exactly like tables, so that we do not really need to know whether an object is a view or a table. We can just use the object in our query, and the DBMS ensures that it works.

But … the exceptions? Yes, indeed. There are exceptions. There are cases where you do indeed want to know whether an object is a table or a view. But you still don’t need to clutter the name with a useless prefix to find out. You can just run a simple query:

SELECT type_desc
FROM   sys.objects
WHERE  name = '<<Name of object>>';

If you find you need to do this a lot, you can even program a keyboard shortcut for this query in SSMS.

“But what if I am looking at a long list of object names in Management Studio?” is an objection I have heard to this. Yes, I have actually heard someone say this. Well, I have a surprise for you. You probably look at that long list of object names in the Object Explorer. And guess what?

The Object Explorer actually keeps tables and views in separate branches of its tree. So if you open your eyes briefly when you click one of the + buttons to expand a branch, you should know what kind of objects you are looking at.

Not exactly rocket science, right?

So why exactly should we prefix all our tables with Tbl_ and all our views with Vw_? Just so you don’t have to execute a single quick query to check the object type, if you even need it at all. Just so you don’t have to pay attention what branch of the Object Explorer tree you expanded?

Readability

If there were no down sides at all, then I might even be tempted to give in and accept the prefixes, just for that perceived benefit of immediately seeing the object type. But the issue is, there are down sides. And one is readability.

Sometimes you see only a part of an object name. The Object Explorer is one. It often sits in the sidebar of the SSMS window, and most people I know don’t make it very wide because they want most of the screen real estate for the query window. But that does mean that only the first part of each object name is visible, and we have to scroll to see the rest.

Another example is execution plans. (I bet you were wondering when I would finally bring up my favorite subject!) In execution plans, especially in the graphical representation, object names are fairly often truncated.

Of course when names are not fully visible there is always potential for confusion. That’s a fact. But prefixes make the problem worse. To me, the names “PurchaseHea…”, “PurchaseLin…”, and “Purchaser” (where two are truncated) may not be fully unambiguous, but they are a lot easier to understand than “tbl_Purchas…”, “tbl_Purchas…”, and “tbl_Purchas…” would be!

Interesting, right? That a coding standard officially intended as increasing readability actually reduces readability!

Compatibility views

But that’s not even my biggest issue. No. My main objection to this naming standard is that it throws one of the benefits of views completely out of the window.

Let’s say we have a table, called Vendors, that lists all our vendors. One of the columns, PrimaryContactID, is a foreign key into the Contacts table, for the primary contact person of that vendor. We have no way to track other contact persons for the same vendor, and that’s becoming an annoyance, so we start a project to change that. For the new data model, we remove the PrimaryContactID column and instead add a new table, VendorContacts, that implements the now many to many relationship between the Vendors and Contacts tables. The VendorContacts table also has a Boolean column IsPrimaryContact so that we still know which contact is the designated primary contact.

But as we do impact analysis we notice that dozens of our current applications query the Vendor and Contact tables, joined on PrimaryContactID. Hundreds of reports seem to use these tables too. And we do not even know how many of our data scientists have used these tables in their saved custom queries. We’ll need to find, change, test, and redeploy all that code, just so it can continue to work exactly the same as it always did. The scope of the project grows and grows.

That’s where views come in. Mary, one of our most talented SQL developers, proposes to create a new table, Suppliers, to hold the data that is now in the Vendors table. Except the PrimaryContactID column, of course. The Suppliers table now links to Contacts in a many to many relationship, SupplierContacts. We can drop the Vendors table after we migrate the data, and then Mary will create a view, called Vendors, that produces data that looks exactly the same as the old Vendors table did. Including the PrimaryContactID, which in that view is of course produced by joining Suppliers to SupplierContacts with a filter on the IsPrimaryContact column.

Now, all of a sudden, the project becomes far easier. All the code units that only care about the primary contact are no longer in scope. They will start using new view instead of the old table. The DBMS ensures that this runs seamlessly. We do not need to find, change, and test any of them. The project can focus on only changing the code that actually needs the new functionality to access other contacts as well. We can change the remaining code gradually over time, preferably in projects that touch that code anyway.

Well. Except for one small detail. We have a naming standard. We do not really have tables called Vendors and Contacts. Their names are tbl_Vendors and tbl_Contacts. And if we follow Mary’s suggestion, then the new tables tbl_Suppliers and tbl_SupplierContacts will be created, tbl_Vendors will be dropped, and the new view to ease the change is called vw_Vendors.

But of course, all of that code we didn’t want to touch looks for tbl_Vendors. Not for vw_Vendors. So we will get errors. And we will need to find and change that code anyway. And any change incurs the risk of an error, if only because I tenfd to fat-finger my keystrokles. So we’ll also need to test all changed code. The scope of the project is all of a sudden huge again!

Well. Okay. We perhaps should actually create a view with the exact name of the old table. Then we do have the benefit that Mary promised. So this means that we now have a view with tbl_Vendors as its name.

It is, of course, easy to see how that will never under any circumstances cause confusion at all. </s>

Summary

When there is any coding standard in place at your workplace, then you should just stick to it. Even if you disagree. A single standard that everybody follows is more important than an endless fight over what is the “best” standard.

But if you force me to give my opinion on standards, you might find me going off on a rant on the naming standard of tables and views. They are supposed to be interchangeable in the SQL language. That only works if their names are interchangeable. Which they are not if your naming standard forces people to use silly prefixes to their names.

We don’t need those prefixes anyway. There are other ways to very quickly and simply retrieve the object type of an object. If you need to know it at all. And the extra characters in front reduce readability of object names in cases where there is limited room and they are truncated. But most of all, using those prefixes robs you of the option to change your database design while retaining the old objects as views so that you can update your old code later, or not at all.

Thanks

Thanks, Mala, for hosting this month’s T-SQL Tuesday. I wish you good luck with the next part of your “job” – the part where you read all contributed posts, and have to make sense of all the conflicting standards that people will suggest!