When someone says, “Find all the rows that have been deleted,” it’s a lot easier when the table has an Id/Identity column. Let’s take the Stack Overflow Users table:

It has Ids -1, 1, 2, 3, 4, 5 … but no 6 or 7. (Or 0.) If someone asks you to find all the Ids that got deleted or skipped, how do we do it?

Using GENERATE_SERIES with SQL Server 2022 & NewerThe new GENERATE_SERIES does what it says on the tin: generates a series of numbers. We can join from that series, to the Users table, and find all the series values that don’t have a matching row in Users:

DECLARE @FirstId INT, @LastId INT;SELECT @FirstId = MIN(Id),@LastId = MAX(Id)FROM dbo.Users;SELECT gs.valueFROM GENERATE\_SERIES(@FirstId, @LastId, 1) gsLEFT OUTER JOIN dbo.Users u ON gs.value = u.IdWHERE u.Id IS NULL; The LEFT OUTER JOIN seems a little counter-intuitive the first time you use it, but works like a champ:

What’s that, you ask? Why does GENERATE_SERIES have fuzzy underlines? Well, SQL Server Management Studio hasn’t been updated with the T-SQL syntax that came out in the last release.

Thankfully, Microsoft separated the setup apps for SSMS and the SQL Server engine itself for this exact reason – the slow release times of SSMS were holding back the engine team from shipping more quickly, so they put the less-frequently-updated SSMS out in its own installer.

(Did I get that right? Forgive me, I’m not a smart man.)

Using Numbers Tables with Older VersionsIf you’re not on SQL Server 2022 yet, you can create your own numbers table with any of these examples. Just make sure your numbers table has at least as many rows as the number of Ids you’re looking for. Here’s an example with a 100,000,000 row table:

DROP TABLE IF EXISTS dbo.Numbers;CREATE TABLE dbo.Numbers (Number INT PRIMARY KEY CLUSTERED); INSERT INTO dbo.Numbers(Number)SELECT TOP 10000000 row\_number() over(order by t1.number) as NFROM master..spt\_values t1 CROSS JOIN master..spt\_values t2 CROSS JOIN master..spt\_values t3GO Then, we’ll use that in a way similar to GENERATE_SERIES:

DECLARE @FirstId INT, @LastId INT;SELECT @FirstId = MIN(Id),@LastId = MAX(Id)FROM dbo.Users;SELECT n.NumberFROM dbo.Numbers nLEFT OUTER JOIN dbo.Users u ON n.Number = u.IdWHERE u.Id IS NULL AND n.Number > @FirstId AND n.Number < @LastIdORDER BY n.Number; That produces similar results, but not identical:

What’s different? Well, this method didn’t include 0! When I populated my numbers table, I only built a list of positive integers. The single most common mistake I see when using numbers tables is not having thorough coverage of all the numbers you need. Make sure it goes as low and as high as the values you need – a problem we don’t have with GENERATE_SERIES, since we just specify the start & end values and SQL Server takes care of the rest.

If you’d like to dive deeper into other ways to solve this problem, Itzik Ben-Gan’s chapter on Gaps & Islands will be right up your alley. Me, though, I’ll call it quits here because I’m in love with GENERATE_SERIES to solve this problem quickly and easily. Also, I’m lazy.