Jan 20

Next available number in T-SQL

T-SQL No Comments »

A collegue of mine at work needed to find the next available ID for a non-identity column in a table.  For instance, if the rows in the table have 1, 2, 3, 6, and 10 in the table, then the query would return 4.  I found the following code snippet (modified for testing) by Steve Baldwin at this location: 

Declare @Test Table
(
    ID int
    ,[Description] varchar(50)
);

Insert Into @Test Values (1, 'Row 1');
Insert Into @Test Values (2, 'Row 2');
Insert Into @Test Values (3, 'Row 3');
Insert Into @Test Values (4, 'Row 4');
Insert Into @Test Values (8, 'Row 8');
Insert Into @Test Values (9, 'Row 9');
Insert Into @Test Values (11, 'Row 11');
Insert Into @Test Values (12, 'Row 12');
Insert Into @Test Values (13, 'Row 13');
Insert Into @Test Values (14, 'Row 14');

Select
    Min(ID) + 1
From
    @Test a
Where
    Not Exists (
        Select
            0
        From
            @Test b
        Where
            b.ID = a.ID + 1
    )
Tags: