We have current table named Article:

id is a numeric(5, 0)

Its very important that similar articles have very similar IDs, so my client wants to see list of all the possible (currently unused) id numbers when he creates a new article record. That way they can look at a range that fits for current article creation.

How can I do this in SQL Server?

CodePudding user response：

One possible solution

Declare @YourTable Table ([id] int,[name] varchar(50))  Insert Into @YourTable Values 
 (1,'aaa')
,(2,'bbb')
,(3,'ccc')
,(25,'ddd')
,(50,'eee')


Select R1 = min(N)
      ,R2 = max(N)
 From  (
        Select N
              ,Grp = N-row_number() over (order by N)
         From  (
                Select Top 99999 N=Row_Number() Over (Order By (Select NULL)) 
                 From  master..spt_values n1, master..spt_values n2
               ) src
         where not exists (Select 1 from  @YourTable where N=id)
       ) A
 Group By Grp 

Results of Available IDs

R1  R2
4   24
26  49
51  99999

Note:

Subquery A will give you a long list of open ID's