Inside Microsoft® SQL Server™ 2005: T-SQL Querying

Existing and Missing Ranges (Also Known as Islands and Gaps)

To put your knowledge of subqueries, table expressions, and ranking calculations into action, I’ll provide a couple of problems that have many applications in production environments. I’ll present a generic form of the problem, though, so you can focus on the techniques and not the data.

Create and populate a table named T1 by running the code in Example 4-10.

Example 4-10. Creating and populating the T1 table

USE tempdb; GO IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1 GO CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY); INSERT INTO dbo.T1(col1) VALUES(1); INSERT INTO dbo.T1(col1) VALUES(2); INSERT INTO dbo.T1(col1) VALUES(3); INSERT INTO dbo.T1(col1) VALUES(100); INSERT INTO ...