CODEWITHSUNDEEP
sql-serverselectsql-order-by
Just a learner
Just a learner

Reputation: 28592

What does "ORDER BY (SELECT NULL)" mean?

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;

Thanks!

Upvotes: 57

Views: 33782

Answers (1)

usr
usr

Reputation: 171188

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

Upvotes: 99

Related Questions