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.

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s