Post reply

INSERT ... SELECT ... ORDER BY consistency of results

  • November 14, 2011 at 8:23 am

    #262666

    I am trying to eliminate every possible source of an error, which we cannot seem to reproduce consistantly?

    Is this construct absolutely guaranteed to produce a consistent sequence of table variable TmpIDs every time? (assuming RealTable does not change)

    DECLARE @Tmp TABLE( TmpID INT IDENTITY, TmpDATA VARCHAR);

    INSERT INTO @Tmp (TmpDATA)

    SELECT RealDATA

    FROM dbo.RealTable

    ORDER BY RealSort;

    And would exactly the same behaviour be expected in SQL 2005?

  • November 14, 2011 at 8:32 am

    #1407217

    If it is critical to have TmpID in order by RealSort, you should use ROW_NUMBER()

    DECLARE @Tmp TABLE( TmpID INT, TmpDATA VARCHAR);

    INSERT INTO @Tmp (TmpID, TmpDATA)

    SELECT

    ROW_NUMBER() OVER(ORDER BY RealSort) as TmpID,

    RealDATA

    FROM

    dbo.RealTable

    ORDER BY

    RealSort;

  • November 14, 2011 at 8:32 am

    #1407218

    Here's the answer from Microsoft: http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx

    Relevant piece:

    4.INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 14, 2011 at 8:47 am

    #1407236

    I thought as much. Many thanks.

  • November 14, 2011 at 8:52 am

    #1407241

    Aside from identity, why does the order by of the insert matters?

    If you want order in the subsequent selects, you HAVE TO specify ORDER BY in the select.

    Tables have no order, so it makes no sense for inserts to have to honor an order (aside from identity).

  • November 14, 2011 at 8:54 am

    #1407246

    He asked about the ID sequence. That should do what's needed. ID can then be used in later Order By clauses to insure sequence.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 14, 2011 at 8:59 am

    #1407255

    GSquared (11/14/2011)

    He asked about the ID sequence. That should do what's needed. ID can then be used in later Order By clauses to insure sequence.

    Clustered PK on ID as well for a risky implicit sort ;-).

    Since I use those tables once when I have 1 row in it it would work :hehe:.

  • November 14, 2011 at 9:02 am

    #1407262

    Ninja's_RGR'us (11/14/2011)

    Aside from identity, why does the order by of the insert matters?

    If you want order in the subsequent selects, you HAVE TO specify ORDER BY in the select.

    Tables have no order, so it makes no sense for inserts to have to honor an order (aside from identity).

    Its complicated. (isn't it always). But I've simplified the example to isolate the specific point in question. The actual insert happens in 3 chunks, with 3 different sort fields, and a developer has been lazy and instead of putting all 3 sort fields in the temp table, he's used this IDENTITY construct assuming it would give the correct sequence in the end. - Which it does on our QA machines - but not in production.

  • November 14, 2011 at 9:06 am

    #1407271

    Tom Brown (11/14/2011)

    Ninja's_RGR'us (11/14/2011)

    Aside from identity, why does the order by of the insert matters?

    If you want order in the subsequent selects, you HAVE TO specify ORDER BY in the select.

    Tables have no order, so it makes no sense for inserts to have to honor an order (aside from identity).

    Its complicated. (isn't it always). But I've simplified the example to isolate the specific point in question. The actual insert happens in 3 chunks, with 3 different sort fields, and a developer has been lazy and instead of putting all 3 sort fields in the temp table, he's used this IDENTITY construct assuming it would give the correct sequence in the end. - Which it does on our QA machines - but not in production.

    No order by in the final select?

Viewing 9 posts - 1 through 8 (of 8 total)

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