Behaviour change - ROW_NUMBER and COMPATIBILITY_LEVEL - 100 vs 110

Question

Behaviour change - ROW_NUMBER and COMPATIBILITY_LEVEL - 100 vs 110

marklegosz

SSCrazy

Points: 2816
More actions
January 24, 2017 at 10:58 am

#401475

One of our developers has written a query using ROW_NUMBER OVER ORDER BY to generate a sequence number, and inserts this into an empty table. With compatibility level set to 100, the "select" by itself returns the same data as the "insert into select from". However when switching to compatibility 110, the ordering of the inserted data changes, yet the select by itself returns the same data set as compatibility level 100.
I've read through the MS notes on this compatibility level change and nothing jumps out at me to point to this being a known or expected behaviour - the ROW_NUMBER may be a symptom of the issue not the cause.
Not specifically looking for a solution, but just wondering if anyone has any thoughts or has experienced anything similar? We're SQL 2012 SP3 with Latin1_General_BIN collation (yay PeopleSoft).
thanks, Mark

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

marklegosz - Tuesday, January 24, 2017 10:58 AM
One of our developers has written a query using ROW_NUMBER OVER ORDER BY to generate a sequence number, and inserts this into an empty table. With compatibility level set to 100, the "select" by itself returns the same data as the "insert into select from". However when switching to compatibility 110, the ordering of the inserted data changes, yet the select by itself returns the same data set as compatibility level 100.
I've read through the MS notes on this compatibility level change and nothing jumps out at me to point to this being a known or expected behaviour - the ROW_NUMBER may be a symptom of the issue not the cause.
Not specifically looking for a solution, but just wondering if anyone has any thoughts or has experienced anything similar? We're SQL 2012 SP3 with Latin1_General_BIN collation (yay PeopleSoft).
thanks, Mark

Well if the ORDER BY in the ROW_NUMBER clause is ambiguous it would be entirely possible to see different results on different runs. Now that's not specific to compatibility level but something that's generally possible.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

You may not be looking for a solution but it would help to see the query. Can you recreate the issue in a sandbox (empty) database? If so, can you post the DDL needed to recreate the issue?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

marklegosz - Tuesday, January 24, 2017 10:58 AM
One of our developers has written a query using ROW_NUMBER OVER ORDER BY to generate a sequence number, and inserts this into an empty table. With compatibility level set to 100, the "select" by itself returns the same data as the "insert into select from". However when switching to compatibility 110, the ordering of the inserted data changes, yet the select by itself returns the same data set as compatibility level 100.
I've read through the MS notes on this compatibility level change and nothing jumps out at me to point to this being a known or expected behaviour - the ROW_NUMBER may be a symptom of the issue not the cause.
Not specifically looking for a solution, but just wondering if anyone has any thoughts or has experienced anything similar? We're SQL 2012 SP3 with Latin1_General_BIN collation (yay PeopleSoft).
thanks, Mark

If your INSERT INTO doesn't have a stated ORDER BY, then the sequencing created ROW_NUMBER() isn't a stable sequencing under either compatibility level. Remember, there is no such thing (in either compatibility level) as "physical order", so there is no actual guarantee that the sequencing will stay the same between runs without explicit ordering of the SELECT statement. The INSERT INTO documentation does specifically mention that the ONLY thing the ORDER BY clause is good for is to help with sequencing,

I think you're drawing a correlation based on false premises here.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?