在SQL Server中，别名使用方括号和单引号有什么区别？ [英] What is the difference between square brackets and single quotes for aliasing in SQL Server?

问题描述

我看到有些人使用单引号来别名列名称，例如：

 从订单$ b中选择orderID'订单号' $ b  

其他使用方括号，例如：

 从订单
  

中选择orderID [订单号]我倾向于使用正方形括号。有偏好/差异吗？

解决方案

要回答是否有偏好/差异问题：

是的，偏好与意见一样多，但请注意您采用的偏好。

作为最佳做法，如果不需要任何额外的努力，建议编写可移植的SQL。

对于您的特定示例，编写可移植的查询同样容易...

 从订单
   
 
 
 ...就像写一个非便携式的一样：
 从Orders 
  
中选择OrderId作为[Order Id]，最好不要在此处编写非标准SQL是等效的可移植形式，具有相同数量的击键。
 
 
 
 []转义的扩散是由于诸如SQL Server Management Studio和MS Access查询生成器之类的工具所致。懒散地逃避一切ing。在SQL Server中度过其职业生涯的开发人员可能永远不会发生这种情况，但是这些年来，将Access和SQL Server应用程序移植到其他数据库平台上花费了很多时间。引用所有内容的Oracle工具也是如此。未经培训的开发人员将DDL作为示例，然后在手工编写时继续使用相同的样式。在工具改进和我们要求更好之前，这是一个艰难的循环。在Oracle中，引用和大小写混合会导致区分大小写的数据库。我看到过有人引用数据库中每个标识符的项目，而且我感觉自己在《失落的土地》中，开发人员在一个没有文档或最佳实践文章的岛屿上发展。
 
 
 
如果您从一开始就使用标准化的合法标识符（使用OrderId或order_Id而不是[Order Id]来编写DDL），则不必担心可能需要转义字符的神话关键字；数据库会在您使用保留字时通知您。我可以指望一次我们将应用程序从一个版本的SQL Server升级到另一个版本并且由于 new 而造成的任何损坏。 
 
 
 
这通常是激烈辩论的主题，因此，如果您以另一种方式考虑：
 
 
 
 C＃程序员不会使用@来转义所有变量，即使这样做是合法的，这被认为是一种奇怪的做法，并且在StackOverflow上是可笑的话题。 GE案例。但是，编写符合标准C＃标识符的开发人员并不介意在其SQL中转义每个标识符，而是编写难看的，不可移植的SQL代码。作为一名顾问，我遇到了不止一名SQL Server程序员，他们诚实地认为[]是必需的语法。我不怪开发商。我责怪这些工具。
 
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?
解决方案
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.

For your specific sample, it is just as easy to write a portable query...
select OrderId as "Order Id" from Orders
... as it is to write a non-portable one:
select OrderId as [Order Id] from Orders
It is preferable not to write non-standard SQL when there is an equivalent portable form of the same number of keystrokes.

The proliferation of [] for escaping is due to tools like SQL Server Management Studio and MS Access query builders, which 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.

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.

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 StackOverflow. 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.

                        
这篇关于在SQL Server中，别名使用方括号和单引号有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！