“If you can’t tell a table from a view you need to try harder”.
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.
A wise man named Buck Woody (blog | @buckwoody) once told me “Use what works”.
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.
I use prefixes for table names for exactly the reason you mention. I also continue to use a variation of the Lezinski Naming Conventions for program variables. Why? They make my life easier, again for exactly the reason you mention.
The person criticizing this practice seems to belong in the category that Joel Spolsky calls Architecture Astronauts. I agree with Buck Woody–use what works.
Exactly my point…use what works for you.
Don’t wishs to contradict the many wise opinions here but some thoughts having supported many applications over the years: 1 It is important to know a table from a view – especially if you have little time and a LOT of code review to do.Where i work we have to give a time estimate for a code review – depending on how many stored procedures/functions there are. If i look at something that *looks* like a five table join but is actually five views that drill down into fifty views and 200 tables (has happened), then my estimate is wayyyy of the mark. That is where ‘tbl..’, ‘vw…’ comes in handy. 2 I get the point on refactoring – but refactoring on a large level or even splitting a much used table into parts and so on are *very* difficult to do in a highly used production environment. When you can absolutely prove the performance gain on an important query that is very significant to them by doing something of that nature then you can perhaps get the go ahead but not becuase you think the person before you designed it poorly or it is some query that shaves off a few seconds that makes you thrilled as a DBA but management couldn’t care less for the gains. In short, most refactoring does not happen the way it should – it is bandaided unfortunately to the extent possible. When large scale refactoring happens there is usually time to name objects appropriately. 3 What would you do if inherit something someone did? Very rarely do people get to support somethign they designed from scratch from start through lifecycle…so if the person before you was a naming fanatic and you think it is totally wrong – would it be enough grounds to refactor the whole thing? I dont think so – but if there is any company that allows for that am much interested in working for them!! In short like Tom said earlier ‘do what works best’. Puritanical stances are nice for debate but unfortunately the real works is a lot tougher!!
Thanks Mal!
Synonyms? Do people actually use those?
Are you serious? If so: yes. Absolutely yes.
I’ve never seen synonyms used except in BOL examples or very old code. Would they help reduce the urge to use a prefix? Or just make the concept worse?
Yes. In the same way views are used to encapsulate vertical/horizontal partitioning, synonyms are used to support AB table switching , Snapshot usage during ETL , Sharded databases, federated servers and so on..
But Mal, needing to distinguish between tables and views certainly doesn’t mean you need to prefix *both*. If you prefix your views (which I still don’t think is necessary in a lot of cases), then you can tell your views by the prefix, and you can tell your tables because they *don’t* have a view prefix.
Aaron,in theory possibly yes.But it is very difficult to have partial standards.It confuses developers and management too, either you name them all a certain way or don’t name them at all.I wish we had wise folks who listened to these arguments but in fact i would be content if they understood 3 levels of normalisation, even that is pretty tough. And in many cases it is for understanding what tables belong to what functionality and is demanded for regardless of what one may think of it otherwise.
…and don’t forget that despite your standards in house, you end up supporting vendor apps that don’t adhere to your standards and you need to unwind all their code.
Times like this I wish ANSI had a standard for everyone to reference.
Mal,
I think you have offered the best summation so far. You would choose one method for designing, and (possibly) the other for supporting.
It has made me think of a new blog post that I need to write, thanks!
Thank you Tom!
That’s why I write all my database interface code in Fortran. It works great for me!
Some of my coworkers hate it. I like your idea to call them “architecture astronauts” and move on.
I’ve posted my reasons why I hate metadata stuffing. Once you see how it’s practiced out in the wild, you realize just how crazy people can get it with. It doesn’t “just work”
Good comment. I’ve recently seen more and more people of late rubbishing the use of object name prefixes as if there is a definitive law and that their opinion must be right since it is *their* opinion. The truth in the matter is that the only thing that really matters is that you code and naming standards are consistent and readable. As good as the SSMS 2012 context tips are, that is still an extra step to take when reviewing code.
Thanks Mark.
Performance issue, no. However…
If I (as a database developer) realise at some point after design once the system is already in use that I really want that table to be two tables (or three, or…), what I can do is replace the table with a view and (in theory) the application won’t notice. It shouldn’t notice anyway. However, if I’d prefixed the table name with tbl (which personally I never, never, never do), I now have either a view named tblSomething or I have to go through the code that used that table and change it everywhere. Kinda what I’d have been trying to avoid by using the view to replace the table in the first place. And yes, I have had to do this before on a number of occasions.
On the point of DIM, FACT or similar, that’s designating the object’s purpose, not what it is. A fact table and a dimension table are both tables and there’s no tooling that could ever tell us the difference as they differ as to their purpose not their type. Just like I might likely call tables that have balances for accounts AccountBalances, not just Balances.
Personally, I feel we need better tools (I don’t have any problems telling a view from a table because of SQLPrompt), not a bunch of prefixes.
Thanks Gail.
Yes, refactoring can make the prefix approach difficult. Just like naming a column IntID. What happens when you need to expand to BIGINT?
Part of refactoring is the cleanup, which would imply that while the temporary step would be to have a view named ‘tbl’, at some point you would go back and remove that object as all new code would be using the new tables.
[Of course we all know that refactoring projects rarely clean up after themselves and I will leave that blog post for a different day.]
However, if I didn’t have the tbl prefix there at all, I wouldn’t need to spend ages (and it can be absolutely ages) changing and re-testing procedures and application code
And rebuilding hundreds of reports…
Or the dtSalesDate that’s actually a VARCHAR(50). Love those.
I shudder at the thought of normalizing a production table into two (or three, or …) tables, and then creating a view so that the other application layers don’t notice the change. This would be an absolute nightmare, especially if you have insert, update or delete sprocs hitting the new view.
I would much rather make the changes in the schema and then make the changes in the code and update my unit tests.
No, it’s actually dead easy. Create the view, create an instead of trigger (or 3) on the view, ensure that they’re properly written and run your tests. Unless you have some strange front end that checks to see if it is inserting into a table (yeah, had one of those), that’s just about all that needs doing.
You can go back and refactor later, but the point is, you don’t have to.
Gail, I completely agree with you. I had to maintain systems that used prefixes for tables and materialized views (in Oracle, Indexed Views in SQL Server). Some of the prefixes were already wrong as the original programmers didn’t change the prefix anymore after changing the tables into materialized views. They would have had to change the code in many tools: database, Informatica, Java code, etc.
95% of the time when you are writing queries you don’t need to know whether it is a table or a view – you should just be assuming it’s a table unless you have reason to investigate further (e.g. there is a performance issue and maybe you are using an uber-view).
I think a prefix on *some* objects can make sense. If you need to distinguish between views and tables, you can do that by *only* prefixing views. If it doesn’t have a view prefix, it’s a table. I see no real gain to have to type an extra three characters every time I reference Customers, Employees, or Orders. If I am going against View_Customers or vwCustomers, I know I am not working against a base table. You can also use a different schema for views, but that works against the lazy folks who don’t like to use schema prefixes.
But in the end, you and Mark are both right: use what works, just do it consistently.
Agreed, consistency is very important here.
Tom, thank you for this post. When I was first starting with databases I was told prefixes were outright wrong; thus I stopped because I was concerned about “doing it right,” and I hadn’t the experience then to go against what a “more experienced” user suggested. However, I’ve often felt there were times I wish I had because it just made things easier to identify objects. Call it the librarian in me that needs to organize things. At least now I have a better perspective on this. Thank you.
Gary, you are most welcome! Don’t be bullied by others into blindly following for no reason. Others have pointed out valid concerns for why they don’t use prefixes for tables, and I see their point(s), but they haven’t convinced me yet that I need to stop using prefixes altogether.
the prefixes convention I find depends on the route you took into programming and databases. Each tools will drive a convention in their tutorials and examples. Generally I find people will adopt what they see most frequently.
The most frequent usage is… table are common and don;t need to be highlighted other db objects are used less frequently by scripters and programmers and hence need to be highlighted as different, hence the vw_, idx_, trg_ sp_ prefixes that abound.
For some designs the difference between a view and table (views were used to enable DW switching and loads) was irrelevant in these cases there was no difference in the names.
I think the naming convention is important but will depend on the consumers of the data, architects should be flexible in the naming and understand the db usages.
just my 2p.
Yes, I also think the architects need to be a bit more flexible.
As usual it’s a compromise situation. I’ve found that prepending “vw” to views, and no prefix on table names, is the best compromise.
exactly – why pollute the names of your tables when you’re not going to be working with views 90% of the time.
I’d rather prefix views with “v”. It’s shorter, and I use them very rarely anyway.
I am speaking from ignorance here: why does it matter if it’s a table or a view? Shouldn’t a good schema remove any need to know?
I suppose so, except that since I rarely see that model being used I tend to advocate the use of a prefix. I see a LOT of vendor code that could benefit from either: using a schema or using a prefix.
I like prefixes myself. I also love this site layout and design. So I was enthusiastic about reading the article. However, the word “Karen” kept appearing over and over, which was a big distraction to the point of the article.
Hmmmm….I guess it is because of the sessions we have been doing and we debate this topic. I didn’t think I used her name too often, but you could be right. I’ll take better care in the future when writing.
WTH? My name is fine…
It sure is.. “caring”….. oops! I meant “Karen” LOL
I prefer using suffix… DataCurrent_tbl… that way I still can use accelerator keys because everything is not filed under “t”. B-)
I think the argument revolves around your line of thought. Are you looking at your database conventions as ideal or practical. Ideally your names wouldn’t be prefixed so that in the event you need/want to swap a Table for a View you can do a quick rename of the table and creation of a view with the former name. Bing Bang Boom! No need to update other objects to use the new name (e.g. Views, Triggers, Stored Procedures, User Defined Functions). Realistically though you probably won’t have to do this for the majority of the databases you create in your life and knowing at a glance if you’re working with a table or a view is probably more valuable in the long run.
I prefer to keep prefixes out of my design with the exception of using v in front of view names. I do this because:
1) They’re similar in purpose (meaning I query them)
2) They’re the most frequently referenced objects in my code
3) I’ll often use the same name for the denormalized view version of the table (e.g., Customer -> vCustomer)
I don’t run into these issues with other objects, so I don’t worry about prefixes anywhere else.
My rebuttal is live. I think we aren’t really that far apart. Except for your insistence of prefixing every object in the database to be consistent.
http://blog.infoadvisors.com/index.php/2012/10/10/metadata-stuffing-why-i-hate-tbl_-for-table-names/
I came here to explain the exact use case that Gail described significantly better than I could. Thank you Gail, and Tom, that is the exact scenario I explain in classes as to why you should never use type prefixes for object names. They change, and then the names are misleading, because it’s too difficult to properly refactor the database. If you understand true relational theory you’ll know that fundamentally tables and views are in fact just virtual representations anyway.
I think “never” is too strong of a word. My “splendid truth” (i need to blog about that) is cost, benefit and risk is how we should decide.
I have to support shops that have only native DB2 tools. Those tools are still mired in the mainframe text only world. They still truncate names for presentation purposes. You have to write a query of the system catalog to get meta data about an object…its so flipping painful.
I make lots of concessions to stuffing names for those guys. But the “right” solution would be to get some real tools to work with a modern database.
Allen, are you suggesting the the use of prefixes makes refactoring difficult? To me, refactoring is the process that is employed when you need to make the changes that Gail has described. The use of prefixes are not going to be the deciding factor for whether or not a refactoring effort will be successful.
I am quite aware that tables and views are logical representations of data pages physically residing in one (or more) files on disk.
When a developer decides to join five views together without understanding that they are really joining 25 tables together, and performance is bad, then degrades over time, and I get called to fix things and I have to say “well, looks like you need to change your code, your design, or your hardware” and they get angry because I can’t find the Turbo button quick enough…that is when I wish it was easier for people to understand which logical representation they were using when building whatever solution they were tasked with.
Should it matter? Nope, not at all.
Does it matter? Yeah, almost always.
Yes, it matters, but the names don’t make a difference. So it takes you an extra 10 seconds to figure out that the object they’re working with is a ‘view’ versus a ‘table’. Is it really worth forcing a refactor when you realize that you need to convert a ‘table’ to a ‘view’? It’s not that hard to figure out. It’s cleaner without the prefixes – let the object name represent what the object is. As the DBA you can change the underlying structures as necessary to get the performance required, but it’s a lot harder if you have to change tblWidget to vwWidget as well.
Thanks Allen, I look forward to you convincing me further this weekend in Nashville.
Have you considered that *sometimes* people (that’s me a lot of times) are sent code for offline review before going onsite. I like to be able to pinpoint what would be the potential weak spots beforehand.
Just like Thomas said, 5 views can be expanded to 25 tables (not going in to specifics in to types of views vs. sql editions), and that is something I want to get straight in.
In that case you also have to consider that “tblCustomer” might actually be a view, because the guy who made the refactoring was too lazy to update the prefix.
If you’re doing offline review without proper CREATE scripts for the objects you’re reviewing, blindly trusting a prefix, you are risking the accuracy of your review.
Yep…refactoring…and failing to clean up properly afterwards is a headache for lots of reasons.
What about synonyms? Do you also suggest that they should be named synonym_Something or they should inherit the prefix of their original object name?
Personally I think that the Programmability layer should not make any assumption about the physical representation of the object being queried and just consider them as logical relational entities.
Wow! Thomas very nice well written article. I’ve read the comments thus far and this is really a great conversational topic. I myself do not use table prefixes, but follow the same concept that Josh Watkins stated ( I leave my tables without prefixes but I do prefix my views). For me it is a Pre-standing standard at my current employer and one I’m accustomed to for various reasons; if it wasn’t a standard though I’m not sure I would include a prefix anyways. I think the last paragraph you made sum’s up my stance on it, “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”. Just because I might not go the route you take or others take I’m always open to hear other perspectives from fellow DBA’s and the community. It’s healthy to have other viewpoints. Thanks for the post and for what it’s worth to Karen I don’t mind her name being mentioned. Have a good one.
Thanks Chris!
Just out of curiosity why haven’t I noticed anyone mention the use of prefixes on stored procedures and functions? To the best of my knowledge most people agree that you should use prefixes of one type or another for these types of objects. For that matter constraints and keys usually have some type of prefix. Is there really that big a difference between usp_ and vw_? Or pk_ and tbl_? I understand the refactoring argument might be stronger for tables and views than for other types of objects but I think being consistent is also as important if not more so.
Kenneth,
Yes, that is part of the debate as well.
I’m not a fan of the prefix “tbl” because it means three extra letters I need to type in. I use SQL Prompt and I want my table names to be selective so I don’t have to type more than I have to. Then again I’m lazy 🙂
I completely agree with Gail. I never, never, never prefix object names, because they can change. A view named tblSomething is not fun.
However, even before SSMS had that useful tooltip (it was there in 2008R2 BTW), you had ALT+F1 to run sp_help.
I’m with Karen here: you need to try harder. And not as hard as may think.
As long as you are using the same naming conventions consistently across your various databases and server, then I think its really just a matter of preference.
Advocating for use of prefixes: As developers we often forget that we are not the only ones who will be looking at our code. Yes it is a pain in the neck to change object types with prefixing (and let’s not start on the complexity of changing when you prefix datatypes).
But don’t forget about our downstream. Eventually (if we’re doing it right) we aren’t the ones supporting the products by our code. If we’ve done our job, we’ve put in enough error handling testing etc etc. that our code can now be called a “Product” and can be supported by the ever so important Generalist.
A generalist is an invaluable asset and if we do our jobs, the first line support that they can provide will generate praise and satisfaction from our customers. Little things like prefixes will enable a generalist to understand where data might be and will help them understand a little bit of the design and data flows. This will go a very long way in diagnosing a perfomance issue, invalid data displayed in the app, a runaway query etc. Remember, many times our generalists are on customer sites and don’t have access to the same tools as us. A little (sometimes alot) of extra effort on our part is very big difference in the customer’s experience.
We can enable our downstream recipients by carrying some (most) of the the burden. During development and during design we should always be thinking about what it’s going to take to support what we build.
Although I still agree with your original statement. I like to be able to read offline code and just know from the objects name what it is: tbl for table, vw for views, ufn for functions, usp for sprocs, etc.
Forgive me for being a sucker for standardization. 🙂
I’m in the “don’t use them camp”. If you want a simple example, ask yourself if a proc that’s sp_ is a system stored proc or an extended stored proc. Think it’s a system stored proc? Are all the extended stored procs xp_ prefixed? Go check it out. SQL Server has sp_ in both lists and xp_ in both lists.
They clearly had to change the implementation but weren’t game to change the names.
Well now, I’m not sure this post aged well. Then again, maybe it did.
The thoughts contained in this post seem…old. I wouldn’t use tbl_, or vw_, or any other prefixes these days. Wayne Snyder was the one, years ago, that helped me to understand that the name of the object should offer the consumer a clear understanding.
We spent a lot of energy discussing prefixes in this post, and in the comments, but I don’t see anyone talking about the most important thing here: the data.
Greg, you know as well as I do, when it comes to working with data for ML purposes, the attribute names are often misleading. For example, an attribute of IsCustomer…is that a customer when the report was run, or was that row updated later, after entered into the CMS?
You know, I should write a new post at this point.
Thanks for stopping by Greg, and for getting some words flowing from my fingers again. It’s been a while.
Cheers!