Can we enclose column name with double quotes in queries like select query

Question

Hey Team,

I'm dealing with multiple teams applications sql server database every day.

I'm getting different types and combinations of column names with space, special characters etc.

So that's why my query uses square brackets to enclose column names as below

select [column name1] from [schema].[table1]

But I have a column itself contains square brackets in its name, so my query will become like below

select [[column name1]] from [schema].[table1] - which throws error multiparty identifier error

If we change it to square brackets select "[column name1]" from [schema].[table1]

It will work with no issue

My concern is can we use square brackets to enclose column name of any kind. Does this recommended to use officially?

Answer 1

Test it with

select quotename('[Test it]')

returns

[[Test it]]]

Answer 2

You can use both double quotes and square brackets to enclose identifiers that contain character that is not part of the regular syntax for identifiers.

The double quotes is the ANSI standard, and should work on many database engines. The square brackets, on the other hand, are proprietary and may only work on SQL Server and Access (from which I believe the origin.)

In SQL Server, the double quotes only serves for quoting identifiers if the setting QUOTED_IDENTIFIER is ON. This is the default setting in most contexts, and you should always keep it ON. OFF is purely a legacy setting. There are some situations where QUOTED_IDENTIFIER for stupid reasons defaults to OFF. One is SQL Server Agent, but it is not a major problem as long as you are running stored procedures. This is because for a stored procedure, this is not a run-time setting, but it saved with the procedure.

If you have names with brackets, I definitely think that you should use the double quotes to delimit them, so that the code gets more readable.

My own personal preference these days is for the double quote. Although the reason for this is quite a trivial one: It's easier to achieve on a Swedish keyboard.