Numer unikalny w dokumencie, ale usunięty można wpisać ponownie: indeks filtrowany

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Indeks unikalny filtrowany rozwiązuje konflikt między jednoznacznością numerów w dokumencie a miękkim usuwaniem wierszy: zwykły indeks unikalny liczy także wiersze usunięte, więc ponowny wpis tego samego numeru kończy się błędem 2601.

Indeks filtrowany, na przykład CREATE UNIQUE INDEX ... WHERE status = 'A' AND typ_numeru = 1, obejmuje tylko wiersze aktywne danego typu. Wiersz usunięty miękko nie bierze udziału w jednoznaczności, więc ten sam numer można wpisać ponownie, a duplikat numeru aktywnego nadal kończy się błędem 2601. Numery seryjne i partie dostają osobne indeksy, bo mają inny klucz.

To wzorzec z nazwami przykładowymi, opisujący tabelę numerów powiązanych z dokumentem. Uruchomiliśmy go na SQL Server 2022 Express w jednorazowej bazie przy dwóch poziomach zgodności, 130 i 150, a wyniki były identyczne. Indeksy filtrowane są dostępne od SQL Server 2008. Tabela ma tu inną postać niż w artykule o STRING_AGG w raporcie, bo to osobne przykłady.

Dlaczego zwykły indeks unikalny nie pasuje do miękkiego usuwania

Tabela numery_dokumentu przechowuje numery powiązane z dokumentem. Wiersz wskazuje dokument i pozycję, a kolumna typ_numeru przyjmuje trzy wartości opisane w tabeli. Wartość numeru ma typ nvarchar(100) i domyślnie jest pustym tekstem. Do tego dochodzą liczba oraz kolumna status: „A” oznacza wiersz aktywny, a „D” usunięty.

Przykład. Wiersz typu 3 ma pusty numer i nie bierze udziału w jednoznaczności.

CREATE TABLE dbo.numery_dokumentu (
    id_numeru      int IDENTITY(1,1) PRIMARY KEY,
    id_dokumentu   int           NOT NULL,
    id_pozycji     int           NOT NULL,
    typ_numeru     tinyint       NOT NULL,                 -- 1 = numer seryjny, 2 = partia, 3 = wiersz bez numeru
    wartosc_numeru nvarchar(100) NOT NULL DEFAULT N'',
    liczba         decimal(15,3) NOT NULL DEFAULT 0,
    status         char(1)       NOT NULL DEFAULT 'A');    -- 'A' = aktywny, 'D' = usunięty miękko

Przykład ilustracyjny. Nazwy tabeli i kolumn są wymyślone.

Usunięcie jest miękkie: wiersz dostaje status „D” i zostaje w tabeli, więc historię numerów dokumentu da się odtworzyć. Cena tej decyzji pojawia się przy indeksie. Zwykły indeks na (id_dokumentu, wartosc_numeru) obejmuje także wiersze usunięte, więc po miękkim usunięciu numeru jego ponowny wpis w tym samym dokumencie kończy się błędem 2601.

Ten sam indeks traktuje dwa puste teksty jako równe. Drugi wiersz typu 3 w tym samym dokumencie, wpisany dla innej pozycji, też kończy się błędem 2601, choć nikt nie wpisał żadnego numeru.

CREATE UNIQUE INDEX UX_zwykly ON dbo.numery_dokumentu (id_dokumentu, wartosc_numeru);

Przykład ilustracyjny. Zwykły indeks unikalny, którego używamy tylko do porównania.

Indeks filtrowany z warunkiem na status i typ numeru

Indeks filtrowany obejmuje wyłącznie wiersze spełniające warunek po słowie WHERE. W przykładzie warunek składa się z dwóch części: status = „A” i typ_numeru = 1 dla numerów seryjnych. Wiersze usunięte i wiersze typu 3 nie biorą więc udziału w jednoznaczności. Ponowny wpis po usunięciu przechodzi, a duplikat numeru aktywnego nadal daje błąd 2601.

Indeks filtrowany pilnuje jednoznaczności tylko wśród wierszy aktywnych, więc miękkie usunięcie zwalnia numer do ponownego użycia.

Seria w dokumencie, partia na pozycji

Dwa typy numerów to dwa indeksy z różnymi kluczami. Numer seryjny jest jednoznaczny w dokumencie, więc jego klucz to (id_dokumentu, wartosc_numeru). Partia jest jednoznaczna na pozycji, więc jej klucz zawiera także pozycję: (id_dokumentu, id_pozycji, wartosc_numeru). Ta sama partia na dwóch pozycjach przechodzi, a powtórzona na jednej pozycji daje 2601. Zwykły indeks (id_dokumentu, wartosc_numeru) odrzuca obie sytuacje, bo jego klucz nie zawiera pozycji.

CREATE UNIQUE INDEX UX_numer_seryjny ON dbo.numery_dokumentu (id_dokumentu, wartosc_numeru)
    WHERE status = 'A' AND typ_numeru = 1;
CREATE UNIQUE INDEX UX_numer_partii  ON dbo.numery_dokumentu (id_dokumentu, id_pozycji, wartosc_numeru)
    WHERE status = 'A' AND typ_numeru = 2;

Przykład ilustracyjny. Dwa indeksy filtrowane: numer seryjny w dokumencie, partia na pozycji.

Kusząca wydaje się propozycja jednego indeksu z filtrem typ_numeru IN (1, 2). Taki indeks wywołuje kolizję numeru seryjnego i partii o tym samym tekście, znów błędem 2601. Filtr z OR jest natomiast odrzucany błędem składni 156, a lista IN jest dozwolona.

Poniższa sekwencja siedmiu poleceń wykonuje się w jednej sesji SQL na dokumencie 1. Dwa z nich muszą zakończyć się błędem 2601, więc owijamy je w TRY ... CATCH i wypisujemy ERROR_NUMBER(). Pozostałe pięć przechodzi bez błędu; wśród nich jest miękkie usunięcie i ponowny wpis tego samego numeru.

-- 1. pierwszy numer seryjny w dokumencie 1
INSERT dbo.numery_dokumentu (id_dokumentu, id_pozycji, typ_numeru, wartosc_numeru, liczba)
VALUES (1, 10, 1, N'SN-000123', 1);

-- 2. ten sam numer, inna pozycja tego dokumentu
BEGIN TRY
    INSERT dbo.numery_dokumentu (id_dokumentu, id_pozycji, typ_numeru, wartosc_numeru, liczba)
    VALUES (1, 20, 1, N'SN-000123', 1);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS numer_bledu;                    -- 2601
END CATCH;

-- 3. usunięcie miękkie, 4. ponowny wpis tego samego numeru
UPDATE dbo.numery_dokumentu SET status = 'D' WHERE id_dokumentu = 1 AND wartosc_numeru = N'SN-000123';
INSERT dbo.numery_dokumentu (id_dokumentu, id_pozycji, typ_numeru, wartosc_numeru, liczba)
VALUES (1, 10, 1, N'SN-000123', 1);

-- 5. dwa wiersze typu 3 (pusty numer) na różnych pozycjach
INSERT dbo.numery_dokumentu (id_dokumentu, id_pozycji, typ_numeru) VALUES (1, 10, 3), (1, 20, 3);

-- 6. ta sama partia na dwóch pozycjach
INSERT dbo.numery_dokumentu (id_dokumentu, id_pozycji, typ_numeru, wartosc_numeru, liczba)
VALUES (1, 10, 2, N'PART-A', 5), (1, 20, 2, N'PART-A', 3);

-- 7. ta sama partia drugi raz na jednej pozycji
BEGIN TRY
    INSERT dbo.numery_dokumentu (id_dokumentu, id_pozycji, typ_numeru, wartosc_numeru, liczba)
    VALUES (1, 10, 2, N'PART-A', 1);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS numer_bledu;                    -- 2601
END CATCH;

Przykład ilustracyjny. Drugie polecenie zderza się z aktywnym numerem, a po miękkim usunięciu ten sam numer wraca do tabeli.

Tabela poniżej zestawia wyniki tych poleceń dla zwykłego indeksu i dla indeksów filtrowanych. Kolumna ze zwykłym indeksem powstaje po zastąpieniu obu indeksów filtrowanych jednym indeksem z bloku o pułapce zwykłego indeksu.

Przykład. Wynik uruchomienia na SQL Server 2022 Express.

Po sekwencji tabela ma sześć wierszy, a odrzucone wpisy nie zostawiają w niej śladu. Widok sys.indexes pokazuje oba indeksy z flagą is_unique równą 1 i filtrami ([status]='A' AND [typ_numeru]=(1)) oraz ([status]='A' AND [typ_numeru]=(2)).

SELECT id_dokumentu, id_pozycji, typ_numeru, wartosc_numeru, status FROM dbo.numery_dokumentu ORDER BY id_numeru;
SELECT name, is_unique, filter_definition FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.numery_dokumentu') AND name LIKE 'UX[_]%';

Przykład ilustracyjny. Zawartość tabeli po sekwencji poleceń i definicje filtrów.

Pułapka czeka przy cofaniu usunięcia. UPDATE ustawiający status „A” przy istniejącym aktywnym wierszu z tym samym numerem także kończy się błędem 2601, więc logika „przywróć” musi to przewidzieć. Najprościej sprawdzić przed cofnięciem, czy numer nie wrócił do dokumentu inną drogą.

BEGIN TRY   -- cofnięcie usunięcia, gdy ten sam numer jest już aktywny
    UPDATE dbo.numery_dokumentu SET status = 'A'
    WHERE id_dokumentu = 1 AND wartosc_numeru = N'SN-000123' AND status = 'D';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS numer_bledu;                    -- 2601
END CATCH;

Przykład ilustracyjny. Cofnięcie usunięcia zderza się z aktywnym wierszem o tym samym numerze.

Obsługa błędów 2601 i 2627 w procedurze zapisu

Naruszenie unikalnego indeksu daje błąd 2601, a naruszenie ograniczenia UNIQUE błąd 2627. Indeks filtrowany nie może być ograniczeniem, więc zawsze daje 2601. Kod, który ma rozpoznać duplikat, powinien dlatego sprawdzać oba numery: ERROR_NUMBER() IN (2601, 2627). Inne błędy przekazuje dalej przez THROW. Sam wzorzec obsługi błędu w procedurze zapisu opisuje artykuł o TRY CATCH i THROW po ROLLBACK.

CREATE OR ALTER PROCEDURE dbo.zapisz_numer_pozycji @id_dokumentu int, @id_pozycji int, @wartosc_numeru nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        INSERT dbo.numery_dokumentu (id_dokumentu, id_pozycji, typ_numeru, wartosc_numeru, liczba)
        VALUES (@id_dokumentu, @id_pozycji, 1, @wartosc_numeru, 1);
        SELECT N'ZAPISANO' AS rezultat;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() IN (2601, 2627) SELECT N'POWTORZONY' AS rezultat;
        ELSE THROW;
    END CATCH;
END;

Przykład ilustracyjny. Duplikat daje wynik POWTORZONY, pozostałe błędy trafiają dalej.

EXEC dbo.zapisz_numer_pozycji 2, 10, N'SN-000777';           -- ZAPISANO
EXEC dbo.zapisz_numer_pozycji 2, 10, N'SN-000777';           -- POWTORZONY

BEGIN TRY
    EXEC dbo.zapisz_numer_pozycji 2, 10, NULL;               -- inny błąd przechodzi dalej przez THROW
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS numer_bledu;                    -- 515
END CATCH;

Przykład ilustracyjny. Pierwszy numer daje ZAPISANO, drugi tej samej wartości POWTORZONY, a NULL przechodzi dalej jako błąd 515.

CREATE TABLE dbo.t_ograniczenie (a int, CONSTRAINT UQ_t_ograniczenie UNIQUE (a));
INSERT dbo.t_ograniczenie VALUES (1);
BEGIN TRY
    INSERT dbo.t_ograniczenie VALUES (1);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS numer_bledu;                    -- 2627
END CATCH;

Przykład ilustracyjny. Ograniczenie UNIQUE zamiast indeksu: inny numer błędu.

Ustawienia sesji i przełącznik -I w sqlcmd

Zakładanie indeksu filtrowanego oraz INSERT na tabeli z takim indeksem wymagają w sesji QUOTED_IDENTIFIER ON. Przy ustawieniu OFF oba kończą się błędem 1934. Dokumentacja Microsoft wymienia też ANSI_NULLS ON. Tę zależność opisuje strona o SET QUOTED_IDENTIFIER, a przykład procedury zapisanej z wyłączonym ustawieniem znajdziesz w artykule o QUOTED_IDENTIFIER OFF w procedurze.

Pułapką jest narzędzie, nie kod. Sqlcmd uruchomiony bez przełącznika -I otwiera sesję z QUOTED_IDENTIFIER OFF: SESSIONPROPERTY zwraca wtedy 0, a z -I zwraca 1. Skrypt z INSERT na takiej tabeli, uruchomiony bez -I, przerywa się błędem 1934.

sqlcmd -S <serwer> -d <baza> -E -b -I -f 65001 -i plik.sql

Polecenie uruchamiające skrypt z przełącznikiem -I.

Błąd 1934 pojawia się przy kompilacji partii. TRY ... CATCH w tej samej partii go nie przechwytuje, a partia kończy się komunikatem. Przechwyci go dopiero zewnętrzny TRY wokół EXEC (...), jak w przykładzie poniżej.

-- sesja z QUOTED_IDENTIFIER OFF (tak działa sqlcmd bez przełącznika -I)
SET QUOTED_IDENTIFIER OFF;
GO
BEGIN TRY
    EXEC (N'INSERT dbo.numery_dokumentu (id_dokumentu, id_pozycji, typ_numeru, wartosc_numeru, liczba) VALUES (2, 10, 1, N''SN-000999'', 1)');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS numer_bledu;                    -- 1934
END CATCH;
GO
SET QUOTED_IDENTIFIER ON;
GO

Przykład ilustracyjny. Sesja z QUOTED_IDENTIFIER OFF i INSERT wykonany przez EXEC: błąd 1934.

Polecenie SET QUOTED_IDENTIFIER działa także między partiami. Wydane w osobnej partii zostaje w kolejnych, zarówno z przełącznikiem -I, jak i bez niego, więc skrypt uruchamiany bez -I może założyć indeks filtrowany, jeśli SET QUOTED_IDENTIFIER ON stoi przed nim. Ustawienie obowiązuje od parsowania partii: SET QUOTED_IDENTIFIER OFF umieszczone w partii za instrukcją SELECT zmienia już wynik tego SELECT, który zwraca 0. Dlatego ustawienie sprawdzaj w osobnej partii.

Kontrola danych przed założeniem indeksu

Założenie indeksu na danych z duplikatami kończy się błędem 1505. Przed założeniem uruchom GROUP BY ... HAVING COUNT(*) > 1 z tym samym warunkiem co filtr indeksu. Pusty wynik oznacza, że indeks się założy. Drugie zapytanie z tego samego bloku odczytuje ustawienie sesji: wartość 1 to QUOTED_IDENTIFIER ON.

-- przed założeniem indeksu: duplikaty wśród wierszy objętych filtrem (wynik pusty = indeks się założy)
SELECT id_dokumentu, wartosc_numeru, COUNT(*) AS ile
FROM dbo.numery_dokumentu
WHERE status = 'A' AND typ_numeru = 1
GROUP BY id_dokumentu, wartosc_numeru
HAVING COUNT(*) > 1;

-- sesja: 1 = QUOTED_IDENTIFIER ON
SELECT SESSIONPROPERTY('QUOTED_IDENTIFIER') AS quoted_identifier;

Przykład ilustracyjny. Oba zapytania zwracają pusty wynik i wartość 1.

Wyniki sekwencji poleceń z tego artykułu pokazują, że o dopuszczeniu wpisu decyduje wyłącznie filtr indeksu: wiersz o statusie „D” i wiersz typu 3 po prostu nie istnieją dla jednoznaczności. Te same numery sklei w jedną kolumnę zapytanie z artykułu o STRING_AGG w raporcie, a miejsce ze stanem wskaże wzorzec z artykułu o saldzie netto w T-SQL. Wybór ceny z przedziałów opisuje tekst o zakresach cennika, a inny sposób zabezpieczenia przed dublowaniem zdarzeń pokazuje wzorzec jednego maila na zdarzenie. Pozostałe artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.

Pytania przed zaprojektowaniem własnego indeksu

Zanim zaprojektujesz indeks dla własnych numerów, odpowiedz sobie na cztery pytania.

• Zakres: czy numer seryjny ma być jednoznaczny tylko w obrębie dokumentu, czy w całej bazie.
• Partia: czy partia ma być jednoznaczna na pozycji, czy w dokumencie.
• Cofanie: kto może cofnąć usunięcie numeru i co ma się stać, gdy numer został w międzyczasie wpisany ponownie.
• Skrypty: jak uruchamiane są skrypty wdrożeniowe: sqlcmd z przełącznikiem -I czy innym narzędziem, i kto sprawdza ustawienia sesji.