"ID" or "Id"

Question

"ID" or "Id"

John Corkett

SSCrazy

Points: 2251
More actions
July 8, 2015 at 7:31 am

#305554

Hi,
Please can anyone point me to any conventions for using ID or Id in column names, e.g. TableColumnID and TableColumnId in T-SQL?
There is a debate in programming circles about this and microsoft publish guidance for .NET (https://msdn.microsoft.com/en-us/library/ms229043.aspx). However, I was hoping to find some documented convention for T-SQL.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

John Corkett (7/8/2015)
Hi,
Please can anyone point me to any conventions for using ID or Id as column names in T-SQL?
There is a debate in programming circles about this and microsoft publish guidance for .NET (https://msdn.microsoft.com/en-us/library/ms229043.aspx). However, I was hoping to find some documented convention for T-SQL.
Thanks

There is no right or wrong answer to this. And AFAIK there is no documented convention for any naming conventions across the board. I prefer "ID" because depending on the font, "Id" can be confused with "ld". Just look at the title of this thread to see a good example. 😉 As with everything when it comes to naming conventions, the most important thing is to be consistent. There is my 2¢.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

If the name of the column is simply "ID", regardless of case, that's probably not a good practice.

It means nothing. Would table_name_ID make more sense?

As for naming conventions in a DB, you will get as many opinions as there are molecules of air!

Come up with a convention that works for your organization, and stick to it

I tend to stick to English words and avoid abbreviations or acronyms.

I personally dislike both PascalCase and camelCase. To me, either of these are difficult to read. I normally use underscores.

THisIsMyTableName

thisIsMyTableName

This_Is_My_Table_Name

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

I was remiss in mentioning as Michael did that if the entire name is "ID" or "Id" then neither is appropriate. That kind of thing means you have to change the name of that column to give it meaning when it is a foreign key. Column names changing between tables is a huge pet peeve of mine as it is incredibly confusing to work with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Y.B. SSChampion Points: 11642 More actions · Answer 4

Where I work "ID" was used everywhere if it was the primary key. Then foreign keys would become AccountID for example. Programmers claimed it was easier for them to tell if the ID's "belonged" to that table or another one (TableName + ID).

That being said I completely disagree and I am slowly convincing them to change the convention here on new projects. 😉

SELECT quote FROM brain WHERE original = 1
0 rows returned

Neil Burton SSC-Insane Points: 22919 More actions · Answer 5

We've got a database that has all the primary keys as ID and all foreign keys as Table+ID and it's horrible to work with. There's two closely related tables that both have SiteID columns in but both are different values. Nine times out of ten, if there's a problem with a report it's because those two columns have been joined directly.

With regards to cases, we use PascalCase because SSRS will automatically put the spaces in at the capitals. I actually prefer to use underscores but I don't mind not because I can see the benefits of PascalCase.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Sean Lange (7/8/2015)
John Corkett (7/8/2015)
Hi,
Please can anyone point me to any conventions for using ID or Id as column names in T-SQL?
There is a debate in programming circles about this and microsoft publish guidance for .NET (https://msdn.microsoft.com/en-us/library/ms229043.aspx). However, I was hoping to find some documented convention for T-SQL.
Thanks
There is no right or wrong answer to this. And AFAIK there is no documented convention for any naming conventions across the board. I prefer "ID" because depending on the font, "Id" can be confused with "ld". Just look at the title of this thread to see a good example. 😉 As with everything when it comes to naming conventions, the most important thing is to be consistent. There is my 2¢.

Agreed - no right or wrong answer unless the documented naming convention for your database says one thing and you don't follow the specified naming convention.

If there is no naming convention specified, then create one and follow it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

yb751 (7/8/2015)
Where I work "ID" was used everywhere if it was the primary key. Then foreign keys would become AccountID for example. Programmers claimed it was easier for them to tell if the ID's "belonged" to that table or another one (TableName + ID).
That being said I completely disagree and I am slowly convincing them to change the convention here on new projects. 😉

Heh... that being said, beware the page-split if you make such changes. "Here there be dragons".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Y.B. SSChampion Points: 11642 More actions · Answer 8

Jeff Moden (7/8/2015)
yb751 (7/8/2015)
Where I work "ID" was used everywhere if it was the primary key. Then foreign keys would become AccountID for example. Programmers claimed it was easier for them to tell if the ID's "belonged" to that table or another one (TableName + ID).
That being said I completely disagree and I am slowly convincing them to change the convention here on new projects. 😉
Heh... that being said, beware the page-split if you make such changes. "Here there be dragons".

Yeah, I'm not making changes to any existing DB's. 😀

SELECT quote FROM brain WHERE original = 1
0 rows returned

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 9

It doesn't matter a lick.

I'd just strongly argue for consistency to help promote clarity.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning