Home Forums Article Discussions Article Discussions by Author Discuss content posted by ron.carpenter What identity comes next? RE: What identity comes next?

  • January 9, 2015 at 9:24 am

    #1769131

    BI_NewBie (1/9/2015)

    Hi,

    i have trouble in understanding the solution.

    So as we have seen in the first table

    CREATE TABLE #Temp1

    (

    Temp1ID int NOT NULL IDENTITY (-2147483648,1),

    Temp1Value char(1) NOT NULL

    ) ;

    INSERT INTO #Temp1 (Temp1Value) VALUES ('1') ;

    INSERT INTO #Temp1 (Temp1Value) VALUES ('2') ;

    SET IDENTITY_INSERT #Temp1 ON ;

    INSERT INTO #Temp1 (Temp1ID,Temp1Value) VALUES (100,'3') ;

    SET IDENTITY_INSERT #Temp1 OFF ;

    INSERT INTO #Temp1 (Temp1Value) VALUES ('4') ;

    after we set identity insert off, the identity value start from 101

    but similarly in the second table after identity insert off, still the next value is showing -3 not 101??

    what is the difference in these two?

    Thanks

    In the second table the identity values are decreasing. This means the next value will be -3 since the identity value you inserted (100) is "less" than the next value to be inserted. I put less in quotes because we know 100 is not actually less than -3.