Can I depend on order of output when using row_number()

sql, sql-server, t-sql

Solution

If you want an ordered result set, add an ORDER BY clause to your SELECT. Period. Anything else is circumstantial and may or may not work depending on the current SQL build you're testing, the day's mood of the optimizer and the phase of Mars transit in Pisces.

A trivial example that contradicts your assumption:

select orderId, CustomerId, orderDateTime
    , row_number() over (partition by customerId order by orderDateTime) RN
    , row_number() over (partition by orderDateTime order by customerId) AntiRN
from #order

Problem

I believe the answer is no. And am looking for a counter example to show that order of output is not guaranteed, absent an order by clause. consider: ``` create table #order (orderId int primary key clustered , customerId int not null -- references customer(customerId) , orderDateTIme datetime not null) insert into #order values (1, 100, '2009-01-01') insert into #order values (2, 101, '2009-01-02') insert into #order values (3, 102, '2009-01-03') insert into #order values (4, 103, '2009-01-04') insert into #order values (5, 100, '2009-01-05') insert into #order values (6, 101, '2009-01-06') insert into #order values (7, 101, '2009-01-07') insert into #order values (8, 103, '2009-01-08') insert into #order values (9, 105, '2009-01-09') insert into #order values (10, 100, '2009-01-10') insert into #order values (11, 101, '2009-01-11') insert into #order values (12, 102, '2009-01-12') insert into #order values (13, 103, '2009-01-13') insert into #order values (14, 100, '2009-01-14') insert into #order values (15, 100, '2009-01-15') insert into #order values (16, 101, '2009-01-16') insert into #order values (17, 102, '2009-01-17') insert into #order values (18, 101, '2009-01-18') insert into #order values (19, 100, '2009-01-19') insert into #order values (20, 101, '2009-01-20') select * from #order -- Results in PK order due to clustered primary key select orderId, CustomerId, orderDateTime , row_number() over (partition by customerId order by orderDateTime) RN from #order ``` On MS SQL Server 2005, the output ordering has two properties: The rows for each `customerId` are contiguous in the output. `Row_number()` is sequential within each customerId. My understanding is that these two properties are not guaranteed absent an explicit order by clause. I am looking for an example where the above properties do not hold that is not forced by an order by clause, but is just a result of how MS SQL Server happens to work. Feel free to develop own table definition, indexes, etc in your example if needed. Or if I am wrong, a link to a reference that would show that these orderings are guaranteed, even without an explicit order by clause.

Original source