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.

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.

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?`

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.

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!

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?

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.

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.

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.

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.

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.

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.

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.

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

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!)