Use of [, ] around column names

[HFChristie]

I've got a general question about SQL syntax in SQL Server. Our DB has prolific use of the square brackets around column names in SQL statements, but isn't using them around all.

I'm having trouble accessing the help on my system (keeps running out of virtual memory), so I haven't been able to look through that as I had hoped.

Anyway, my question is this;
What is the difference between
SELECT [company], [company_id], [address] FROM ...

and
SELECT [company], company_id, [address] FROM ...

and
SELECT company, company_id, address FROM ...

where both are from the same table, and all three columns are valid for the table.

Thanks

 

[jester777]

The use of square brackets allows you to use reserved words or identifiers with invalid characters in as names for columns, databases, views etc.

Eg hyphen (-) is invalid in a name

create table fred-1 (int fred) fails but
create table [fred-1] (int fred) works.

Eg2 CASE is a reserved word

create table case (int fred) fails but
create table [case] (int fred) works OK.

SOme people put the square brackets around all names just in case a word they use may become reserved in the future.

In your case there isn't any difference because none of the names used use an invalid character or reserved word.

 

[fluteplr]

It also solves the problem of DBAs who like to put spaces in object names. Personally I would prefer to take them out and shoot them , but to each his own.

 

[SQLSister]

Incidentally, when you create a table, you chould check it out in Enterprise Manager in the design mode and see if any of the fields have the [] around them. If they do, you have used a reserved word or invalid character and will have to always use the brackets to reference that fielkd. Best to change the field name right then before you have programmed anything with the field name!!

 

[HFChristie]

Great!

Thanks for the help