I had some trouble understanding the explanation of the correct answer at first. The explanation for the answer states, "SQL Server will use the maximum identity value + increment in the direction of the increment for the next identity."

I'm not sure if there is any better way to word it. But I first thought "maximum identity value" was intended to mean 100 in example 2 (for example, SELECT MAX(Temp2ID) FROM #Temp2). Then I realized that the intended meaning was something like, "the value farthest in the direction of the increment value's sign." Or, in terms of SQL: MAX(ID) + 1 if the increment is >= 1, MIN(ID) - 1, if the increment is <= -1.

Here is the sample code with two additional queries I wrote to help me understand it.

CREATE TABLE #Temp1

(

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

Temp1Value char(1) NOT NULL

) ;

CREATE TABLE #Temp2

(

Temp2ID int NOT NULL IDENTITY (-1,-1),

Temp2Value 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 ;

-- Query to show current and next increment values.

SELECT MAX(Temp1ID) AS CURRENT_INCREMENT_VALUE, MAX(Temp1ID) + 1 AS NEXT_INCREMENT_VALUE FROM #Temp1;

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

INSERT INTO #Temp2 (Temp2Value) VALUES ('1') ;

INSERT INTO #Temp2 (Temp2Value) VALUES ('2') ;

SET IDENTITY_INSERT #Temp2 ON ;

INSERT INTO #Temp2 (Temp2ID,Temp2Value) VALUES (100,'3') ;

-- Query to show current and next increment values.

-- Used adding a negative integer to demonstrate "increment" of -1.

SELECT MIN(Temp2ID) AS CURRENT_INCREMENT_VALUE, MIN(Temp2ID) + (-1) AS NEXT_INCREMENT_VALUE FROM #Temp2;

SET IDENTITY_INSERT #Temp2 OFF ;

INSERT INTO #Temp2 (Temp2Value) VALUES ('4') ;

SELECT

Temp1ID,Temp2ID

FROM

#Temp1 t1

INNER JOIN #Temp2 t2

ON t1.Temp1Value = t2.Temp2Value

WHERE

t1.Temp1Value = '4' ;

DROP TABLE #Temp1 ;

DROP TABLE #Temp2 ;

But it is good that SQL Server doesn't work in such a way as to make the wrong answer that I picked (101,99) correct. :w00t: Otherwise, as others have noted, you would end up with ID collisions when using negative increments.

Thanks,

webrunner