Post reply

Order by While inserting

  • February 15, 2012 at 3:37 pm

    #1447591

    ScottPletcher (2/15/2012)

    I thought it depended on whether SQL thought it was "necessary" or not.

    Whether the number of rows, etc., met whatever magic criteria SQL went by -- which are not documented and can change from release to release.

    The real key is, "it doesn't matter". The only thing that matters is how to control the numbering of the IDENTITY column during inserts if that's important. For existing tables, it has been demonstrated that an ORDER BY will work if the ORDER BY values are determinent. For SELECT/INTO it has been demonstrated that an ORDER BY will work if the ORDER BY values are determinent and parallalism is prevented using MAXDOP.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 15, 2012 at 3:55 pm

    #1447602

    ScottPletcher (2/15/2012)

    IIRC, a single insert of many rows will be done in the clustered index order auto-magically.

    Really? Interesting. I don't recall that. Would be nice tho.

    Yup, does happen. I wouldn't call it 'auto-magically', the optimiser just drops a sort into the plan

    CREATE TABLE InsertOrder (

    SomeKey UNIQUEIDENTIFIER,

    Filler CHAR(5)

    )

    CREATE CLUSTERED INDEX idx_TestingOrder ON InsertOrder (SomeKey)

    go

    INSERT INTO InsertOrder

    SELECT NEWID(), LEFT(name,5) FROM sys.columns AS c

    View the exec plan of that insert and there's a sort just before the clustered index insert that orders the resultset by the NewID.

    Exercise for the readers: toss a nonclustered index onto the other column and see how the plan changes (hint: sorts, plural)

    Will it happen in every single case? Probably not, I'm sure there are several optimisations for particular cases.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 15, 2012 at 4:33 pm

    #1447610

    I wouldn't call it 'auto-magically',

    That's primarily what I meant. I thought it was conditional. I've seen SQL not do it.

    To me it's much safer to code your own ORDER BY for that.

    I would expect SQL to do sorts for the other indexes for any significant number of rows, for the same reason -- to avoid too many random mods and page splits.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • February 16, 2012 at 1:51 am

    #1447694

    ScottPletcher (2/15/2012)

    I wouldn't call it 'auto-magically',

    That's primarily what I meant. I thought it was conditional. I've seen SQL not do it.

    To me it's much safer to code your own ORDER BY for that.

    I wouldn't agree. If a sort is needed, one will be added. If there's some optimisation of a particular case where a sort isn't needed, then explicitly adding one may make the query less efficient.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 16, 2012 at 8:12 am

    #1447912

    When did SQL Server start doing these "auto sorts" for clus indexes?

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • February 16, 2012 at 2:11 pm

    #1448153

    ScottPletcher (2/16/2012)

    When did SQL Server start doing these "auto sorts" for clus indexes?

    Common sense tells me it was like this since the day clustered indexes were implemented in SQL Server.

    Alex Suprun

Viewing 6 posts - 31 through 35 (of 35 total)

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