I came across some SQL in an application which had no space before the "ORDER BY" clause. I was surprised that this even works.
我在一个应用程序中遇到了一些SQL，它们在“ORDER BY”子句之前没有空格。我很惊讶这竟然行得通。
Given a table of numbers, called [counter] where there is simply one column, counter_id that is an incrementing list of integers this SQL works fine in Microsoft SQL Server 2012
给定一个名为[counter]的数字表，其中只有一个列，counter_id是一个整数递增的列表
select
*
FROM [counter] c
where c.counter_id = 1000ORDER by counter_id
This also works with strings, e.g.:
这也适用于字符串，例如:
WHERE some_string = 'test'ORDER BY something
My question is, are there any potential pitfalls or dangers with this query? And conversely, are there any benefits? Other than saving, what, 8 bits of network traffic for that whitespace (whcih may well be a consideration in some applications)
我的问题是，这个查询是否存在潜在的陷阱或危险?反过来，有什么好处吗?除了保存之外，还有8比特的网络流量用于空格(whcih在某些应用程序中很可能是一个考虑因素)
3 个解决方案
#1
4
Let me explain the reason why this works with numbers and strings.
让我来解释一下为什么这与数字和字符串有关。
The reason is because numbers cannot start identifiers, unless the name is escaped. Basically, the first things that happens to a SQL query is tokenization. That is, the components of the query are broken into identifiers and keywords, which are then analyzed.
原因是数字不能启动标识符，除非名称被转义。基本上，SQL查询的第一件事就是标记化。也就是说，查询的组件被分解为标识符和关键字，然后对它们进行分析。
In SQL Server, keywords and identifiers and function names (and so on) cannot start with a digit (unless the name is escaped, of course). So, when the tokenizer encounters a digit, it knows that it has a number. The number ends when a non-digit character is encountered. So, a sequence of characters such as 1000ORDER BY is easily turned into three tokens, 1000, ORDER, and BY.
在SQL Server中，关键字、标识符和函数名(等等)不能以数字开头(当然，除非名称被转义)。所以，当记号赋予器遇到一个数字时，它知道它有一个数字。当遇到非数字字符时，数字结束。因此，一个字符序列，如1000ORDER，很容易变成三个令牌，1000,ORDER，和BY。
Similarly, the first time that a single quote is encountered, it always represents a string literal. The string literal ends when the final single quote is encountered. The next set of characters represents another token.
类似地，第一次遇到单引号时，它总是表示字符串字面量。当遇到最后一个单引号时，字符串文字就结束了。下一组字符表示另一个令牌。
Let me add that there is exactly zero reason to ever use these nuances. First, these rules are properties of SQL Server's tokenization and do not necessarily apply to other databases. Second, the purpose of SQL is for humans to be able to express queries. It is way, way more important that we read them.
让我补充一点，完全没有理由使用这些细微差别。首先，这些规则是SQL Server的标记化属性，并不一定适用于其他数据库。其次，SQL的目的是让人类能够表达查询。更重要的是我们要读它们。
#2
0
As jarlh mentioned there might be difference during scanning and parsing the tokens but it creates correctly during execution plan, hence it might not be huge difference in advantages or disadvantages
正如jarlh提到的，在扫描和解析令牌时可能会有差异，但是在执行计划中会正确地创建，因此在优缺点上可能不会有太大的差异
#3
0
When parser examines characters ,it checks for keywords,identifiers,string constants and match overall semantic and syntactic structure of the language. Since 'Order by' is a keyword and sql parser knows its possible syntactic location in a query, it will interpret it accordingly without throwing any error. This is the reason why your order by will not throw any error. Parsing sql query
当解析器检查字符时，它检查关键字、标识符、字符串常量，并匹配语言的整体语义和语法结构。由于“Order by”是一个关键字，sql解析器知道它在查询中的语法位置，因此它将相应地解释它，而不会抛出任何错误。这就是为什么你的订单不会抛出任何错误的原因。解析sql查询
解析SQL
#1
4
Let me explain the reason why this works with numbers and strings.
让我来解释一下为什么这与数字和字符串有关。
The reason is because numbers cannot start identifiers, unless the name is escaped. Basically, the first things that happens to a SQL query is tokenization. That is, the components of the query are broken into identifiers and keywords, which are then analyzed.
原因是数字不能启动标识符，除非名称被转义。基本上，SQL查询的第一件事就是标记化。也就是说，查询的组件被分解为标识符和关键字，然后对它们进行分析。
In SQL Server, keywords and identifiers and function names (and so on) cannot start with a digit (unless the name is escaped, of course). So, when the tokenizer encounters a digit, it knows that it has a number. The number ends when a non-digit character is encountered. So, a sequence of characters such as 1000ORDER BY is easily turned into three tokens, 1000, ORDER, and BY.
在SQL Server中，关键字、标识符和函数名(等等)不能以数字开头(当然，除非名称被转义)。所以，当记号赋予器遇到一个数字时，它知道它有一个数字。当遇到非数字字符时，数字结束。因此，一个字符序列，如1000ORDER，很容易变成三个令牌，1000,ORDER，和BY。
Similarly, the first time that a single quote is encountered, it always represents a string literal. The string literal ends when the final single quote is encountered. The next set of characters represents another token.
类似地，第一次遇到单引号时，它总是表示字符串字面量。当遇到最后一个单引号时，字符串文字就结束了。下一组字符表示另一个令牌。
Let me add that there is exactly zero reason to ever use these nuances. First, these rules are properties of SQL Server's tokenization and do not necessarily apply to other databases. Second, the purpose of SQL is for humans to be able to express queries. It is way, way more important that we read them.
让我补充一点，完全没有理由使用这些细微差别。首先，这些规则是SQL Server的标记化属性，并不一定适用于其他数据库。其次，SQL的目的是让人类能够表达查询。更重要的是我们要读它们。
#2
0
As jarlh mentioned there might be difference during scanning and parsing the tokens but it creates correctly during execution plan, hence it might not be huge difference in advantages or disadvantages
正如jarlh提到的，在扫描和解析令牌时可能会有差异，但是在执行计划中会正确地创建，因此在优缺点上可能不会有太大的差异
#3
0
When parser examines characters ,it checks for keywords,identifiers,string constants and match overall semantic and syntactic structure of the language. Since 'Order by' is a keyword and sql parser knows its possible syntactic location in a query, it will interpret it accordingly without throwing any error. This is the reason why your order by will not throw any error. Parsing sql query
当解析器检查字符时，它检查关键字、标识符、字符串常量，并匹配语言的整体语义和语法结构。由于“Order by”是一个关键字，sql解析器知道它在查询中的语法位置，因此它将相应地解释它，而不会抛出任何错误。这就是为什么你的订单不会抛出任何错误的原因。解析sql查询
解析SQL