Create a sequence of number

I revised my clarification. Considering the search value as 300 and as per the below data already available in table

• (30000, 80000)--300 will fit(30000-80000=50000) where 300 sequence of numbers are available

• (700, 4000) --300 will fit (700-4000=3300) where 300 sequence numbers are available

• (9000, 120000)--300 will fit(9000-12000=3000) where 300 sequence numbers are available

• (100, 600)--300 will fit (100-600=500) where 300 sequence numbers are available and it should create a block based on this and update this entry

• (4000, 5500)--300 will fit (4000-5500=1500) where 300 sequence numbers are available

it should create a new entry and update the existing block where the search value fits

(30000, 80000) 
(700, 4000)
(9000, 120000)
(300, 600) - Updated the existing entry (100, 600)
(4000,5500)
(100,300) - created a new entry for 300 blocks

Once again if the search value is 500 on the same table

• (30000, 80000)--500 will fit here because 30000 - 80000 = 50000 where 500 sequence of numbers are available

• (700, 4000)--500 will fit here because 700-4000 = 3300 where 500 sequence numbers are available and here it has to create the block because this is the least available block to fit 500

• (9000, 120000)--500 will fit here because 9000 - 12000 = 300 where 500 sequence of numbers are available

• (300, 600) --500 will not fit here 300-600=300 where 500 sequence numbers is not available

• (4000,5500)--500 will fit here 4000-55000=1500 where 500 sequence numbers are available but it should not create the block based on this row.- As 500 will fit in (700, 4000)

• (100,300) --500 will no fit here 100-300 where 500 sequence number is not available

it should create a new entry and update the existing block where the search value fits

(30000, 80000)
(1200, 4000)- Updated the existing entry (700, 4000)
(9000, 120000)
(300, 600) 
(4000,5500)
(100,300) 
(700, 1200)- created a new entry for 500 blocks

Please ignore the blow...

I am working on creating sequence of numbers to be generated and update the table(SQL Server). Could you please guide me on how to write a query for this scenario/logic

Table Name: NumberBlocks

1. If user is asking 300 number sequence then it will check the table and find the 300 will fit in which row. In this case 300 will fit in all the cases but it should 1st check the least block and update and create a new entry
2. If user is asking 5000 number sequence then it will check the table and find the 5000 will fit in which row. In this case 5000 will fit in all the cases but it should 1st check the least block and update and create a new entry

I am using Max() and Min() of the table but it will only take what is the max.

Based on the details of your 17:53:29Z post and your 21:09:02Z question updates, the range-end value appears to be exclusive, so we are dealing with half-open intervals. These are common in computer science and actually make some aspects of the range allocation logic simpler.

Since we are storing unallocated and allocated ranges in the same table, we will need an IsAllocated flag. Otherwise, repeated requests for the same size allocation will repeatedly allocate the same range. I have also taken the liberty of renaming the columns to something more meaningful to make the code easier to follow.

The following will find lowest available block of sufficient size and (if found) will either allocate the entire range or split the range into allocated and residual unallocated parts. If the requested range is greater than the largest available block size or is <= 0, no block is allocated and a null result is returned,

DECLARE @Requested INT = 200

-- Find lowest available block of sufficient size
DECLARE @SelectedStart INT
DECLARE @SelectedEnd INT

SELECT TOP 1 @SelectedStart = SequenceStart, @SelectedEnd = SequenceEndExclusive
FROM Sequences
WHERE SequenceEndExclusive - SequenceStart >= @Requested
AND IsAllocated = 0
AND @Requested > 0  -- Sanity check
ORDER BY SequenceStart

-- If found, either allocate the entire range or split the range into
-- allocated and residual unallocated parts
IF @SelectedStart IS NOT NULL
BEGIN
    IF (@SelectedEnd - @SelectedStart = @Requested)
    BEGIN
        -- Allocate entire range
        UPDATE Sequences
        SET IsAllocated = 1, Notes = 'Allocated entire range'
        WHERE SequenceStart = @SelectedStart
    END
    ELSE BEGIN
        -- Update existing range to reflect residual portion
        UPDATE Sequences
        SET SequenceStart = SequenceStart + @Requested, Notes = 'Residual'
        WHERE SequenceStart = @SelectedStart

        -- Insert new allocated range
        INSERT Sequences (SequenceStart, SequenceEndExclusive, IsAllocated, Notes)
        VALUES (@SelectedStart, @SelectedStart + @Requested, 1, 'Added and Allocated')
    END
END

-- Show Results
SELECT @Requested AS Requested, @SelectedStart AS AllocatedStart
SELECT * FROM Sequences

(The Notes logic and SELECT * FROM Sequences are present for demo purposes and can be removed prior to actual use.)

Sample results:

See this db<>fiddle for a demo.

There might also be a need for a deallocation mechanism. That could simply set IsAllocated to 0, but should likely also include cleanup logic for merging adjacent (preceding and/or following) unallocated ranges.