I have seen some people alias column names using single quotes eg:
我看到一些人使用单引号别名列名称，例如：
select orderID 'Order No' from orders
and others use square brackets eg:
和其他人使用方括号，例如：
select orderID [Order No] from orders
I tend to use square brackets. Is there any preference/difference?
我倾向于使用方括号。有偏好/差异吗？
4 个解决方案
#1
48
To answer the question "is there any preference/difference":
要回答“是否有任何偏好/差异”的问题：
Yes, there are as many preferences as there are opinions, but be careful whose preferences you adopt.
是的，有很多偏好和意见一样多，但要小心你采用的偏好。
As a best practice, it is advisable to write portable SQL if it doesn't require any extra effort.
作为最佳实践，如果不需要任何额外的工作，建议编写可移植的SQL。
For your specific sample, it is just as easy to write a portable query:
对于您的特定示例，编写可移植查询同样容易：
select OrderId as "Order Id" from Orders
Than to write a non-portable one:
写一个非便携式的：
select OrderId as [Order Id] from Orders
It isn't justifiable to write non-standard SQL when there is an equivalent portable form of the same number of keystrokes.
当存在相同数量的击键的等效可移植形式时，编写非标准SQL是不合理的。
The proliferation of [] for escaping is due to tools like SQL Server Management Studio and MS Access query builders, that lazily escape everything. It may never occur to a developer who spends his/her career in SQL Server, but the brackets have caused a lot of expense over the years porting Access and SQL Server apps to other database platforms. The same goes for Oracle tools that quote everything. Untrained developers see the DDL as examples, and then proceed to use the same style when writing by hand. It is a hard cycle to break until tools improve and we demand better. In Oracle, quoting, combined with mixed casing, results in case sensitive databases. I have seen projects where people quoted every identifier in the database, and I had the feeling I was in The Land of The Lost where the developers had evolved on an island without documentation or best practice articles.
[]用于转义的扩散是由于像SQL Server Management Studio和MS Access查询构建器这样的工具，它们懒得逃避一切。对于在SQL Server中度过他/她职业生涯的开发人员来说，它可能永远不会发生，但是多年来，括号在将Access和SQL Server应用程序移植到其他数据库平台方面造成了大量费用。引用所有内容的Oracle工具也是如此。未经训练的开发人员将DDL视为示例，然后在手动编写时继续使用相同的样式。在工具改进并且我们要求更好之前，这是一个艰难的循环。在Oracle中，引用与混合大小写相结合会导致区分大小写的数据库。我看过人们引用数据库中每个标识符的项目，我感觉我曾经在The Land of The Lost中开发人员在没有文档或最佳实践文章的岛上进化。
If you write your DDL, from the start, with normalized, legal identifiers (use OrderId, or order_Id instead of [Order Id], you don't worry about the mythical keyword that might need escape characters; the database will inform you when you've used a reserved word. I can count on one finger the times we've ever upgraded an app from one version of SQL Server to another and had any breakage due to new reserved words.
如果您从一开始就使用规范化的合法标识符（使用OrderId或order_Id而不是[Order Id]）编写DDL，则不必担心可能需要转义字符的神秘关键字;数据库会在您通知时通知您我已经使用了一个保留字。我可以指望我们曾经将应用程序从一个版本的SQL Server升级到另一个版本，并且由于新的保留字而导致任何破坏。
This is often the subject of heated debate, so if you think about it another way:
这通常是激烈争论的主题，所以如果你以另一种方式思考它：
C# programmers don't escape all their variables with @, even though it is legal to do so. That would be considered an odd practice, and would be the subject of ridicule on *. Escaping should be for the edge cases. But the same developers that write conforming C# identifiers don't mind escaping every single identifier in their SQL, writing terribly ugly, non-portable SQL "code". As a consultant, I've met more than one SQL Server programmer who honestly thought [] was required syntax. I don't blame the developers, I blame the tools.
C＃程序员不会使用@转义所有变量，即使这样做是合法的。这将被视为一种奇怪的做法，并将成为*上的嘲笑主题。转义应该是边缘情况。但编写符合C＃标识符的开发人员并不介意在SQL中转义每个标识符，编写非常丑陋，不可移植的SQL“代码”。作为一名顾问，我遇到了不止一位认为[]是必需语法的SQL Server程序员。我不怪开发者，我责怪工具。
#2
4
It depends on what settings you have in force whether 's are valid or not. And you missed out ". See Delimited Identifiers:
这取决于您有效的设置是否有效。你错过了“。看到分界标识符：
When QUOTED_IDENTIFIER is set to ON, SQL Server follows the ISO rules for the use of double quotation marks (")and the single quotation mark (') in SQL statements. For example:
当QUOTED_IDENTIFIER设置为ON时，SQL Server遵循ISO规则，在SQL语句中使用双引号（“）和单引号（'）。例如：
Double quotation marks can be used only to delimit identifiers. They cannot be used to delimit character strings.
双引号只能用于分隔标识符。它们不能用于分隔字符串。
Single quotation marks must be used to enclose character strings. They cannot be used to delimit identifiers.
必须使用单引号括起字符串。它们不能用于分隔标识符。
When QUOTED_IDENTIFIER is set to OFF, SQL Server uses the following rules for single and double quotation marks:
当QUOTED_IDENTIFIER设置为OFF时，SQL Server对单引号和双引号使用以下规则：
Quotation marks cannot be used to delimit identifiers. Instead, brackets have to be used as delimiters.
引号不能用于分隔标识符。相反，括号必须用作分隔符。
Single or double quotation marks can be used to enclose character strings.
单引号或双引号可用于括起字符串。
And finally:
最后：
Delimiters in brackets can always be used, regardless of the setting of QUOTED_IDENTIFIER
无论QUOTED_IDENTIFIER的设置如何，始终可以使用括号中的分隔符
Where, in all of the above quotes, when they refer to brackets they're talking about [] brackets.
在所有上述引用中，当他们提到括号时，他们谈论的是[]括号。
#3
3
Single quotes are more readable. As demonstrated above, highlighted in red.
单引号更具可读性。如上所示，以红色突出显示。
MySQL uses `backticks` to escape special characters.
MySQL使用`backticks`来逃避特殊字符。
MSSQL can either use "double quotes" or [brackets] for identifiers (tables, columns, etc)
and 'single quotes' for character strings or aliases.
MSSQL可以对标识符（表，列等）使用“双引号”或[括号]，对字符串或别名使用“单引号”。
The square brackets are used primarily to encapsulate objects so that special characters such as spaces, periods or hyphens do not throw syntax errors.
方括号主要用于封装对象，以便特殊字符（如空格，句点或连字符）不会引发语法错误。
I would recommend using the 'as' keyword before your column aliases - it's much more readable.
我建议在列别名之前使用'as'关键字 - 它更具可读性。
select [column with spaces] as 'my col' from "table with spaces" where n = 'foo'
select "column with spaces" as 'my col' from [table with spaces] where n = 'foo'
#4
1
The quote approach enables you to do this:
引用方法使您可以执行此操作：
SELECT 1 AS 'bla[]bla'
#1
48
To answer the question "is there any preference/difference":
要回答“是否有任何偏好/差异”的问题：
Yes, there are as many preferences as there are opinions, but be careful whose preferences you adopt.
是的，有很多偏好和意见一样多，但要小心你采用的偏好。
As a best practice, it is advisable to write portable SQL if it doesn't require any extra effort.
作为最佳实践，如果不需要任何额外的工作，建议编写可移植的SQL。
For your specific sample, it is just as easy to write a portable query:
对于您的特定示例，编写可移植查询同样容易：
select OrderId as "Order Id" from Orders
Than to write a non-portable one:
写一个非便携式的：
select OrderId as [Order Id] from Orders
It isn't justifiable to write non-standard SQL when there is an equivalent portable form of the same number of keystrokes.
当存在相同数量的击键的等效可移植形式时，编写非标准SQL是不合理的。
The proliferation of [] for escaping is due to tools like SQL Server Management Studio and MS Access query builders, that lazily escape everything. It may never occur to a developer who spends his/her career in SQL Server, but the brackets have caused a lot of expense over the years porting Access and SQL Server apps to other database platforms. The same goes for Oracle tools that quote everything. Untrained developers see the DDL as examples, and then proceed to use the same style when writing by hand. It is a hard cycle to break until tools improve and we demand better. In Oracle, quoting, combined with mixed casing, results in case sensitive databases. I have seen projects where people quoted every identifier in the database, and I had the feeling I was in The Land of The Lost where the developers had evolved on an island without documentation or best practice articles.
[]用于转义的扩散是由于像SQL Server Management Studio和MS Access查询构建器这样的工具，它们懒得逃避一切。对于在SQL Server中度过他/她职业生涯的开发人员来说，它可能永远不会发生，但是多年来，括号在将Access和SQL Server应用程序移植到其他数据库平台方面造成了大量费用。引用所有内容的Oracle工具也是如此。未经训练的开发人员将DDL视为示例，然后在手动编写时继续使用相同的样式。在工具改进并且我们要求更好之前，这是一个艰难的循环。在Oracle中，引用与混合大小写相结合会导致区分大小写的数据库。我看过人们引用数据库中每个标识符的项目，我感觉我曾经在The Land of The Lost中开发人员在没有文档或最佳实践文章的岛上进化。
If you write your DDL, from the start, with normalized, legal identifiers (use OrderId, or order_Id instead of [Order Id], you don't worry about the mythical keyword that might need escape characters; the database will inform you when you've used a reserved word. I can count on one finger the times we've ever upgraded an app from one version of SQL Server to another and had any breakage due to new reserved words.
如果您从一开始就使用规范化的合法标识符（使用OrderId或order_Id而不是[Order Id]）编写DDL，则不必担心可能需要转义字符的神秘关键字;数据库会在您通知时通知您我已经使用了一个保留字。我可以指望我们曾经将应用程序从一个版本的SQL Server升级到另一个版本，并且由于新的保留字而导致任何破坏。
This is often the subject of heated debate, so if you think about it another way:
这通常是激烈争论的主题，所以如果你以另一种方式思考它：
C# programmers don't escape all their variables with @, even though it is legal to do so. That would be considered an odd practice, and would be the subject of ridicule on *. Escaping should be for the edge cases. But the same developers that write conforming C# identifiers don't mind escaping every single identifier in their SQL, writing terribly ugly, non-portable SQL "code". As a consultant, I've met more than one SQL Server programmer who honestly thought [] was required syntax. I don't blame the developers, I blame the tools.
C＃程序员不会使用@转义所有变量，即使这样做是合法的。这将被视为一种奇怪的做法，并将成为*上的嘲笑主题。转义应该是边缘情况。但编写符合C＃标识符的开发人员并不介意在SQL中转义每个标识符，编写非常丑陋，不可移植的SQL“代码”。作为一名顾问，我遇到了不止一位认为[]是必需语法的SQL Server程序员。我不怪开发者，我责怪工具。
#2
4
It depends on what settings you have in force whether 's are valid or not. And you missed out ". See Delimited Identifiers:
这取决于您有效的设置是否有效。你错过了“。看到分界标识符：
When QUOTED_IDENTIFIER is set to ON, SQL Server follows the ISO rules for the use of double quotation marks (")and the single quotation mark (') in SQL statements. For example:
当QUOTED_IDENTIFIER设置为ON时，SQL Server遵循ISO规则，在SQL语句中使用双引号（“）和单引号（'）。例如：
Double quotation marks can be used only to delimit identifiers. They cannot be used to delimit character strings.
双引号只能用于分隔标识符。它们不能用于分隔字符串。
Single quotation marks must be used to enclose character strings. They cannot be used to delimit identifiers.
必须使用单引号括起字符串。它们不能用于分隔标识符。
When QUOTED_IDENTIFIER is set to OFF, SQL Server uses the following rules for single and double quotation marks:
当QUOTED_IDENTIFIER设置为OFF时，SQL Server对单引号和双引号使用以下规则：
Quotation marks cannot be used to delimit identifiers. Instead, brackets have to be used as delimiters.
引号不能用于分隔标识符。相反，括号必须用作分隔符。
Single or double quotation marks can be used to enclose character strings.
单引号或双引号可用于括起字符串。
And finally:
最后：
Delimiters in brackets can always be used, regardless of the setting of QUOTED_IDENTIFIER
无论QUOTED_IDENTIFIER的设置如何，始终可以使用括号中的分隔符
Where, in all of the above quotes, when they refer to brackets they're talking about [] brackets.
在所有上述引用中，当他们提到括号时，他们谈论的是[]括号。
#3
3
Single quotes are more readable. As demonstrated above, highlighted in red.
单引号更具可读性。如上所示，以红色突出显示。
MySQL uses `backticks` to escape special characters.
MySQL使用`backticks`来逃避特殊字符。
MSSQL can either use "double quotes" or [brackets] for identifiers (tables, columns, etc)
and 'single quotes' for character strings or aliases.
MSSQL可以对标识符（表，列等）使用“双引号”或[括号]，对字符串或别名使用“单引号”。
The square brackets are used primarily to encapsulate objects so that special characters such as spaces, periods or hyphens do not throw syntax errors.
方括号主要用于封装对象，以便特殊字符（如空格，句点或连字符）不会引发语法错误。
I would recommend using the 'as' keyword before your column aliases - it's much more readable.
我建议在列别名之前使用'as'关键字 - 它更具可读性。
select [column with spaces] as 'my col' from "table with spaces" where n = 'foo'
select "column with spaces" as 'my col' from [table with spaces] where n = 'foo'
#4
1
The quote approach enables you to do this:
引用方法使您可以执行此操作：
SELECT 1 AS 'bla[]bla'