[] brackets in sql statements

28
votes

What do the brackets do in a sql statement?

For example, in the statement: 

insert into table1 ([columnname1], columnname2) values (val1, val2)

Also, what does it do if the table name is in brackets?

8
sqlms-access
Duplicate question: stackoverflow.com/questions/52898/sql-syntax-curiousity - bdukes
Better title on this one though. Who, upon encountering brackets for the first time, would search for "curiousity"? - Shog9
Especially since "curiousity" isn't even a word. - Justin Bennett
HA! Crazy canuks and their superfluous 'u's. :-p - Shog9

8 Answers

45
votes

The [] marks the delimitation of a identifier, so if you have a column whose name contains spaces like Order Qty you need to enclose it with [] like:

select [Order qty] from [Client sales]

They are also to escape reserved keywords used as identifiers

14
votes

This is Microsoft SQL Server nonstandard syntax for "delimited identifiers." SQL supports delimiters for identifiers to allow table names, column names, or other metadata objects to contain the following:

  • SQL reserved words: "Order"
  • Words containing spaces: "Order qty"
  • Words containing punctuation: "Order-qty"
  • Words containing international characters
  • Column names that are case-sensitive: "Order" vs. "order"

Microsoft SQL Server uses the square brackets, but this is not the syntax standard SQL uses for delimited identifiers. Standardly, double-quotes should be used for delimiters.

In Microsoft SQL Server, you can enable a mode to use standard double-quotes for delimiters as follows:

SET QUOTED_IDENTIFIER ON;
6
votes

They are meant to escape reserved keywords or invalid column identifiers.

CREATE TABLE test
(
  [select] varchar(15)
)

INSERT INTO test VALUES('abc')

SELECT [select] FROM test
2
votes

Anything inside the brackets is considered a single identifier (e.g. [test machine]. This can be used to enclose names with spaces or to escape reserve words (e.g. [order], [select], [group]).

2
votes

They allow you to use keywords (such as date) in the name of the column, table, etc...

Since this is a bad practice to begin with, they are generally not included. The only place you should see them being used is by people starting out with sql queries that don't know any better. Other than that they just clutter up your query.

1
votes

if you use any column name which is same as any reserved keyword in sql, in that case you can put the column name in square bracket to distinguish between your custom column name and existing reserved keyword.

1
votes

When having table names or filenames with spaces or dashes (-) etc... you can receive "Systax error in FROM clause". Use [] brackets to solve this.

See: https://msdn.microsoft.com/en-us/library/ms175874.aspx

0
votes

They are simply delimiters that allow you to put special characters (like spaces) in the column or table name e.g. 

insert into [Table One] ([Column Name 1], columnname2) values (val1, val2)