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
Those brackets are included so that SQL Server can correctly interpret queries that reference table or column names that include spaces or special characters.
In SQL syntax notation, curly brackets enclose two or more required alternative choices, separated by vertical bars. [ ] In SQL syntax notation, square brackets indicate an optional element or clause. Multiple elements or clauses are separated by vertical bars.
Syntax, Examples, and Text Parentheses are used in SQL to group parameters or arguments. They are required when entering a command (i.e. they must be typed exactly as they appear). { item1 item2 ... } Curly braces indicate groupings of identifiers, parameters, or arguments.
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.
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]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With