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

Question

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:

1. The rows for each customerId are contiguous in the output.

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

Answer 1

Im struggling to find the relevance here; if you want explicit ordering the recommended way would be to use an ORDER BY clause in the query.

I would never rely on the default ordering of a table when producing a query that I relied on the order of the results. Any modern RDBMS is going to be able to optimize an order by based on indexes and the like, so it's not something that has to be worried about.

In regards to row_number, while it is a side effect that if no ORDER BY clause exists, the output is ordered by the ROW_NUMBER value, you cannot depend on this behavior, as it is not guaranteed.

Again, the only way to guarantee order of the output is with an ORDER BY clause.

Answer 2

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

Answer 3

If you want it ordered, you must use ORDER BY.

just look at the execution plan with

SET SHOWPLAN_ALL ON

if there is no "order by" in the StmtText column, you just get things however they are sorted after all the work is done. Sometimes you get lucky, sometime not, with how the data is stored/loaded/filtered/joined, etc. and how it is returned.

Answer 4

As mentioned, you can't rely on row order without an ORDER BY.

However, you can rely on the ROW_NUMBER() function value.

SELECT
    principal_id, name, 
    ROW_NUMBER() OVER (ORDER BY principal_id DESC) AS DemoRank
FROM
    msdb.sys.database_principals
ORDER BY
    name

If you consume the data in your client by DemoRank, then you would be OK, even without the ORDER BY clause

If you rely on recordset ordering (ordinal index), then no.

The example above gives first row (index = 0) as '##MS_PolicyEventProcessingLogin##', but using DemoRank value gives "db_denydatawriter"

Basically, use ORDER BY.