10 Oct 2012 Use What Works: Prefixing Database Tables With ‘tbl’
That quote is from Karen López (blog | @datachick), and she often makes the comment in reference to my advocating the use of prefixes in object names. My reason for wanting to use prefixes is simple enough: I want to know if I am looking at a table or a view when reviewing code.
Karen doesn’t believe that anyone should be using object names as a place to store meta data about the objects themselves. I would like to agree with her but then we’d both be wrong.
I am not a fan of her position or her quote. Can you imagine your child comes home from school having trouble understanding how division works and your advice is “you need to try harder”?
Our Tools Are Failing Us
Karen’s quote would have you believe that she is putting the onus on people to understand what database object they are looking at without any sort of hint or help. To me that is no different than calling the matter a “training issue”, an excuse for why things are the way they are but not a solution for how to avoid the issue from happening in the first place.
What Karen and I agree on here is that our tools are failing us. Karen reminds me often about how our tools have a greater impact on database design decisions than what we realize. When those tools fail to provide the details I need as a DBA, such as distinguishing a table from a view, we end up with prefixes for our table names.
I believe that I am in the minority these days when it comes to using table prefixes. I laugh when someone tells me they would never use a prefix to identify a table, but they would use one for views. It’s as if they don’t understand that by NOT using a prefix they are still designating the difference between a table and a view anyway!
I know many people that designate a variety of database objects. Indexes are common example, as folks want to know if the index is clustered, non-clustered, or perhaps filtered. Triggers, functions, stored procedures…all objects that get affixed with a prefix most of the time. Even data warehouses will use “Dim” and “Fact”. But for some reason the idea of using a prefix on a table is frowned upon by the same establishment that would advocate the use of prefixes elsewhere!
How SSMS 2012 Helps
Check out what I found in SSMS 2012 the other day. If you hover over the name of the object in a T-SQL statement you get a tooltip that explains what you are looking at:
How cool is that?
I love finding new things like this when using updated versions of tools that I have been using for years. I have no idea if this is new to SSMS 2012 or not, but it is new to me and I am enjoying it.
So, looks like I don’t need to try harder, as Karen would suggest. It would seem that Microsoft was the one that went out of their way to help me. They took a tool that had been failing me as a DBA and made it better.
I am still going to use prefixes though. If anyone can prove to me that the use of a prefix in a table name causes a performance issue then I will reverse my position on this matter. Until then, I will continue to use them, and to advocate for their use, because there are folks like me for which this method works best. Also, not everyone uses SSMS, so I want to be mindful for that.
Using prefixes still works for me. It also works for others. It may not be your choice, but that does not make it the wrong choice for others.