Seems like a lot of processing overhead for no real gain.. but, here are two ways to do that:DECLARE @Foo TABLE (TempID VARCHAR(20))INSERT @FooVALUES('ID0001'),('ID0002'),('ID0003'),('ID0006')-- Method 1;WITH Tens (N) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9), Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)SELECT MIN(T.N)FROM Tally AS TLEFT OUTER JOIN @Foo AS F ON T.N = CAST(STUFF(TempID, 1, 2, '') AS INT)WHERE F.TempID IS NULL-- Method 2SELECT MIN(CAST(STUFF(TempID, 1, 2, '') AS INT) + 1) AS NewIDNumberFROM ( SELECT *, LEAD(TempID, 1) OVER (ORDER BY TempID) NextID FROM @Foo ) AS TWHERE CAST(STUFF(NextID, 1, 2, '') AS INT) - CAST(STUFF(TempID, 1, 2, '') AS INT) > 1 OR NextID IS NULL