Is adding the ‘tbl’ prefix to table names really a problem?

Question

I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’.

On the internet I found some blogs saying it adds nothing to documentation, and also that “it takes longer to read it”.

Questions and considerations

• It this really a problem? Because I’m prefixing tables with ‘tbl’ since my first dba job (the senior DBA told me to do that for organization).
• Is this something that I need to get rid of? I made some tests, copying a really big table and giving it the ‘tbl’ prefix, while keeping the other one without it, and I didn’t notice any performance issue.

Answer 1

I once had a table and it was shiny and beautiful. It held all the financial transactions for an organization. And then we started loading data into it.

In the current month, they can state and restate values as often as they want. In the final 10 days of a month, they'd restate numbers -> run ETL processing -> review reports several times a day. Once the month is complete, then the books are sealed and they can't modify values.

It's amazing how much financial data a financial services firm generates... Something we didn't realize with our test data set was the volume of data was going to make their month end procedures untenable. It took an increasingly long time to delete out the "current month's data" prior to replacing it with the new trial run.

We had to do something to make it faster for processing without breaking the uncatalogued list of "who knows what" that all depends on the MonthlyAllocation table. I decided to play magician and whip the tablecloth out from underneath them. I went old-school and used a Partitioned View. The data already had an IsComplete flag so I made two tables - each with contrary check constraints: MonthlyAllocationComplete, MonthlyAllocationInComplete

I then created the partitioned view with the same name as the original table: MonthlyAllocation. No process was any the wiser about the physical change we made to the database. No reports broke, none of the analysts with direct access reported any issues with that "table" before or after.

Cool story bro, but where you going?

What if they had a naming convention there, tbl_MonthlyAllocation? Now what? Do we spend lots of man hours going through every ETL, every report, every ad-hoc spreadsheet in the organization and updating them to use vw_MonthlyAllocation? And then of course all those changes go through the Change Board and that's always a quick and painless process.

You boss might ask: What's the reward to the company for all that work again?

The other option becomes we leave this view named as tbl_ and not spend all that time testing, updating and deploying code. Which becomes an amusing anecdote you explain to all the new hires, and those with short attention spans, that have to work with the database as to why you are inconsistent with the naming of objects

Or you don't double encode objects with redundant metadata. The database will happily tell you what is a table, what is a view, what is a table valued function, etc.

Naming conventions are good, just don't paint yourself into a corner with them.

Answer 2

Brent here (the guy you're referring to in the question).

The reason I tell you not to add tbl to the front of your table names is the same reason I'd say not to add child to the front of your child's name. You don't call them childJohn and childJane. Not only does it not add any value, they may not be a child later in life - and your objects may later become views.

Answer 3

This is a very subjective argument, but here is my take: the tbl prefix is useless.

How many scenarios are you looking at code and you can't tell if something's a table or something else?

What value does tbl add except that when you look at a list of tables in Object Explorer, you have to do more work to find the one(s) you're looking for?

Some people say they want it to be clear in their code when they're dealing with a table or a view. Why? And if this is important, why not only name views in a special way? In most cases, they act just like tables, so I see little value in distinguishing.

Answer 4

It is a terrible practice.

tbl
tbl_
Table_
t_

... seen them all in production.

One of the products I'm currently working with has half of the tables named tbl_whatever, and the other half named "normally" - They've obviously got developers that are working to different standards. Another one of their bad habits is prefixing column names that are foreign keys with fk, followed by the table name, fk then the column name, giving such awful column names as fktbl_AlarmsfkAlarmsID. That naming convention is just a logical extension of the whole tbl_% mantra, and you can see just how ridiculous it gets!

I nearly forgot about the other database that makes me cry on a daily basis. Datatypes prefixing column names... dtPaymentDate, because the name really needed the dt prefix?`

Answer 5

comDon't verListen prepTo adjThose adjOther nouPeople. proYou auxShould advAlways verUse nouPrefixes prepWith proYour adjTable nouNames. proI auxHave advEven verStarted gerUsing proThem prepIn adjNormal nouWriting.

comSee advHow adjEffective proIt verIs? nouPeople auxCan verUnderstand proYour nouWriting adjBetter!

Answer 6

Using a prefix like this is known as Hungarian Notation. It's premise is simple: you can determine what something is by how it's named. This is particularly common in programming languages, especially when developers write monolithic functions that span dozens of pages, either by lack of skill or lack of language features. It's a mnemonic aid that helps developers keep data types straight.

Generally speaking, this style of naming is likely to be used in really old code, or by developers that are either just learning (and happened to learn this habit), or have been doing it as long as they can remember. In my experience, I've observed that it's relatively rare to see Hungarian notation spring up spontaneously with inexperienced developers if they're not introduced to it by a programming course or tutorial; they're more likely to use variable names like i or x or acct.

Besides painting yourself into a corner, this is really just filler. The SQL syntax is precise enough that a developer should always know if they're if they're talking about a field, record, or data set (which may be a table, view, etc). While it might make a great teaching aid, this syntax usually shouldn't exist in production databases. It can harm legibility, and make it more difficult to find the table you're looking for, especially if several alternative naming themes pop up, like tbl vs. table vs. tab, etc.

The database doesn't really care what you call your tables, fields, etc. They're just bytes of data that are arranged in a particular order by their human operators or scripts. However, the humans that have to maintain this data will appreciate meaningful names, like "user", "order" and "account". It's also more practical if you're using SQL queries, like "show table like 'a%'". Using prefixes and suffixes can unnecessarily complicate otherwise trivial maintainence.

Answer 7

I've read a few blog posts like this or this (there's quite a few) after I inherited my first SQL Server Instance and noticed many objects where prefixed, I wanted to start developing new ones with a less verbose approach and singular naming convention (lot easier for me to [and possibly other developers] work on Object Relational Mapping).

One of the most widely used prefixes was Tbl or tbl, but then I had TBL and tbl_ and even *TableName*_Tbl

On trying to query and work from Visual Studio this is just hell, I wouldn’t matter having a whole set of table prefixed tbl but please just keep it that way for the whole Database, at least.

So in the end I would definitely say is a matter of personal preference but it also has a lot to do with consistency and if you go down that road, a lot of people will be happy at least you keep it the same along your development.

...On the other hand I just wanted to say, if you take other approach and go for non-prefix, singular-name table you will make a developer happy in the future, and what’s more important than happiness?

Answer 8

Yes, adding a prefix that denotes the type of the object is a problem, and unnecessary. Because,

1. Sometimes objects begin life as something but will end up becoming something else. (e.g. table tblXxx was split into tblXxxY and tblXxxZ for some reason and replaced with a view that joins the two new tables. Now you have a view called tblXxx).
2. When you type tbl in the editor, its AutoComplete feature hangs for 45 seconds and then shows you a list of 4000 entries.

But...

I'm going to play devil's advocate and say something others have outright advised against. There are some rare cases a suffix/prefix could be useful, and here's an example from the organization I work for.

We have an entity based ERP system, where the business logic is written in Oracle PL/SQL. It's nearly two decades old, yet a very stable system (This is not a legacy system. We are continuously developing it).

The system is entity based, and each entity associates a table, multiple views, multiple PL/SQL packages, and a host of other database objects.

Now, we want the objects belonging to the same entity to have the same name but yet to be distinguishable of it's purpose and type. So, if we have two entities that are named CustomerOrder and CustomerInvoice, we'll have following objects:

1. Tables to store data [TAB suffix]:
   • CUSTOMER_ORDER_TAB
   • CUSTOMER_INVOICE_TAB.
2. Views that are used by clients [No suffix]:
   • CUSTOMER_ORDER
   • CUSTOMER_INVOICE.
3. Interface for basic operations; (PL/SQL packages) [API suffix]:
   • CUSTOMER_ORDER_API
   • CUSTOMER_INVOICE_API.
4. Report generators; (PL/SQL packages) [RPI suffix]:
   • CUSTOMER_ORDER_RPI
   • CUSTOMER_INVOICE_RPI.
5. Indexes for primary keys [PK suffix]:
   • CUSTOMER_ORDER_PK
   • CUSTOMER_INVOICE_PK.
6. Secondary indexes [IX suffix]:
   • CUSTOMER_ORDER_XXX_IX
   • CUSTOMER_INVOICE_XXX_IX (where XXX describes the usage of the index).
7. ... and so on.

This is indeed a form of Apps Hungarian (note that same type of objects can have different suffixes depending on the purpose). But, with a suffix instead of a prefix. I can't tell you enough how this system is so easy to read. IntelliSense actually works because instead of typing TBL and getting 4000 results, I can type Customer and get all the objects belonging to entities named Customer*.

So, here I have showed you how metadata can be useful. The purpose is to have a related set of database objects identifiable by a single name, yet differentiate them based on their purpose.

Having said that, if you don't have this kind of system, there is no use of either prefixing or suffixing the type of object.

Note that we haven't used suffixes like _table, _package, or _view (i.e. the type of the object). For example, both (3) and (4) are PL/SQL packages, yet use a different suffix. It's the same for (5) and (6), both of which are indexes. So the suffix is based on purpose rather than the type.

Answer 9

tl;dr It (very likely) adds redundant information, leading to cognitive overhead, and should therefore be removed.

Context is a wonderful thing, especially in computer systems. Out of context you couldn't possibly tell whether something called users is a table, a view, a stored procedure or something else entirely. Legacy (or just badly written) systems and languages often make it difficult to work out the context while reading the code. For example, in Bash, you can't tell what function users does without at least reading the contents of the function. In Java, Map<Uid,UserDetails> users() is pretty transparent, and you can dig down to the details easily.

Taking this argument to SQL tables, when would it be useful for the consumers of users to know whether it's a table or a view? In other words, is a tbl prefix going to tell any of the consumers something they don't know and need to know? Hopefully the vast majority of the consumers will be writing DML and DQL queries against it. For them it should be just another data source, and whether it's a view or a table should be just about as relevant as whether it's partitioned, stored on HDD or SSD, or any other technical detail. The DBA of course needs to know these details to run some rare DDL/DCL queries, but it seems counter-productive to pollute the namespace for the sake of the minority who really knows how to navigate the schema and get all the technical details.

Answer 10

One of the features of a relational database management system is that it separates the logical presentation of information to clients from the physical storage. The former is columns in a rowset. The latter is as files on a storage volume. It is the DBMS's job to map one to the other. It is a concern only to the DBA or sysadmin which hardware is supporting the information need. The consumer need not, indeed should not, be aware of those concerns.

Neither should the client be concerned about how a rowset is constructed. A base table, a view or a function are all, in this respect, identical. Each simply produces a set of rows and columns which the client consumes. Even a simple scalar can be considered a one-row, one-column table. The row / column model is the contract between the client and the server.

By prefixing the artefacts' names with their implementation type this separation of concerns is broken. The client is now aware of and dependent on the internals of the server. Change in the server's coding may require client re-work.

Answer 11

This has been covered many times, but probably most famously by Joe Celko, an American SQL and relational database expert. I've personally been on the receiving end of one of his rants online (felt honored), and he talks about these prefixes as "tibbling", or more accurately described as "skeuomorphism".

The general idea is that these prefixes are a coding practice from long ago (probably due to naming limitations, like an 8 byte limit to object names), passed down generations of programmers for no apparently useful reason, other than it is just "the way it is done".

Companies, bloggers and programmers pass on information with their own style of coding, and some styles might be a bit more "Frankenstein" than others. A company might have a "house style", and the programmer is forced to learn this practice.

Over time things stick, even if the reason they were used originally is now deprecated. "Tibbling" is one of the most well known examples of this in relational database management.

To round up: it doesn't add any value, it adds exactly 4 byte of storage space on each table name for no reason other than because it's there. It offers nothing to modern SQL Server systems, but if it makes you feel better having it there, go ahead and use it.

Answer 12

There are lots of answers here that I agree with that tell you that this isn't a very valuable naming convention. For those people who aren't convinced by the other answers I'm going to try to demonstrate what many other answers are saying.

---

SELECT
  bar
  , fizz
  , buzz
FROM dbo.foo

In the above statement I am selecting three columns out of something named dbo.foo. One of the core complaints of the prefixers is that they don't know if dbo.foo is a table or a view. Of course that is one of the strengths of a view as an abstraction, but I digress. They say we should prefix the object like this dbo.tblFoo. Now they can see that the object is a table (probably) in the above query. However if we write the functional equivalent of that goal it would look like this.

SELECT
  bar
  , fizz
  , buzz
FROM dbo.Foo --table

I suspect that comment looks less than useful even though that is what prefixers are effectively arguing for. To highlight the useless noise this meta data comment injects consider a more complicated query.

SELECT
  qc.occurances
  , i.employeeId
  , q.questionText
FROM dbo.questionCount /*Indexed view*/ qc WITH (NOEXPAND)
  INNER JOIN dbo.interviewer /*partitioned view*/ i ON i.employeeId = qc.interviewerId
  INNER JOIN dbo.question /*table*/ q ON q.id = qc.questionId
WHERE
  EXISTS (
           SELECT 
             1 
           FROM dbo.currentEmployees /*view*/ ce 
             INNER JOIN dbo.questionsAsked /*table*/ qa ON qa.candidateId = ce.candidateId
           WHERE
             ce.employeeId = i.employeeId
             AND
             qa.questionId = q.id
         )
  AND
  qc.occurances > 5;

Do the extra comments make that query any easier to reason about or do they just add extra noise? In my opinion they just add extra noise and add zero value. That is what the anti-prefixers are trying to say. Furthermore using prefixes is actually worse than those comments because the cost of updating a comment is much lower than updating the name of a prefixed table if your data model needs to be adapted. Since these prefixes have a cost and they don't impart valuable information they should be avoided.

Another advantage of prefixes that some people cite is it can impart some sort of grouping or ordering in your development environment. Since we spend a large amount of time editing code this can be a seductive argument for some. However in my experience modern development environments provide equivalent or superior options that don't litter your code with useless meta data and limit your flexibility.

Answer 13

My suggestion would be that you stop doing this immediately. If this makes you uncomfortable going forward, add "_tbl" to the end of your table names. Of course for reasons already mentioned, there's no need to do that either. The person who originally told you to do that gave you some bad advice. It may have been bad "this makes sense in terms of our organization's badly set up system" advice, but in that case it was his job to fix it. Still bad advice.

Answer 14

Is “Do not prefix your tables with tbl” really a problem?

Regarding the system? No. Regarding other people? Maybe. You can generate lotsa hate.

I personally do not prefix tables but do so on other objects like views, stored procedures, functions, etc.

Answer 15

I would have to say please stop doing this. It has happened in the past, but by continuing to do this you encourage new people who come into your environment to think it's a local convention and continue. But they won't just continue, they will do it slightly differently

When trawling a db for a specific item, and I'm not the only one who will open object explorer and just think I'll scroll to it, you know it's alphabetical. That is until prefixes start muddying the waters, and I have tblname, tbl_name, tbname, t_name and a thousand other variations, it becomes really hard to find that table which you know is already a table

Likewise prefixing everything vw_ or sp_, someone will come in and you'll get SPname, spname, s_p_name. Remove all the prefixes, the software knows what the item is, if you need really to know then use a suffix instead. NameOfMyView_v, NameOfMyProc_sp. much easier to search visually

But what if you're trawling through a long stored proc and can't easily tell what is a view a proc or a table? Well chances are that these will be aliased anyway, and even if not the suffix comes to your rescue

Don't be afraid of changing what you do, and if you walk into an environment where the naming conventions are already shot to heck don't be afraid to implement your own, and start to change things for the better. By matching the existing chaos there's no chance of making it less confusing, only making it moreso

Answer 16

Each side has its advantages and its disadvantages. It’s just up to your team’s or company’s lead to decide on conventions to follow.

We, for one, use the tbl convention. The main reason is that we know in scripts what we can and shouldn’t do. We have varied projects and people who jump in not knowing the schema by heart. Our tables have logical names, so it’s easy to find your way while writing scripts. While doing so, when we find a table, we know immediately (through IntelliSense) that it’s a table. One can argue that adding the prefix does not add much context. However, removing it means there’s no context.

The only real advantage of not using the prefix is interchangeability. However, I would argue this is a potentially dangerous situation. Sure, your scripts and queries won’t break, but maybe you start relying on that fact too much, while some things just don’t work with views or are ill-advised.

In the end it really just boils down to what your team prefers and how it writes code/scripts.

Answer 17

Consider dbo.User vs dbo.tUser. Now imagine you want to find all the places in code that this table is used. Which version makes this easier (or even possible?) The word "user" is likely to have a lot of false positives, as variable names, in comments, etc.

That's something I haven't seen in any of the other answers, but I'm a developer-cum-DBA, so maybe others haven't had to work on legacy code, where queries can be embedded in the application, and not all queries fully qualify references with the schema, and things like that. (Even if everything is fully-qualified, you need to find dbo.User and [dbo].[User] and dbo.[User] and so on). Or database versions where the "dependency information" gets stale and is inaccurate (e.g. older versions of MS-SQL)

So, on some projects I've worked on, that are mixed like that, I mandated a t or v prefix (tbl_ gets silly), simply to make it a more selective search term.

In later projects, with things like SQL Server Data Tools (hello, Find All References), and database access exclusively through an ORM layer, there is no utility, because finding all usages of the table is trivial (as is renaming it!)

Answer 18

If I have to think of an advantage of having prefix 'tbl', it is that in current SSMS, with intellisense, I can just type

select * from tbl

and then find the table name I need (assuming I have no idea about exact table name). This is one-step work. Of course, we can always find out the table name through additional steps, but I'd say that with 'tbl' prefix, this is the ONE step work, and that's why I always prefer a prefix (not necessarily 'tbl') in my own homework project.

Edit: (After so many down votes, I still think it is worthwhile pointing out some niche advantages of giving a specific prefix to a specific category of objects in sql server). It seems no one complains having a prefix for stored procedure/functions/views, but there is always argument about doing so with tables. (To me, in real world I cannot care less whether we give a prefix or not to tables).

I remember in sql server 2005 days, there was a requirement to find what tables are used in which stored procedures / views, with table names prefixed, it was such an easy job with Regular Expression/C#. (Yes, I know there were other ways, no argument here).

My career grows with reading lots of "best practices" papers / articles, but I also have seen enough exceptions to almost every "best practice" one way or another in different scenarios. Thus, I always tell myself and my fellow DBAs, make the judgement according to the business requirements, "best practice" has its place for sure, but it can only be considered in the context of our own environment.