使用下划线解析SQL Server编号文字

I wonder why it works and why it does not return an error:

我想知道为什么它工作，为什么它不会返回错误：

SELECT 2015_11

Result:

结果：

╔══════╗
║ _11  ║
╠══════╣
║ 2015 ║
╚══════╝

Second case:

第二种情况：

SELECT 2.1_a

╔═════╗
║ _a  ║
╠═════╣
║ 2.1 ║
╚═════╝

Checking metadata:

检查元数据：

SELECT  name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
N'SELECT 2015_11', NULL, 0) 
UNION ALL
SELECT  name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
N'SELECT 3.2_a', NULL, 0) 

╔══════╦══════════════════╗
║ name ║ system_type_name ║
╠══════╬══════════════════╣
║ _11  ║ int              ║
║ _a   ║ numeric(2,1)     ║
╚══════╩══════════════════╝

While identifier that starts with letter behaves as I think it should:

虽然以字母开头的标识符表现得像我认为的那样：

SELECT a_11
-- Invalid column name 'a_11'.

LiveDemo

LiveDemo

2 个解决方案

#1

SQL treats query like

SQL将查询视为

SELECT 2015_11

如

SELECT 2015 _11

which is shortcut for

这是快捷方式

SELECT 2015 AS [_11]

SQL Server expects column names to follow some naming convention rules as detailed in this MSDN link

SQL Server期望列名遵循一些命名约定规则，如此MSDN链接中所述

The names of variables, functions, and stored procedures must comply with the following rules for Transact-SQL identifiers. The first character must be one of the following:

变量，函数和存储过程的名称必须符合以下Transact-SQL标识符规则。第一个字符必须是以下之一：

A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.

Unicode标准3.2定义的字母。字母的Unicode定义包括从a到z，从A到Z的拉丁字符，以及来自其他语言的字母字符。

The underscore (_), at sign (@), or number sign (#).

下划线（_），符号（@）或数字符号（＃）。

Certain symbols at the beginning of an identifier have special meaning in SQL Server. A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.

标识符开头的某些符号在SQL Server中具有特殊含义。以at符号开头的常规标识符始终表示局部变量或参数，不能用作任何其他类型对象的名称。以数字符号开头的标识符表示临时表或过程。以双数字符号（##）开头的标识符表示全局临时对象。虽然数字符号或双数字符号可用于开始其他类型对象的名称，但我们不建议使用此做法。

Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.

某些Transact-SQL函数的名称以符号（@@）的double开头。为避免与这些功能混淆，不应使用以@@开头的名称。

Also the syntax for SELECT as per MSDN is like

根据MSDN，SELECT的语法也是如此

SELECT [ ALL | DISTINCT ] [ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ] ::= { * | { table_name | view_name | table_alias }.* | { [ { table_name | view_name | table_alias }. ] { column_name | $IDENTITY | $ROWGUID } | udt_column_name [ { . | :: } { { property_name | field_name } | method_name ( argument [ ,...n] ) } ] | expression [ [ AS ] column_alias ] } | column_alias = expression } [ ,...n ]

选择[全部| DISTINCT] [TOP（表达式）[PERCENT] [WITH TIES]] :: = {* | {table_name | view_name | table_alias}。* | {[{table_name | view_name | table_alias}。 ] {column_name | $ IDENTITY | $ ROWGUID} | udt_column_name [{。 | ::} {{property_name | field_name} | method_name（argument [，... n]）}] |表达式[[AS] column_alias]} | column_alias = expression} [，... n]

In this case the SQL parser first checks for table name, and then column name, Identity and rowguid, and so on till it hits the match with

在这种情况下，SQL解析器首先检查表名，然后检查列名，Identity和rowguid，依此类推，直到它与

| expression [ [ AS ] column_alias ]

|表达式[[AS] column_alias]

It then reads literal value till underscore character which is when it realizes that literal must have ended and starts parsing the later characters as Column_alias without as explicit AS

然后它读取字面值直到下划线字符，这是当它意识到文字必须已经结束并开始将后面的字符解析为Column_alias而没有显式AS

To verify this try following code in SQL server

要验证这一点，请尝试在SQL Server中执行以下代码

SELECT 2015AS _11

This will produce same results as

这将产生相同的结果

SELECT 2015_11

Also to verify what I just wrote above see the screenshot from SSMS which does a code highlighting on AS

另外，为了验证我刚刚在上面写的内容，请参阅SSMS的屏幕截图，它在AS上执行代码突出显示

In your first example 2015 is integer literal and in second example 2.1 is decimal literal

在您的第一个示例中，2015是整数字面值，在第二个示例中，2.1是十进制字面值

In your third example a is not a valid literal. If you try

在第三个示例中，a不是有效的文字。如果你试试

SELECT 'a'_8

This will give you result like

这会给你带来的结果

╔═════╗
║ _8  ║
╠═════╣
║ a   ║
╚═════╝

PS: You'll see that this works pretty much the same way with # as well

PS：你会发现这与＃的工作方式大致相同

So SELECT 2015#11 will give similar results

所以SELECT 2015＃11将给出类似的结果

╔══════╗
║ #11  ║
╠══════╣
║ 2015 ║
╚══════╝

#2

To understand what is happening, you need to understand what SQL Server accepts as identifiers. There are a lot of rules, which are documented here. But, the important one is:

要了解发生的情况，您需要了解SQL Server接受的标识符。这里记录了很多规则。但是，重要的是：

The first character must be one of the following:

第一个字符必须是以下之一：

A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.

Unicode标准3.2定义的字母。字母的Unicode定义包括从a到z，从A到Z的拉丁字符，以及来自其他语言的字母字符。

The underscore (_), at sign (@), or number sign (#).

下划线（_），符号（@）或数字符号（＃）。

The important point is that when the SQL Server parser encounters a digit, it says to itself: "This is a number". When it hits the underscore, it says "Well, no more number, must be starting something else". The parser recognizes the second component as a valid identifier, so this is treated as:

重要的一点是，当SQL Server解析器遇到一个数字时，它会对自己说：“这是一个数字”。当它击中下划线时，它说“好吧，没有更多的数字，必须开始别的东西”。解析器将第二个组件识别为有效标识符，因此将其视为：

select 2015 _11

which is for a column alias, even without the as.

这是一个列别名，即使没有as。

#1