Re-using Key values (finding the gaps)

Suppose you do physical deletes of data and are in a position to re-use the ‘keys’ for a table. You can use the technique below to find gaps in the key for your table (assuming you are using a single numerical key).

— LET’S USE TEMPDB
USE tempdb;
GO

— DROP THE TABLE IF IT ALREADY EXISTS
IF OBJECT_ID(‘dbo.SampleOrders’) IS NOT NULL
DROP TABLE dbo.SampleOrders;
GO

— CREATE THE TABLE AND POPULATE IT
CREATE TABLE dbo.SampleOrders
(
OrderId INT NOT NULL PRIMARY KEY,
OrderDate DATETIME NOT NULL
);
INSERT INTO dbo.SampleOrders(OrderId, OrderDate) VALUES(2, ’15 Aug 2006′);
INSERT INTO dbo.SampleOrders(OrderId, OrderDate) VALUES(3, ’15 Aug 2006′);
INSERT INTO dbo.SampleOrders(OrderId, OrderDate) VALUES(5, ’16 Aug 2006′);
INSERT INTO dbo.SampleOrders(OrderId, OrderDate) VALUES(6, ’19 Aug 2006′);
INSERT INTO dbo.SampleOrders(OrderId, OrderDate) VALUES(8, ’19 Aug 2006′);

— NOW WE CAN SELECT THE MINIMUM MISSING KEY (OrderId), IN THIS CASE 1 WILL BE RETURNED
SELECT CASE WHEN NOT EXISTS(SELECT *
FROM dbo.SampleOrders
WHERE OrderId = 1) THEN 1
ELSE (SELECT MIN(A.OrderId + 1)
FROM dbo.SampleOrders AS A
WHERE NOT EXISTS
(SELECT * FROM dbo.SampleOrders AS B
WHERE B.OrderId = A.OrderId + 1))
END;

— NOW LET’S INSERT 1 TO SEE IF OUR INNER QUERY WILL RETURN THE CORRECT VALUE (4)
INSERT INTO dbo.SampleOrders(OrderId, OrderDate) VALUES(1, ’11 Aug 2006′);

— NOW THIS SAME QUERY SHOULD RETURN 4
SELECT CASE WHEN NOT EXISTS(SELECT *
FROM dbo.SampleOrders
WHERE OrderId = 1) THEN 1
ELSE (SELECT MIN(A.OrderId + 1)
FROM dbo.SampleOrders AS A
WHERE NOT EXISTS (SELECT *
FROM dbo.SampleOrders AS B
WHERE B.OrderId = A.OrderId + 1))
END;

— CLEANUP THE SAMPLEORDERS
IF OBJECT_ID(‘dbo.SampleOrders’) IS NOT NULL
DROP TABLE dbo.SampleOrders;
GO

It’s a fairly easy way to find gaps working from the lowest gap up. Thanks to Itzik Ben-Gan for pointing it out in his book: Inside Microsoft SQL Server 2005: T-SQL Querying.

Note, although the use of * is generally advised against, it is safe to use it in EXISTS or NOT EXISTS queries as the optimizer ignores the SELECT arguments.