Should I use a SQL Server keyword as a column name?

Question

I am designing a database and recently named a column in a table DayOfWeek, completely forgetting that DayOfWeek is a built-in function in SQL Server. Now I am deciding if I should just leave it as is and reference the column with square brackets [DayOfWeek] or change the column name to avoid any conflicts in the future. I am not too far into the project so changing it is not too hard. The debate in my head is that the column name of DayOfWeek just makes so much sense for its purpose, so I really want to use it... but it is a reserved word... and could cause pain in the future (especially if I always have to put square brackets around it when referencing the column).

What does everyone think?

Answer 1

I would change it - i've got a legacy table called user - it is a pain with the square brackets all the time. perhaps call it DayOfWeekName or DayOFWeekId

Josh

Answer 2

change it if is easy. But as a good practice I'd use square brackets everywhere. Once you get used to, is no more of a pain than putting space between words.

Answer 3

We have a table here called User. If at all possible, I would change it. Although Josh is correct, you can place square brackets to denote it is a table, that task gets old very quickly. Using reserved words as tables also makes it difficult for other developers. If someone doesn't know that is a reserved word, it can be difficult determining why a query doesn't work.

Answer 4

If you using any DB adapter / abstraction library to get data from database, then don't worry. Class will escape column names for you. If you writing SQL queries yourself, then it can make some troubles. You need to escape column names in your queries.

P.S. I remmember, few times I was in situation like you. But I was named column as "order" :-)

Answer 5

I would avoid also using reserved words, you can get very easly in trouble if you're not paying attention.

Answer 6

Jeff,

If you're not too far down the track to rename the column (relatively) painlessly then I'd recommend you change it. You've identified one probable-future-headache for the maintentance crew, and I guess it would be actually less costly (over time) to clean it up now, especially considering that renaming something isn't the hell-on-wheels since the advent of truly effective search and replace functionality in text-editors and IDE's.

The truly hard part of renaming it is gaining the understanding required to do the job safely. You are unique (being the author) in having that understanding. If you asked me (just for instance) to do the job, then it probably wouldn't be a cost effective business proposition.

So... +1 for fixing the sucker yourself... and +2 for not doing it again ;-)

Cheers. Keith.

Answer 7

I always make sure I never use a keyword as the start of any variable/object/function simply because you're never sure if your target language will like it. It can often generate wacky errors that take a while to track down. Even if syntax checking does pick it up, it means you've wasted more time than if it had been a different name like FurryKitten.

I would avoid DayOfWeek and opt for something which is a completely different, maybe Weekday or DayName. It just saves hassle.

Plus - square brackets just create headaches, and there are a lot of SQL developers out there who don't use the brackets - new developers will end up creating "non-bracketed" code out of habit for some time after they join the team. Uncommon conventions should be avoided if possible.