Total Page Preview:   000000000321

Find missing id in SQL Server without loop.

In this article, I will explain how we can get all missing id without loop using a recursive function in SQL server.

 

Find missing id in SQL Server without loop.

Find missing id in SQL Server without loop using recursive CTC.

----Declare local variable-----
DECLARE @MinValue INT, @MaxValue INT
 
----Drop temp table if alread created-----
DROP TABLE IF EXISTS #TmpData
 
----Create temp table-----------
CREATE TABLE #TmpData(Id INT)
 
----Insert id in temp table---------
INSERT INTO #TmpData(Id) VALUES(1),(3),(5),(8),(10)
 
-----Get min and max id from temp table-----
SELECT   @MinValue = MIN(Id), @MaxValue =  MAX(Id)   FROM #TmpData
 
----generate serial number using recursive ctc-----
;WITH NumberSequence(Number) AS
(
    SELECT @MinValue AS Number
        UNION ALL
    SELECT Number + 1 FROM NumberSequence WHERE Number < @MaxValue
)
 
------Get all missing Id's -------
SELECT Number, IIF(T.Id IS NULL, 'Missing', 'Not Missing') AS IsMissing 
FROM NumberSequence A
LEFT JOIN #TmpData T ON A.Number = T.Id

 

OUTPUT:

Find missing id in SQL Server without loop.
 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments


                           
                           

                           

                           

Facebook User: