Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Behaviour change - ROW_NUMBER and COMPATIBILITY_LEVEL - 100 vs 110 RE: Behaviour change - ROW_NUMBER and COMPATIBILITY_LEVEL - 100 vs 110

  • January 24, 2017 at 3:06 pm

    #1924758

    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?