How can I find next available value in a table in the fastest way-CodePudding

I have a table that contains a varchar column which is indexed. The values in this column consist of a prefix and an incrementing number value. It is not necessary the values will be in order.

ABC00010
ABC00011
ABC00015
ABC00012
ABC00017
ABC00016
and so on...

There may be missing values in the sequence. How can I find the smallest number available for insert?

I wrote this and it works. But it takes a few seconds when the numbers are in the thousands.

Declare @C int = 1;
While Exists(Select 1 From MyTable Where Col='ABC' Format(@C,'00000')) Set @C=@C 1;
Select 'Next Number: ABC' Format(@C,'00000');

Is there a faster way?

CodePudding user response：

Always think set-based operations. A While loop is not set-based. Here I create a CTE with sequential numbers from 0 through 99999. Then I match the sequence CTE to the "MyTable", order the list, and select a TOP 1 from a RIGHT JOIN.

DECLARE @myTable TABLE (
    Col nvarchar(8)
);

INSERT INTO @myTable
VALUES ('ABC00001'), ('ABC00002'), ('ABC00005'), ('ABC00003')
;

WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
, y as (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as number
    FROM x ones, x tens, x hundreds, x thousands, x tThousands
)
, yMod as (
    SELECT y.number
        , 'ABC'   RIGHT('00000'   CAST(y.number as nvarchar), 5) as Col
    FROM y 
)
SELECT TOP 1
    ym.Col
FROM @myTable as mt
    RIGHT OUTER JOIN yMod as ym
        ON ym.Col = mt.Col
WHERE mt.Col IS NULL
ORDER BY ym.number

CodePudding user response：

A better option would be to not delete records but mark them as deleted. Use a BIT column as "deleted" flag for this. Then you can easily select the smallest deleted record and re-use it.

If no one was found, select the largest one and add 1 to the index. Or, even better, use a

CodePudding user response：

Something like this?

Declare @testData table (Col varchar(20));
 Insert Into @testData (Col)
 Values ('ABC00001'), ('ABC00002'), ('ABC00012'), ('ABC00013')
      , ('XYZ00002'), ('XYZ00003'), ('XYZ00010'), ('XYX00012');

Declare @prefix char(3) = 'XYZ';

   With gaps
     As (
 Select *
      , grp = v.inc - row_number() Over(Order By v.inc)
   From @testData As td
  Cross Apply (Values (replace(td.Col, @prefix, ''))) As v(inc)
  Where td.Col Like @prefix   '%'
        )
 Select Top 1
        next_value = concat(@prefix, right(concat('00000', max(g.inc)   1), 5))
   From gaps As g
  Group By
        g.grp
  Order By
        next_value;