Is Identity guaranteed sequence number when mass inserting

  • Using something like

    CREATE TABLE tab1 (

    ID INT Identity(1,1)

    , Col1 int

    )

    INSERT INTO tab1

    SELECT Col1 FROM tab2

    Does it guaranteed me that new rows inserted in tab1 (from tab2) will be inserted in the same order as the "SELECT Col1 FROM tab2" return them?

    Will I be able to do the following match after:

    First row from "SELECT Col1 FROM tab2" = first new row inserted in tab1

    Second row from "SELECT Col1 FROM tab2" = second new row inserted in tab1

    Ex: tab 1 MAX ID is 4 (next Identity value is 5)

    If I insert 4 rows in tab1,

    Will tab1 rows 5 match "SELECT Col1 FROM tab2" row 1

    and tab1 rows 6 match "SELECT Col1 FROM tab2" row 2

    and tab1 rows 7 match "SELECT Col1 FROM tab2" row 3

    and tab1 rows 8 match "SELECT Col1 FROM tab2" row 4 ?

    I've read BOL and found nothing on that. I know about Identity gap when a rollback occurs but I'm unsure about that case if something can, while SQL is processing the mass insert, modify the ID sequence generated.

  • I've never seen a situation where it didn't unless the clustered indexing was changed (since you don't have an order by) or there's something strange going on with the identity settings.

    So, in theory, yes, you should be able to do:

    select a1.id, b2.id

    FROM tab1 AS a1

    JOIN tab2 AS b2

    ON a1.id - 4 = b2.id

    This would be hell on your indexing but it should be consistent, unless someone reseeds when you're not looking... 😉


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Megistal (9/7/2010)


    Using something like

    CREATE TABLE tab1 (

    ID INT Identity(1,1)

    , Col1 int

    )

    INSERT INTO tab1

    SELECT Col1 FROM tab2

    Does it guaranteed me that new rows inserted in tab1 (from tab2) will be inserted in the same order as the "SELECT Col1 FROM tab2" return them?

    Will I be able to do the following match after:

    First row from "SELECT Col1 FROM tab2" = first new row inserted in tab1

    Second row from "SELECT Col1 FROM tab2" = second new row inserted in tab1

    Ex: tab 1 MAX ID is 4 (next Identity value is 5)

    If I insert 4 rows in tab1,

    Will tab1 rows 5 match "SELECT Col1 FROM tab2" row 1

    and tab1 rows 6 match "SELECT Col1 FROM tab2" row 2

    and tab1 rows 7 match "SELECT Col1 FROM tab2" row 3

    and tab1 rows 8 match "SELECT Col1 FROM tab2" row 4 ?

    I've read BOL and found nothing on that. I know about Identity gap when a rollback occurs but I'm unsure about that case if something can, while SQL is processing the mass insert, modify the ID sequence generated.

    The short answer to this is Yes.

    The longer answer is that since your SELECT Col1 FROM tab2 doesn't have an order by clause, there is no guaranteed order - SQL will get your results in the most efficient way that it can.

    If you want to guarantee that the records match on their ID column, then look into using SET IDENTITY_INSERT tab1 ON, and insert the values from tab2: INSERT INTO tab1 (ID, Col1)

    SELECT ID, Col1 FROM tab2

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thanks to both of you! This will help me very much!

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

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