什么是“ORDER BY (SELECT NULL)"?意思是?

问题描述

以下 SQL 来自 Itzik Ben-Gan，用于生成数字表.order by (select null) 部分是什么意思?谢谢.

The following SQL is from Itzik Ben-Gan that is used to generate a numbers table. What does the order by (select null) part mean? Thanks.

DECLARE @number_of_numbers INT;
SELECT @number_of_numbers = 100000;

WITH    a AS ( SELECT   1 AS i
               UNION ALL
               SELECT   1
             ),
        b AS ( SELECT   1 AS i
               FROM     a AS x ,
                        a AS y
             ),
        c AS ( SELECT   1 AS i
               FROM     b AS x ,
                        b AS y
             ),
        d AS ( SELECT   1 AS i
               FROM     c AS x ,
                        c AS y
             ),
        e AS ( SELECT   1 AS i
               FROM     d AS x ,
                        d AS y
             ),
        f AS ( SELECT   1 AS i
               FROM     e AS x ,
                        e AS y
             ),
        numbers
          AS ( SELECT TOP ( @number_of_numbers )
                        ROW_NUMBER() OVER ( ORDER BY ( SELECT   NULL
                                                     ) ) AS number
               FROM     f
             )
    SELECT  *
    FROM    numbers;

谢谢！

推荐答案

ROW_NUMBER 在语法上需要 ORDER BY 子句.没有它你就不能使用它.SELECT NULL 是一种在不强制执行任何特定命令的情况下关闭错误的技巧.在这种情况下，我们不需要强制执行任何顺序，因此最快的选择是使用 SELECT NULL.

ROW_NUMBER requires an ORDER BY clause syntactically. You cannot use it without one. SELECT NULL is a hack to shut up the error while not enforcing any particular order. In this case we don't need to enforce any order, so the fastest option is to use SELECT NULL.

优化器看穿了这个技巧，所以它没有运行时成本(通过查看执行计划很容易验证这个说法).

The optimizer sees through this trick, so it has no runtime cost (this claim is easily verified by looking at the execution plan).

这篇关于什么是“ORDER BY (SELECT NULL)"?意思是?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！