When naming columns in a SQL Server table, are there any names I should avoid using?

Question

I remember when I was working with PHP several years back I could blow up my application by naming a MySQL column 'desc' or any other term that was used as an operator.

So, in general are there names I should avoid giving my table columns?

Answer 1

As long as you surround every column name with '[' and ']', it really doesn't matter what you use. Even a space works (try it: [ ]).

Edit: If you can't use '[' and ']' in every case, check the documentation for characters that are not allowable as well as keywords that are intrinsic to the system; those would be out of bounds. Off the top of my head, the characters allowed (for SqlServer) for an identifier are: a-z, A-Z, 0-9, _, $, #.

Answer 2

in general don't start with a number, don't use spaces, don't use reserved words and don't use non alphanumeric characters

however if you really want to you can still do it but you need to surround it with brackets

this will fail

create table 1abc (id int)

this will not fail

create table [1abc] (id int)

but now you need to use [] all the time, I would avoid names as the ones I mentioned above

Answer 3

You should avoid any reserved SQL keywords (ex. SELECT) and from a best practices should avoid spaces.

Answer 4

Yes, and no.

• Yes, because it's annoying and confusing to have names that match keywords, and that you have to escape in funny ways (when you're not consistently escaping)
• and No, because it's possible to have any sequence of characters as an identifier, if you escape it properly :)

Use [square brackets] or "double quotes" to escape multi-word identifiers or keywords, or even names that have backslashes or any other slightly odd character, if you must.

Answer 5

Strictly speaking, there's nothing you can't name your columns. However, it will make your life easier if you avoid names with spaces, SQL reserved words, and reserved words in the language you're programming in.

Answer 6

You can use pretty much anything as long as you surround it with square brackets:

SELECT [value], [select], [insert] FROM SomeTable

I however like to avoid doing this, partly because typing square brackets everywhere is anoying and partyly because I dont generally find that column names like 'value' particularly descriptive! :-)

Just stay away from SQL keywords and anything which contains something other than letters and you shouldn't need to use those pesky square brackets.

Answer 7

You can surround a word in square brackets [] and basically use anything you'd like.

I prefer not to use the brackets, and in order to do so you just have to avoid reserved words.

MS SQL Server 2008 has these reserved words

Answer 8

Check the list of reserved keywords as indicated in other answers.

Also avoid using the "quoting" using quotes or square brackets for the sake of having a space or other special character in the object name. The reason is that when quoted the object name becomes case sensitive in some database engines (not sure about MSSQL though)

Some teams use the prefix for database objects (tables, views, columns) like T_PERSON, V_PERSON, C_NAME etc. I personally do not like this convention, but it does help avoiding keyword issues.

Answer 9

Beware of using square brackets on updates, I had a problem using the following query:

UPDATE logs SET locked=1 WHERE [id] IN (SELECT [id] FROM ids)

This caused all records to be updated, however, this appears to work fine:

UPDATE logs SET locked=1 WHERE id IN (SELECT [id] FROM ids)

Note that this problem appears specific to updates, as the following returns only the rows expected (not all rows):

SELECT * FROM logs WHERE [id] IN (SELECT [id] FROM ids)

This was using MSDE 2000 SP3 and connecting to the database using MS SQL (2000) Query Analyzer V 8.00.194

Very odd, possibly related to this Knowledgebase bug http://support.microsoft.com/kb/140215

In the end I just removed all the unnecessary square brackets.