What is the use of the square brackets [] in sql statements?

Question

Hi,

I've noticed that Visual Studio 2008 is placing square brackets around column names in sql. Do the brackets offer any advantage? When I hand code T-SQL I've never bothered with them.

Example: Visual Studio: SELECT [column1], [column2] etc...

My own way: SELECT column1, column2 etc...

Answer 1

The brackets can be used when column names are reserved words.

If you are programatically generating the SQL statement from a collection of column names you don't control, then you can avoid problems by always using the brackets.

Answer 2

The brackets are required if you use keywords or special chars in the column names. You could name a column [First Name] (with a space)--but then you'd need to use brackets every time you referred to that column.

The newer tools add them everywhere just in case or for consistency.

Answer 3

Column names can contain characters and reserved words that will confuse the query execution engine, so placing brackets around them at all times prevents this from happening. Easier than checking for an issue and then dealing with it, I guess.

Answer 4

I believe it adds them there for consistency... they're only required when you have a space or special character in the column name, but it's cleaner to just include them all the time when the IDE generates SQL.

Answer 5

It's a good thing that your hand coded SQL has never needed brackets, your database naming convention should exclude names that need brackets.

Answer 6

They're handy if your columns have the same names as SQL keywords, or have spaces in them.

Example:

create table test ( id int, user varchar(20) )

Oh no! Incorrect syntax near the keyword 'user'. But this:

create table test ( id int, [user] varchar(20) )

Works fine.

Answer 7

They are useful if you are (for some reason) using column names with certain characters for example.

Select First Name From People

would not work, but putting square brackets around the column name would work

Select [First Name] From People

In short, it's a way of explicitly declaring a object name; column, table, database, user or server.

Answer 8

Microsoft is generously providing us with the opportunity to exercise the same sort of careless attitude toward table and column names that we enjoy with directories and files.