According to a popular SO post is it considered a bad practice to prefix table names. At my company every column is prefixed by a table name. This is difficult for me to read. I'm not sure the reason, but this naming is actually the company standard. I can't stand the naming convention, but I have no documentation to back up my reasoning.

All I know is that reading AdventureWorks is much simpler. In this our company DB you will see a table, Person and it might have column name:

or maybe even
Person_Person_First_Name (don't ask me why you see person 2x)

Why is it considered a bad practice to pre-fix column names? Are underscores considered evil in SQL as well?

Note: I own Pro SQL Server 2008 - Relation Database design and implementation. References to that book are welcome.

migrated from stackoverflow.com Jun 21 '11 at 9:01

This question came from our site for professional and enthusiast programmers.

  • 2
    Looks like who made these rules wasn't aware of the aliasing function. – ba__friend Jun 20 '11 at 19:16
  • @Daniel Pryden - I used poor wording. Question updated. – P.Brian.Mackey Jun 20 '11 at 19:16
  • A similar kind of post here stackoverflow.com/questions/465136/… – Rahul Jun 20 '11 at 19:26
  • @ba__friend - Can you give me some detail on that comment? – P.Brian.Mackey Jun 20 '11 at 19:29
  • 1
    The essential word you have used is Standard. If you change a standard practice, you have inconsistency. Do you honestly feel that any change from this standard practice, is worth that resulting inconsistency? Is the status-quo really worse than that? – Dems Jun 20 '11 at 19:33

11 Answers 11


Underscores are not evil just harder to type. What is bad is changing standards midstream without fixing all the existing objects. Now you have personId, Person_id, etc. and can't remember which table uses the underscores or not. Consistency in naming (even if you personally don't like the names) helps make it easier to code.

Personally the only place I feel the need to use the tablename in a column is on the ID column (the use of just ID is an antipattern in database design as anyone who has done extensive reporting queries can tell you. It's so much fun to rename 12 columns in your query every time you write a report.) That also makes it easier to immediately know the FKs in other tables as they have the same name.

However, in a mature database, it is more work than it is worth to change an existing standard. Just accept that is the standard and move on, there are far more critical things that need to be fixed first.

  • 4
    True, but a database with no consistent naming standard is far harder to query than one with a poor standard that is consistently applied. – HLGEM Jun 20 '11 at 19:36
  • 2
    I partially agree. If the database is HUGE then keep the standard. But I spend my time reading code more than writing it and the table name prefixes seems like more noise to sift through. If it were me and knew I could refactor it within a few days or a week I sure would. But a lot of time you don't have that luxury and have to keep coding prodution, prodution, prodution. – programmer May 21 '12 at 17:37
  • 3
    @jason, you may break more than you think doing that. Databases are often accessed by many things the application programmer is not aware of. Things like data imports from clients or other dbs and exports to clients and or datawarehouses, other applications, reports, etc. You can get used to reading any standard in a matter of hours, and refactoring away from an approved company standard without an agreement to move to a new standard would get you fired most places. Honestly, unless the database is in its infancy, it is better to use the existing standard whether you like it or not. – HLGEM May 21 '12 at 17:45
  • 7
    @jason, I'm a database person, we are widely known to have no sense of adventure! – HLGEM May 21 '12 at 20:29
  • 2
    Completely agree with TableName.Id being an antipattern. Have worked within and without that pattern long enough that I can confidentelly say mistakes/confusion occurs with TableName.Id that don't occur with TableName.TableNameId – AaronLS Oct 9 '13 at 19:12

An argument for the column name prefixing would be preventing name "collisions" when joining multiple tables AND when the query creator doesn't use aliases.

SELECT person.name, company.name FROM person JOIN company ON ...

SELECT * FROM person JOIN company ON ...

Both queries would have two "name" columns (name_1, name_2) without "telling" to which entity it belongs. And you can never be sure of the generated column names (will it be name_2 or name_3 or ...). If you use the table name prefixing, the column names would be person_name, company_name so you know each name to which entity it belongs, plus you know that the column names will remain constant (if you're getting them in Java using JDBC for example).

Both arguments can be ignored if you use aliasing, but I think most coding conventions enforced in companies come as a consequence of many (junior) programmers not following good practices. In this case, for example, using the wildcard on a SELECT statement can cause problems without the name prefixing.

As for the underscore in table and column names, I use it extensively because I use only lowercase in names plus underscore as a separator. Using only lowercase helps distinguish identifiers from SQL keywords (which I type in all uppercase):

SELECT person_name, COUNT(bought_product) FROM bought_products WHERE person_name LIKE 'A%'
  • 6
    I would have loved if all columns with the same information in different tables would have had exactly the same name and that whenever you use more then 1 table aliasing would be the enforced standard. Getting pretty tired of remembering to which table patid, patientid, pat_id, id_pat or ptatient_id maps. – Pieter B Sep 26 '12 at 14:37
  • 1
    I never write a production query without aliasing all the columns. It makes maintenace so much easier. Of course I write complicated reporting/export type queries with up to 10-20 joins and 30-50 columns and six months later it is hard to remember which table that column came from. – HLGEM Jan 6 '15 at 20:11

Adding those kind of prefixes to column names will make a table more difficult to evolve. As an example: if eventually you realize that you want/need to change the table name, you will have to modify your entire table structure (i.e., not only the name of the table, but the name of all its columns). This would also make more difficult updating the table's indexes and the code of clients querying it.


There are exceptions if used judiciously (as per Patrick Karcher's answer on your link) for common column names (normally only ID, sometimes Name) that would be ambiguous too often.

Another best practice is to always qualify columns and objects in your queries. So column prefixes become moot and clutter your code.

Compare these: which is easiest on the eye?

SELECT P.name, P.Salary FROM dbo.Person P

SELECT Person.Name, Person.Salary FROM dbo.Person Person

SELECT dbo.Person.name, dbo.Person.Salary FROM dbo.Person

SELECT Person.Person_name, Person.Person_Salary FROM dbo.Person Person
  • 3
    Definitely the first one... :) – ErikE Jun 20 '11 at 19:48
  • 3
    What about SELECT name, salary FROM dbo.Person? :P – cHao Jun 21 '11 at 3:13
  • 1
    @cHao: try creating a view WITH SCHEMABINDING on that... – gbn Jun 21 '11 at 4:11
  • @gbn: Works fine for me. CREATE VIEW [dbo].[vwMeritPercentage] with schemabinding AS SELECT [Performance Score] as dblMinScore, ISNULL(( SELECT TOP 1 [Performance Score] FROM dbo.Budget b WHERE b.[Performance Score] > budget.[Performance Score] ORDER BY [Performance Score] ), 100.00) as dblMaxScore, [Merit Minimum] as dblMinPercentage, [Merit Midpoint] as dblBudgetPercentage, [Merit Maximum] as dblMaxPercentage FROM dbo.Budget; – cHao Jun 21 '11 at 4:39
  • 1
    Relevant documentation (msdn.microsoft.com/en-us/library/ms187956(v=SQL.105).aspx): "When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced." Note that columns don't require dotted names (unless they are otherwise required to resolve ambiguities in the query)...only tables, views, and functions. – cHao Jun 21 '11 at 5:10

In general, there really don't seem to be any ubiquitous standards. The question you linked to has several high-voted answers with totally different conventions. Of course, everybody is going to defend their own standards, and it is far more important to keep consistent conventions in a project.

That said, prefixing column names seems to be overkill. You already know what table you're working with, and a situation with two columns from different tables having the same name can be easily resolved using table or column aliases.


In TSQL you can refer to fields in the form TableName.FieldName if you want to avoid ambiguity so adding table names to field names actually takes away from readability making it TableName.TableName_FieldName or similar. I think using underscores or not is more of a personal choice. I prefer CamelCase and i use _ when i want to add a suffix or similar e.g. TableName_Temp, but that's just me.


I once worked on a system where we decided to use short codes to prefix the columns. The PK fields used the "full table name" as a prefix, and all other columns used 2-4 characters consistently as their prefix. Each column also used a data domain as its suffix. If done consistently, it can be very nice and clean. It's nonsense that a naming standard of one type or another implicates sloppy coding. The presence of a consistent standard is what's important. I've seen a number of databases that are inconsistent because there is no clear standard, and that more than anything else would indicate to me that there may be trouble in the data structures. If the designer of a database can't even consistently name objects and their children, why would that lead me to believe there is anything consistent or thoughtful about the underlying data model, relationships, constraints, integrity, etc?


Prefixing is bad practice because it causes the problem its designed to prevent. As you stated, its very hard to read prefixed columns. If you end up with duplicated column names in a query where you join two tables, you can resolve it their, or is a view, stored procedure or tabular user defined function that does it for you if you find yourself constantly joining particular tables.

As far as using underscored in a table name, that's a religious argument. In the end if increases visibility and makes things easier go for it. I generally would never have spaces or tables in a column or table name. However, I might make an exception for tables or views that were only used by a reporting package, or exported to a CSV file.


Many databases have strict limits on the number of characters in a column name (i.e. Oracle).

If you are working with a database that allows long column names, but you later decide that you want to migrate that structure on to another database system, the prefixes will increase the chances that your column names will be invalid.

Although you are working with SQL Server now, nobody can predict the future, and it's possible that your software may have to work on multiple databases in the future.


Consider writing a data dictionary (or "registry"). By that I mean a document containing all the data elements in your model: name, description, etc. It should be independent of the implementation of the model e.g. should not mention table names. How would you disambiguate names such as 'ID', 'Type' and 'Code'?

One approach is to follow the guidelines of the international standard ISO/IEC 11179 -- after all, why reinvent the wheel? The basic structure is:

[Object] [Qualifier] Property RepresentationTerm

with delimiters between the elements: SQL doesn't play nice with spaces in column names and underscore works well visually.

I have a feeling that someone in your organization was trying to follow these guidelines when they came up with element names such as Person_Person_First_Name.

The example I like to show is the UK Nation Health Service (NHS) data dictionary.

So I don't think the naming convention you have to work with sounds too bad.

On implementation e.g. in SQL, some folk like to omit the Object, Qualifier and Property sub-elements when the table name provides context e.g. person_first_name becomes first_name in the Person table etc. However, the rule of thumb that a data element shouldn't change its name simply due to it's location in the physical model seems a good one. Anyone who if thinks it isn't a good idea to follow this rule should be given the task of documenting all the name variations they've used :)

You will find a nice summary of ISO 11179 in the book Joe Celko's Programming Style, including evidence for preferring underscores as delimiters in column names.


Some find it easier in code to know what table the data came from, however, if you are talking about an object oriented system you should use the context of the name to know where it came from, in this case the table name.

Personally, table name prefixing is an indication that the developers are not very skilled and as you dig deeper you will find many other bad coding conventions and if I had to guess the app has too many tables, is buggy, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.