Find an available range from a table in SQL Server

Question

I have a table that contains the "Start and End" columns, like INT. I need to find the very first available range Ncompared to existing data.

Table layout :

CREATE TABLE [dbo].[MSRange]
(
    [RangeId] [int] IDENTITY(1,1) NOT NULL,
    [RangeStart] [int] NOT NULL,
    [RangeEnd] [int] NOT NULL,
    CONSTRAINT [PK_MSRange] 
       PRIMARY KEY CLUSTERED ([RangeId] ASC)
) ON [PRIMARY]

Sample Data:

INSERT INTO [dbo].[MSRange] ([RangeStart], [RangeEnd])
VALUES (1, 150), (1250, 1500), (3100, 7500), (10500, 15000);

Requirements:

I need to find a placeholder for 1000 slots, obviously, from the given seeds, which we can say is available from 151 to 1150. Similarly, for 1,500 slots, 1501-3000 are available.

Please help me how to get the first placeholder available.

Answer 1

declare @MSRange table
(
    [RangeId] [int] IDENTITY(1,1) NOT NULL primary key,
    [RangeStart] [int] NOT NULL,
    [RangeEnd] [int] NOT NULL
) 

INSERT INTO @MSRange ([RangeStart], [RangeEnd])
VALUES (1, 150), (1250, 1500), (3100, 7500), (10500, 15000);

declare @N int = 1000;

with cte as
(
select RangeId, 
       RangeEnd as result_range_start, 
       isnull(lead(RangeStart) over(order by RangeId), 2147483647) as result_range_end

from @MSRange
)

select top 1 result_range_start + 1, result_range_start + @N
from cte
where result_range_end - result_range_start > @N
order by RangeId;

, 2012. @@version <= 2008 R2 , row_number() ,