45

I have a copy of an existing database with existing records. when i was playing around with the table designer and noticed some of the column names have [] around them. they all seem to be arbitrary typed (float, datetime, netext, nvarchar etc) and there is nothing in column properties that gets rid of the []. I tried to rename delete the [] but it reappaears as soon as I exit edit.

according to this post, it is a keyword column for xml columns? but none of those columns are xml columns. Would someone kindly explain the purpose of this to a ms-sql newbie? thanks

Community
  • 1
  • 1
Bonk
  • 1,859
  • 9
  • 28
  • 46

3 Answers3

90

The square brackets [] are used to delimit identifiers. This is necessary if the column name is a reserved keyword or contains special characters such as a space or hyphen.

Some users also like to use square brackets even when they are not necessary.

From MSDN:

Delimited identifiers

Are enclosed in double quotation marks (") or brackets ([ ]). Identifiers that comply with the rules for the format of identifiers may or may not be delimited.

SELECT *
FROM [TableX]         --Delimiter is optional.
WHERE [KeyCol] = 124  --Delimiter is optional.

Identifiers that do not comply with all of the rules for identifiers must be delimited in a Transact-SQL statement.

SELECT *
FROM [My Table]      --Identifier contains a space and uses a reserved keyword.
WHERE [order] = 10   --Identifier is a reserved keyword.
Kasper van den Berg
  • 8,951
  • 4
  • 48
  • 70
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 4
    (They can quote any identifier, not just columns.) –  Mar 28 '12 at 23:41
  • that makes sense, all the column names that have `[]` also have `space` in their names. however by removing the `space` and `[]` manually, table designer still automatically adds `[]`? – Bonk Mar 28 '12 at 23:42
  • 1
    @YonkShi It probably does so for consistency (there is no need to worry about if it conflicts with a keyword or contains special characters etc.), even when not needed. Unless there is a *generation option* to disable such (e.g. global setting?), it'll remain the way it is. –  Mar 28 '12 at 23:43
  • I do find it strange that I have to specify the brackets around 'Address', which is not on the list of reserved keywords?! – Jowen May 06 '14 at 11:43
  • 1
    @Jowen 'Address' seems to be reserved but can't find what's for – klaudyuxxx Apr 23 '15 at 10:00
12

Square brackets may be placed around objects (e.g. views, databases, columns etc)

Their primary purpose is, as Mark mentioned, to encapsulate the objects so that special characters such as a space or period do not interfere with your syntax.

Many applications by default use the bracketed notation, to further reduce risk of syntax errors or anything of that sort.

It's typically good practice to not include any spaces

Database_Name < GOOD PRACTICE

Database Name < GENERALLY TRY TO AVOID

In any case, if the latter is used, you may use square brackets i.e.

select * from [Database Name]
Michael Capobianco
  • 792
  • 6
  • 17
  • 2
    +1 for "Many applications by default use the bracketed notation, to further reduce risk of syntax errors" being the actual answer. – onedaywhen Mar 29 '12 at 08:29
4

the brackets are special characters in sql server that are used to explicitly delimit information.

they can be used in xml as per the article, they can also be used to specify meta names (column, table, etc.) that have spaces or other key words.

declare my column nvarchar(50)

would be invalid syntax, but you could do

declare [my column] nvarchar(50)

just think of them as explicit delimiters.

Taran
  • 12,822
  • 3
  • 43
  • 47
mson
  • 7,762
  • 6
  • 40
  • 70