What are the advantages always square brackets sql server objects inside procedure

Question

Hi What are the advantages always square brackets sql server objects inside procedure?

--proc format 01
CREATE [schema].[Proc_getdata01]
AS
BEGIN
SELECT [column01],[column01] FROM [schema].[table]
WHERE [column03]='01';
END
GO

--proc format 02
CREATE schema.Proc_getdata02
AS
BEGIN
SELECT column01,column01 FROM schema.table
WHERE column03='01';
END
GO

I hope these are following advantages,

1) If you have SQL keyword, space or any other illegal characters then you need to use square brackets.
2) SQL server parse and compiler much more easy to validate and compile code.
3) code search tools easy to find table names or column names only.

Do you have different arguments for above advantages? what are the main disadvantages?
any different view for above second point?

Answer 1

If you have identifiers with space, comma etc in the name ,you need to use square brackets. The same if the identifier is a reserved keyword.

The only advantage is that if you don't use brackets, it could happen that a table or a column name becomes a reserved keyword in a future version of SQL Server. Microsoft are however very restrictive with adding new keywords. so I don't find this compelleting

It's not advantage for parsing, since the square brackets are not mandatory, so a parser must handle both.

I see many people who use square brackets more or less consistently, but my opinion is that 1) the code is more difficult to read and 2) it wears out your fingers. I only use square brackets when I have names that require it. And I avoid using names that needs bracketing in table and columns. I would only use for column aliases returned to the client.

Finally, it is worth pointing that the square brackets is a convention unique for Microsoft. In standard SQL, you use the double quote " for the same purpose. And you can use double quotes with SQL Server as well. (But the brackets are far more common.

Answer 2

Hi @Tharindu Dhaneenja ,

Welcome to Microsoft Q&A!

The brackets are required if you use keywords, spaces, hyphens or special chars in the column names or identifiers.

Using square brackets in SQL Statements allow the names to be parsed correctly. But I failed to find out any official document mentioning that it could be much easier to compile code.

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

Brackets could also be useful when you would like to replace all in a script. If your batch contains a variable named @ hide and a column named [String], you can rename the column to [NewString], without renaming @ hide to @NewString.

But it could be recommended to focus more on better naming convention that would avoid using the [] unless unavoidable. It would definitely save a lot of typing effort.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Additional to the other see Database Identifiers for more details and rule, when you must use brackets for an identifier name.

Answer 4

There are two main disadvantages:

Code is harder to read.

It is not ansi sql. For ansi sql you should use double quotes instead of brackets.