CODEWITHSUNDEEP
sqlsql-server-2005database-design
Joe
Joe

Reputation: 2107

Should underscores be used in column names?

Technically, the underscore character (_) can be used in column names. But is it good practice to use underscores in column names? It seems to make the name more readable but I'm concerned about technical issues which may arise from using them. Column names will not be prefixed with an underscore.

Upvotes: 27

Views: 36561

Answers (3)

Yevhen Nedashkivskiy
Yevhen Nedashkivskiy

Reputation: 137

If you check PostgreSQL documentation you may find that almost all the objects are named with Snake Case.

Moreover, a lot of system objects in MySQL, MS SQL Server, Oracle DB, and aforementioned PostgreSQL use Snake Case.

From my personal experience it is not a big deal to use underscores for objects naming.

But there is a caveat.

Underscore symbol is a placeholder for any symbol in SQL LIKE operator:

SELECT * FROM FileList WHERE Extention LIKE 'ex_'

It is a potential issue when there is a lot of dynamic SQL code, especially if we are talking about autogenerated object names. And such bugs are quite hard to find.

Personally I would rather avoid underscores in naming. But at the same time there is no need to rewrite all the existing code if this type of naming has already being used.

Forewarned is forearmed.

Upvotes: 2

Thomas Kejser
Thomas Kejser

Reputation: 1294

There are no direct technical issue with using an underscore in the name. In fact, I do it quite often and find it helpful. Ruby even auto generate underscores in column names and SQL Servers own system objects use underscores too.

In general, it is a good idea to have some naming convention that you stick to in the database, and if that includes underscores, no big deal.

Any character can be used in the name, if you put square brackets or quotes around the name when referring to it. I try to avoid spaces though, since it makes things harder to read.

There are a few things you want to avoid when coming up with a naming convention for SQL Server. They are:

  1. Don't prefix stored procedures with sp_ unless you are planning to make them system wide.
  2. Don't prefix columns with their data type (since you may want to change it).
  3. Avoid putting stuff in the sys schema (you can with hacking, but you shouldn't).
  4. Pretend your code is case sensitive, even when it isn't. You never know when you end up on a server that has tempdb set up to be case sensitive.
  5. When creating temp table, always specify collation for string types.

Upvotes: 14

m.edmondson
m.edmondson

Reputation: 30892

There is no problem with this, as long as it makes the column name clearer.

Upvotes: 8

Related Questions