Is ROW_NUMBER() non-deterministic?

I have presented my T-SQL Window function presentations for a couple of years. I love it when interesting questions come up during the session. Recently, during my intro session, I was showing the difference between ROW_NUMBER, RANK, and DENSE_RANK by using a non-unique ORDER BY column. Here is the query I ran:

 

SELECT SalesOrderID, OrderDate, CustomerID, 
	ROW_NUMBER() OVER(ORDER BY OrderDate) As RowNum,
	RANK() OVER(PARTITION BY CustomerID ORDER BY OrderDate) As Rnk,
	DENSE_RANK() OVER(PARTITION BY CustomerID ORDER BY OrderDate) As DenseRnk
FROM Sales.SalesOrderHeader
WHERE CustomerID = 11078;

Here are the results. We were looking at rows 14 and 15 where the order date was the same.

det 1

Someone asked if the row numbers 14 and 15 could be switched and assigned to the opposite SalesOrderID rows. Sure, they could. I thought that the database engine is just assigning them the way that they are because it is the easiest. So, even with the same data and the same OVER clause, ROW_NUMBER could return something different. This is called non-deterministic behavior.

In order to prove this, let’s take a look at another query to see if we can get the row numbers to switch on 2014-03-15.

 

SELECT SalesOrderID, OrderDate, CustomerID, 
	ROW_NUMBER() OVER(ORDER BY OrderDate) As RowNum,
	RANK() OVER(ORDER BY OrderDate) As Rnk,
	DENSE_RANK() OVER(PARTITION BY CustomerID ORDER BY OrderDate) As DenseRnk
FROM Sales.SalesOrderHeader
WHERE CustomerID = 11078
ORDER BY OrderDate, SalesOrderID DESC;

By changing the sort order, I was able to get those two row numbers to switch rows:

det 3

Now 68288 gets row number 14 instead of 15 with exactly the same OVER clause.

UPDATE: After seeing that someone on Twitter disagreed with me because I changed the query’s ORDER BY clause, I decided to do some research. I found that all of the Ranking functions, and, actually, all the windowing functions that depend on ORDER BY in the OVER clause are non-deterministic. Here is a list of SQL Server functions divided by determinism.

About Kathi Kellenberger

I am a SQL Server professional working for Linchpin People. I love talking about SQL Server to anyone who will listen, just ask my two year old granddaughter. I love to write and teach. I am so humbled and honored to say that I made SQL Server MVP in 2013.
This entry was posted in T-SQL, T-SQL Window Functions. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s