I have a @Tab table, which has an ID column. In this column, some values are missing between the first and last value of the column.
Now, I will try to find out the all the missing values between 1 and 16.
DECLARE @Max int;
Declare @Min int;
SET @Min=(SELECT MIN(t.Id) FROM @Tab t);
SET @max=(SELECT MAX(t.Id) FROM @Tab t);
WITH CTE AS
(SELECT @Min AS Col
SELECT COl+1 as t FROM CTE c
SELECT * FROM CTE WHERE CTE.Col NOT In(SELECT Id FROM @Tab t);
This query returns 4 values: 3, 6, 9, 14. These four values are missing in ID column of @Tab.
HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.