The Data Game

Unikatowość w indeksie

Jak dobrze wiemy, czy to indeks nieklastrowy czy klastrowy (skupiamy się na indeksach “rowstore”) może zostać opatrzony opcjonalną klauzulą UNIQUE która definiuje go jako unikatowy, czyli nie posiadający duplikatów w kolumnach tworzących jego definicję (jednak duplikaty spokojnie mogą się znajdować wśród kolumn zawartych w klauzuli INCLUDE).

Przygotujmy sobie tabelę z takim indeksem i wypełnijmy ją poprawnymi danymi:

DROP TABLE IF EXISTS dbo.testDuplicates;
CREATE TABLE dbo.testDuplicates ([Id] INT NOT NULL IDENTITY(1,1) CONSTRAINT [PK_DupeId] PRIMARY KEY,
							  [Value] NVARCHAR(50) NULL,
							  INDEX [IX_UQ_NC_loneliness] UNIQUE NONCLUSTERED ([Value]));
INSERT INTO dbo.testDuplicates([Value])
VALUES
	('a'),
	('b'),
	('c'),
	('d'),
	('e');

Powyższy kod stworzy nam tabelę z unikatowym indeksem na kolumnie Value. Spróbujmy teraz dorzucić tam wiersz ze zduplikowaną wartości i zobaczmy co się stanie.

INSERT INTO dbo.testDuplicates([Value]) VALUES ('c')

Woah, jaki piękny błąd!

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.testDuplicates' with unique index 'IX_UQ_NC_loneliness'. The duplicate key value is (c).

I wszystko fajnie, jesteśmy uratowani! Jednak zostaje pytanie, co zrobi SQL Server z wartością NULL? Czy potraktuje ją specjalnie i pominie sprawdzenie unikatowości przy wrzucaniu więcej niż jednego wiersza? Pozwoli wrzucić tylko jeden wiersz? A może w ogóle nie pozwoli na NULLe? Sprawdźmy to!

INSERT INTO dbo.testDuplicates([Value]) VALUES (NULL);
INSERT INTO dbo.testDuplicates([Value]) VALUES (NULL);

Podzieliłem to na dwa inserty aby sprawdzić który z nich się wywróci. I co się zadziało?

(1 row affected)
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.testDuplicates' with unique index 'IX_UQ_NC_loneliness'. The duplicate key value is (<NULL>).

Pierwsze zapytanie wykonało się poprawnie (o czym świadczy komunikat “1 row affected“), jednak drugie już rzuciło nam przykrym błędem duplikatu wartości. Peszkie.

Co robić, jak żyć?

Przyjmijmy jednak, że z jednej strony potrzebujemy zachować unikatowość wartości, które mają znaczenie, a jednocześnie użytkownicy mają być w stanie umieścić tam tyle wartości NULL ile tylko sobie wymyślą. Jest na to pewien sposób i nazywa się “indeks filtrowany”. Dokładnie tak jak w zwykłym zapytaniu SELECT mamy klauzulę WHERE, tak samo może ona występować w obrębie tworzenia indeksu. Spójrzcie.

--Najpierw usuwamy istniejący indeks
DROP INDEX [IX_UQ_NC_loneliness] ON dbo.testDuplicates;
GO
--I zakładamy nowy, zwróćcie uwagę na klauzulę WHERE na końcu definicji
CREATE UNIQUE NONCLUSTERED INDEX [IX_UQ_NC_emptiness] ON dbo.testDuplicates([Value]) WHERE [Value] IS NOT NULL;
GO

Więc stworzyliśmy nowy indeks i powiedzieliśmy SQL Serverowi żeby utrzymywał go tylko dla wartości które nie są NULL. Jak to działa? Puśćmy ponownie zapytanie z wrzucaniem nieznanych wartości.

INSERT INTO dbo.testDuplicates([Value]) VALUES (NULL);
INSERT INTO dbo.testDuplicates([Value]) VALUES (NULL);

Tada! Oba zapytania wykonały się poprawnie, a wartości NULL zostały umieszczone w kolejnych wierszach tabeli. A co jakbyśmy chcieli umieścić prawdziwy duplikat?

INSERT INTO dbo.testDuplicates([Value]) VALUES ('a');

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.testDuplicates' with unique index 'IX_UQ_NC_emptiness'. The duplicate key value is (a).

No nie da się.

Oki, to pa!