227

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...
Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
Paulj
  • 3,096
  • 3
  • 25
  • 30
  • 1
    It's a good thing that your hand coded SQL has never needed brackets, your database naming convention should exclude names that need brackets. – Christian Oudard Sep 09 '08 at 21:06
  • Related answer discusses QUOTED_IDENTIFIER setting: http://stackoverflow.com/questions/19657101/what-is-the-difference-between-square-brackets-and-single-quotes-for-aliasing-in – Jared Beck Oct 29 '14 at 02:09
  • 23
    I might add that the brackets are a Microsoft quirk. The SQL Standard actually uses double quotes(`"`), which Microsoft _also_ supports. If, Microsoft felt the need to delimit everything, it would have been better form to use the standard double-quotes instead. But I guess that would have made it too easy to port to another DBMS, and that will never do … – Manngo Dec 14 '17 at 21:41
  • And they are added automatically when you use a GUI and, instead of typing the names, drag the table to code window. That way it can work no matter if the names contain undesirable characters. – skan Dec 06 '21 at 00:42

10 Answers10

247

The brackets are required if you use keywords or special chars in the column names or identifiers. 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.

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Michael Haren
  • 105,752
  • 40
  • 168
  • 205
  • 7
    is this the only purpose of the square brackets? –  Jul 08 '13 at 15:33
  • 1
    @mehow, it [seems](http://msdn.microsoft.com/en-us/library/aa224033(v=sql.80).aspx) that way, but I don't have a reference indicating such explicitly. – Michael Haren Jul 08 '13 at 16:02
  • 3
    Related q: http://stackoverflow.com/questions/9917196/meaning-of-square-brackets-in-ms-sql-table-designer – Michael Haren Jul 08 '13 at 16:03
  • 2
    sql also uses square brackets in the like-operator of a select query to limit results using regular expressions. http://www.codeproject.com/Articles/33941/SQL-LIKE-Operator – Jens Frandsen Sep 04 '13 at 18:49
  • 3
    You may also define a column name called `select` but `select select from mytable` would throw an error so `select [select] from mytable` would make it a valid query. Whatever word becomes colourful in your SQL management studio, should be surrounded by brackets if you want to use them as column / table / sp / etc. names. In MySQL angled single quotes ```` would be the equivalent of this. – Baz Guvenkaya Feb 09 '17 at 05:03
  • 1
    And we use `@` sign in `C#` for the reserved words. Let's say I'm dynamically parsing a `JSON` and one of my variables' name is `params`. Since it's a reserved word VS will give it a different colour so you'll get a compile time error. Correct usage will be; `dynamic stuff = JObject.Parse(myJSON); bool isItNull = stuff.@params == null;` – Baz Guvenkaya Feb 09 '17 at 05:14
  • is it allowed in oracle also ? – Shivam Arora Jul 06 '17 at 12:20
  • No prizes for guessing it's a bad idea to use spaces and reserved words for column names.. – Norbert Norbertson Dec 08 '17 at 11:42
  • 3
    I know auto-generated queries use squared brackets around EVERYTHING. But that's because Microsoft is too lazy too check if it's acutally required. You should only use square-bracks when it's absolutely needed. – NoName Nov 10 '19 at 03:24
  • 1
    @BazGuvenkaya Can you imagine putting the @ sign before every C# variable, "just in case" a future C# version adds a new keyword? IMHO this would be totally insane. (Also something to consider: the sanity of writing every C# keyword in all caps.) – Jared Thirsk Jan 08 '21 at 14:05
76

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.

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
  • @Blordbeard is this something specific to Microsoft SQL Server or do square brackets also work the same for Oracle, mySQL, PostgreSQL etc.? – user3700562 Aug 18 '18 at 17:24
  • 6
    Brackets are not standard SQL, not sure what characters all the DB engines use. For example: MySQL uses backticks: https://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-used-as-column-names-mysql-create-table – Blorgbeard Aug 19 '18 at 03:46
12

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.

GateKiller
  • 74,180
  • 73
  • 171
  • 204
10

During the dark ages of SQL in the 1990s it was a good practice as the SQL designers were trying to add each word in the dictionary as keyword for endless avalanche of new features and they called it the SQL3 draft.

So it keeps forward compatibility.

And i found that it has another nice side effect, it helps a lot when you use grep in code reviews and refactoring.

Lothar
  • 12,537
  • 6
  • 72
  • 121
8

Regardless of following a naming convention that avoids using reserved words, Microsoft does add new reserved words. Using brackets allows your code to be upgraded to a new SQL Server version, without first needing to edit Microsoft's newly reserved words out of your client code. That editing can be a significant concern. It may cause your project to be prematurely retired....

Brackets can also be useful when you want to Replace All in a script. If your batch contains a variable named @String and a column named [String], you can rename the column to [NewString], without renaming @String to @NewString.

Bill
  • 198
  • 1
  • 5
  • Replace all workaround: rename `@String` to `$PreserveMe$`, then rename String to NewString, then rename `$PreserveMe$` back to `@String`. – Jared Thirsk Jan 08 '21 at 14:03
7

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.

6

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.

Torlack
  • 4,395
  • 1
  • 23
  • 24
4

In addition Some Sharepoint databases contain hyphens in their names. Using square brackets in SQL Statements allow the names to be parsed correctly.

HSchlarb
  • 41
  • 1
4

They are useful to identify each elements in SQL.

For example:

CREATE TABLE SchemaName.TableName (

This would actually create a table by the name SchemaName.TableName under default dbo schema even though the intention might be to create the table inside the SchemaName schema.

The correct way would be the following:

CREATE TABLE [SchemaName].[TableName] (

Now it it knows what is the table name and in which schema should it be created in (rightly in the SchemaName schema and not in the default dbo schema)

zar
  • 11,361
  • 14
  • 96
  • 178
  • Thanks for this. I encountered this kind of usage. However, could you elaborate on how `it knows what is the table name and in which schema should it be created in`? How do you pass in the `SchemaName` and `TableName` parameters? – alelom Oct 18 '21 at 09:38
  • @alexlomba87 It's been a while but I think my answer was based on my testing. The Microsoft book for SQL course says "You should use two-part names to refer to tables in SQL Server databases, such as Sales.Customer" so they don't ask to surround namespace with square bracket but if I remember correctly, surrounding them puts it in the right namepsace. – zar Oct 19 '21 at 00:46
3

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.

Jeff Donnici
  • 2,738
  • 19
  • 17