To bring this story to a temporarily end. I tried to verify the previous shown behaviour

with up to 500.000.000 Rows in #testEnvironment. (timeconsuming business !!, had to move transaction log

because...

To offer a complete environment inc. TimeStamps:

IF object_id('tempdb..#testEnvironment2') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment2

END

CREATE TABLE #testEnvironment2

(ID INTEGER NOT NULL PRIMARY KEY

CHECK (ID > 0));

;WITH t1 AS...

What about another unspectacular solustion:

SELECT ISNULL((SELECT TOP 1 RowNumber as FirstUnusedID

FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID )

AS RowNumber,ID FROM #testEnvironment2)T

WHERE RowNumber <> ID

ORDER BY 1),(ISNULL((SELECT MAX(ID) +...