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 ;

Select Sign(Ident_Incr('tablename')) * Max(Sign(Ident_Incr('tablename')) * $IDENTITY) + Ident_Incr('tablename')
From tablename