Weryfikacja ciągłości zakresu, identyfikacja dziur oraz scalanie przedziałów, przydaje się do rozwiązywania różnych zadań. W systemach transakcyjnych, szczególnie mocno obciążonych przez wielu użytkowników, mogą wystąpić sytuacje gdy funkcja IDENTITY nie zapewnia bezwzględnej ciągłości identyfikatorów. Dotyczy to również obiektów wprowadzonych w SQL Server 2012, znanych już od dawna w Oracle – sekwencji.

Artykuł ten, nie będzie jednak o bezpiecznym nadawaniu kolejnych, ciągłych identyfikatorów (polecam triggery INSTEAD OF INSERT). Zaprezentuję tu kilka wskazówek i praktycznych przykładów jak wykrywać nieciągłości, identyfikować dziury oraz scalać zakresy.

Niezwykle pomocne okazują się tutaj funkcje szeregujące bazujące na funkcji okna OVER() oraz wspólne wyrażenia tablicowe.

Wykrywanie nieciągłości w zbiorze

Na początek coś bardzo prostego. Trochę podstaw, bez których trudno jest rozwiązywać bardziej skomplikowane przykłady.

Załóżmy, że mamy zbiór elementów ponumerowanych w zakresie od 1-1000, inkrementowanych co 1. Każdy ciągły, jednostajnie rosnący zbiór wartości, powinien zwrócić ten sam wynik dla takiego wyrażenia:

Wartość_elementu  – ( Inkrementacja * Nr_elementu_w_szeregu ) = Const.

Use tempdb
GO

set nocount on 
go

IF ( OBJECT_ID('dbo.zakres') is not null)  
drop table dbo.zakres;

create table dbo.zakres
( 
	Vlan int
);

DECLARE @zmienna int = 1

WHILE (@zmienna<=1000)
BEGIN
	insert into  dbo.zakres values (@zmienna)
	set @zmienna = @zmienna + 1
END;

-- kwerenda sprawdzająca
with CheckCont as (
	-- dla inkrementowanego o 1
	select distinct T1.VLAN  - (1*(ROW_NUMBER() OVER (ORDER BY  VLAN))) as Diff
	from dbo.zakres t1
)
Select CASE WHEN Count(Diff)> 1 then 'Discontinuous' else 'Continuous' end as Result
from CheckCont

Jak widać bez niespodzianek – jest ok. Skasujemy teraz kilka losowych rekordów i ponowie zweryfikujemy ciągłość elementów, tą samą kwerendą sprawdzającą.

DELETE FROM dbo.zakres
WHERE VLAN IN (
SELECT TOP 3 VLAN from dbo.zakres ORDER BY NEWID() 
)

Scalanie zakresów

Scenariusz, z którym ostatnio się spotkałem to scalanie zakresów VLAN w konfiguracji interfejsów urządzeń sieciowych (routerów). Informacje o VLANach są przechowywane w tabeli, w której każdy rekord, odpowiada pojedynczemu VLANowi, przypisanemu do danego portu, określonego urządzenia. To co trzeba wykonać, to agregacja (sklejenie zakresów) do skrótowej postaci przyjaznej dla użytkownika.

Wygenerujmy sobie prostą tabelę, z nieciągłą numeracją w kolumnie VLAN :

IF ( OBJECT_ID('dbo.zakres') is not null)  drop table dbo.zakres;
create table dbo.zakres
( 
	Vlan int
);

DECLARE @zmienna int = 1, @los int =0, @cnt int =1

WHILE (@cnt<=10)
BEGIN

	SET @los = case when   CONVERT(INT, (11) * RAND()) > 8  then 1 else 0 end

	insert into dbo.zakres values (@zmienna + @los)
	SELECT @zmienna = @zmienna +1+ @los, @cnt +=1
END;

select * from dbo.zakres

Zadaniem jest wykrycie i scalenie zakresów rekordów o ciągłej numeracji. Stosując się do reguły ciągłości, którą pokazałem w poprzednim przykładzie, sprawdźmy rezultat tej formułki dla kolejnych wierszy. Widać, że wynik może posłużyć jako bezwzględny identyfikator określonego, ciągłego zakresu. W naszym przypadku mamy 4 zakresy, w tym dwa wieloelementowe.

Kluczowym znów będzie numerowanie rekordów i późniejsze ich grupowanie względem wyliczonego w ten sposób identyfikatora zakresu. Dwa podejścia – stare, sprzed SQL Server 2005 z zapytaniem skorelowanym :

with PoNumerowane as
(
	select VLAN ,  
           (select count(*) from dbo.zakres t2 where t2.VLAN  <= t1.VLAN )  as IdRek
	from dbo.zakres t1
)
select VLAN - IdRek as ZakresID, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from PoNumerowane
group by VLAN - IdRek 

Oraz lepsze, zdecydowanie bardziej wydajne, rozwiązanie z użyciem funkcji ROW_NUMBER() :

with Ponumerowane as (
	select VLAN , ROW_NUMBER() OVER(order by VLAN)  as IdRek,
	 VLAN - ROW_NUMBER() OVER(order by VLAN) as IdZakres
	from dbo.zakres t1 
)
select IdZakres, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from Ponumerowane
group by IdZakres

Takie rozwiązanie można ubrać w funkcję użytkownika i prezentować scalone zakresy liczb w przyjaznej formie, np. dodatkowo agregując je do postaci skalarnej (FOR XML PATH).

Wyszukiwanie brakujących elementów w zbiorze

Na koniec sytuacja, gdy interesuje nas to, czego nie ma w tabeli. Mogą to być braki numerów zamówień, albo dziury w ciągłym zakresie adresacji urządzeń IP, nadające się do ponownego przydzielenia. Identyfikację brakujących elementów, na przykładzie zbioru inkrementowanego o 1, możemy wykonać na przykład tak :

with cte as (
	
	SELECT (select MIN(VLAN) from dbo.zakres) as VLAN, 
(select MAX(VLAN) from dbo.zakres) as MaxVal
	UNION ALL

	-- zakładamy inkrementację + 1 
	SELECT VLAN + 1 , MaxVal
	FROM CTE  c 
	where VLAN < MaxVal
)
select c.VLAN as MissingVlan from cte c left join dbo.zakres t on c.VLAN = t.VLAN
where t.VLAN is null
OPTION (MAXRECURSION 0)