dtcSoftware - powerShellDba

📌 Warum das Thema überhaupt relevant ist

GRUNDLAGEN

Ein Unique Index (oder Unique Constraint) stellt sicher, dass in einer Spalte (oder einer Kombination von Spalten) keine doppelten Werte vorkommen. Doch was passiert, wenn die Spalte NULL-Werte erlaubt? Gilt NULL als gleich zu NULL? Die Antwort ist: NULL ist ungleich NULL – zumindest im Kontext von Unique Indizes. Genau dieses Verhalten führt oft zu Verwirrung: Mehrere NULL-Werte sind erlaubt, obwohl man vielleicht genau das Gegenteil möchte. Dieser Artikel zeigt, wie SQL Server mit NULL-Werten in Unique Indizes umgeht, welche Tücken es gibt und wie Sie eindeutige Einschränkungen mit optionalen Werten dennoch durchsetzen können.

💡 Kernaussage: Ein Unique Index akzeptiert mehrere NULL-Werte, da NULL als unbekannt gilt und niemals als gleich bewertet wird. Das ist standardkonform, aber nicht immer gewünscht.

🔎 Standardverhalten: Mehrere NULL-Werte sind erlaubt

VERHALTEN

Erstellen wir eine einfache Tabelle mit einem Unique Index auf einer nullable Spalte:

CREATE TABLE dbo.TestUnique (
  Id INT IDENTITY (1,1) PRIMARY KEY,
  Code VARCHAR(20) NULL,
  CONSTRAINT UQ_TestUnique_Code UNIQUE (Code)
);

-- Einfügen von NULL-Werten – funktioniert beliebig oft
INSERT INTO dbo.TestUnique (Code) VALUES (NULL);
INSERT INTO dbo.TestUnique (Code) VALUES (NULL);
INSERT INTO dbo.TestUnique (Code) VALUES (NULL);

-- Eindeutige Werte funktionieren normal
INSERT INTO dbo.TestUnique (Code) VALUES ('A');
INSERT INTO dbo.TestUnique (Code) VALUES ('A'); -- Fehler: Verletzung der UNIQUE-Einschränkung

💡 Erklärung: Nach SQL-Standard sind zwei NULL-Werte nicht gleich (NULL = NULL ist unbekannt). Daher erkennt der Unique Index keine Duplikate und lässt beliebig viele NULL-Einträge zu. Für viele Anwendungen ist das korrekt (z. B. optionale Seriennummern, bei denen nur echte Werte eindeutig sein müssen).

⚠️ Die Herausforderung: Nur einen einzigen NULL-Wert zulassen

KONFLIKT

In manchen Fällen möchte man, dass eine Spalte entweder einen eindeutigen Wert oder höchstens einen NULL-Wert enthält. Beispiel: Eine Tabelle für Benutzer, bei der die E-Mail-Adresse eindeutig sein soll, aber auch NULL (keine E-Mail) erlaubt ist – allerdings nur für einen Benutzer? Nein, meist möchte man mehrere NULL-E-Mails erlauben. Ein realistischeres Beispiel: Eine „aktuelle Vertragsversion“-Spalte, bei der es genau eine Zeile mit NULL (die aktive Version ohne Enddatum) geben darf, alle anderen müssen eindeutige Enddaten haben. Oder eine Auftragstabelle, bei der maximal ein Stornierungsgrund NULL sein soll (weil noch nicht storniert). Wie löst man das?

📌 Problem: Ein normaler Unique Index erlaubt unbegrenzt viele NULLs. Sie können diese Einschränkung also nicht direkt abbilden.

Beispiel für gewünschtes Verhalten: Maximal eine Zeile mit NULL in Spalte „EndDatum“ (alle anderen müssen eindeutige Daten haben). Ein normaler Unique Index lässt mehrere NULLs zu – unerwünscht.

✅ Lösung 1: Gefilterter Unique Index (WHERE IS NOT NULL)

FILTERED INDEX

Mit einem gefilterten Index können Sie die Eindeutigkeitsprüfung nur auf Nicht-NULL-Werte anwenden. Wenn Sie zusätzlich nur einen NULL-Wert erzwingen möchten, brauchen Sie einen zweiten gefilterten Index, der NULL auf Eindeutigkeit prüft (oder eine andere Logik). Allerdings: Ein gefilterter Unique Index auf WHERE Column IS NULL erlaubt ebenfalls nur einen NULL-Wert, da der Index nur diese eine Zeile enthalten würde. Probieren wir es aus:

CREATE TABLE dbo.Vertrag (
  VertragId INT PRIMARY KEY,
  EndDatum DATE NULL
);

-- Index, der eindeutige EndDaten für nicht-NULL-Werte erzwingt
CREATE UNIQUE NONCLUSTERED INDEX UQ_Vertrag_EndDatum_NotNull
  ON dbo.Vertrag (EndDatum)
  WHERE EndDatum IS NOT NULL;

-- Index, der höchstens einen NULL-Wert erlaubt
CREATE UNIQUE NONCLUSTERED INDEX UQ_Vertrag_EndDatum_Null
  ON dbo.Vertrag (EndDatum)
  WHERE EndDatum IS NULL;

-- Test: Einfügen von zwei NULLs – der zweite schlägt fehl
INSERT INTO dbo.Vertrag (VertragId, EndDatum) VALUES (1, NULL); -- OK
INSERT INTO dbo.Vertrag (VertragId, EndDatum) VALUES (2, NULL); -- Fehler: Verletzung des gefilterten Index

-- Nicht-NULL-Werte müssen untereinander eindeutig sein
INSERT INTO dbo.Vertrag (VertragId, EndDatum) VALUES (3, '2025-12-31');
INSERT INTO dbo.Vertrag (VertragId, EndDatum) VALUES (4, '2025-12-31'); -- Fehler: doppeltes Datum (erlaubt der erste Index nicht)

💡 Ergebnis: Mit zwei gefilterten Unique Indizes haben wir die gewünschte Logik: Höchstens ein NULL, alle Nicht-NULL-Werte untereinander eindeutig. Das ist elegant und ressourcenschonend, da die Indizes klein bleiben.

🧩 Lösung 2: Berechnete Spalte als „NULL-Ersatz“

WORKAROUND

Eine ältere Methode (vor SQL Server 2008, als gefilterte Indizes noch nicht existierten) ist die Verwendung einer berechneten Spalte, die aus NULL einen eindeutigen Wert (z. B. die Primärschlüssel-ID) macht, und diese dann als Unique zu definieren. Das ist umständlich und oft nicht nötig. Hier ein Beispiel nur fürs Verständnis:

-- Nicht mehr empfohlen – dient der Historie
CREATE TABLE dbo.Beispiel (
  Id INT PRIMARY KEY,
  Wert VARCHAR(50) NULL,
  WertUnique AS ISNULL(Wert, 'NULL_' + CAST(Id AS VARCHAR(10))) PERSISTED
);
CREATE UNIQUE INDEX UQ_WertUnique ON dbo.Beispiel (WertUnique);

📌 Nachteil: Diese Lösung ist komplex, benötigt persistente Spalten und kann bei vielen Änderungen teuer sein. Gefilterte Indizes sind fast immer die bessere Wahl.

🔧 Lösung 3: Mehrspaltige Unique Indizes mit NULL-Unterscheidung

KOMPOSIT

Manchmal will man, dass eine Kombination aus Spalten eindeutig ist, wobei einzelne Spalten NULL sein dürfen. Standardmäßig gilt auch hier: Mehrere NULLs in einer der Spalten sind erlaubt, solange die andere(n) Spalte(n) sich unterscheiden. Aber wenn alle Spalten NULL sind, ist das auch mehrfach möglich. Ein Beispiel:

CREATE TABLE dbo.KombiTest (
  Id INT PRIMARY KEY,
  SpalteA INT NULL,
  SpalteB INT NULL,
  CONSTRAINT UQ_Kombi UNIQUE (SpalteA, SpalteB)
);

-- Das ist erlaubt (mehrfach)
INSERT INTO dbo.KombiTest (Id, SpalteA, SpalteB) VALUES (1, NULL, NULL);
INSERT INTO dbo.KombiTest (Id, SpalteA, SpalteB) VALUES (2, NULL, NULL); -- ebenfalls erlaubt (weil (NULL,NULL) nicht gleich (NULL,NULL) nach SQL-Regel)

-- Eindeutige Nicht-NULL-Kombinationen funktionieren
INSERT INTO dbo.KombiTest (Id, SpalteA, SpalteB) VALUES (3, 1, NULL); -- ok
INSERT INTO dbo.KombiTest (Id, SpalteA, SpalteB) VALUES (4, 1, NULL); -- Fehler (doppelte (1, NULL)-Kombination)

💡 Fazit: Das Standardverhalten bei zusammengesetzten Indizes ist konsistent: NULLs in einer Spalte verhalten sich wie oben, d. h. zwei Zeilen mit (NULL, 1) und (NULL, 2) sind unterschiedlich, aber (NULL, NULL) kann mehrfach vorkommen. Wenn das nicht gewünscht ist, helfen wieder gefilterte Indizes auf die entsprechenden Kombinationen.

⚡ Performance: Gefilterte Indizes mit NULL

OPTIMIERUNG

Gefilterte Indizes sind nicht nur kleiner, sondern können auch die Performance verbessern, weil sie weniger Einträge enthalten. Ein Index auf WHERE Column IS NULL hat maximal eine Zeile (wenn Sie nur einen NULL zulassen). Der Optimierer kann solche Indizes gezielt für Abfragen nutzen, die nach NULL suchen. Allerdings sollten Sie beachten:

Gefilterte Indizes können nicht als Primärschlüssel oder Unique Constraint (im klassischen Sinne) deklariert werden, aber als Unique Index sind sie vollwertig.
Parameterisierte Abfragen mit WHERE Column = @value können den gefilterten Index nur nutzen, wenn @value zur Filterbedingung passt (z. B. WHERE Column IS NULL funktioniert, aber WHERE Column = @null nicht, da @null nicht den Wert NULL hat, sondern einen Parameter).
Nutzen Sie OPTION (RECOMPILE), um solche Abfragen zu optimieren, wenn Sie mit NULL-Variablen arbeiten.

-- Abfrage, die den gefilterten Index auf NULL effizient nutzt
SELECT * FROM dbo.Vertrag WHERE EndDatum IS NULL;

-- Vorsicht: Diese Abfrage nutzt den Index nicht, wenn @date = NULL (Parameter ist nicht NULL, sondern der Wert NULL ist was anderes)
DECLARE @date DATE = NULL;
SELECT * FROM dbo.Vertrag WHERE EndDatum = @date; -- funktioniert nicht wie erwartet!
-- Korrekt wäre: WHERE EndDatum IS NULL

⚠️ Wichtiger Hinweis: Ein Parameter mit Wert NULL ist nicht dasselbe wie die NULL-Konstante. Verwenden Sie in dynamischen Abfragen IS NULL explizit, wenn Sie nach NULL suchen.

📖 Unterschied: UNIQUE CONSTRAINT vs. UNIQUE INDEX

SYNTAX

In SQL Server sind Unique Constraints und Unique Indizes funktional nahezu identisch. Ein Unique Constraint wird automatisch durch einen Unique Index implementiert. Der Unterschied liegt in der Verwaltung:

Unique Constraint: Ist Teil der Tabellendefinition (kann als Fremdschlüssel referenziert werden).
Unique Index: Ist eigenständiges Objekt, kann gefiltert werden, kann INCLUDE-Spalten haben.

Für gefilterte Indizes müssen Sie CREATE UNIQUE INDEX verwenden. Ein Unique Constraint unterstützt keine Filterbedingung.

-- Das ist möglich (gefilterter Unique Index)
CREATE UNIQUE NONCLUSTERED INDEX UQ_Beispiel_Filtered
  ON dbo.Tabelle (Spalte)
  WHERE Spalte IS NOT NULL;

-- Das ist nicht erlaubt (Constraint ohne Filter)
ALTER TABLE dbo.Tabelle
  ADD CONSTRAINT UQ_Beispiel UNIQUE (Spalte); -- ohne WHERE

✅ Best Practices für Unique Indexes auf Nullable Columns

PRAXIS

Standardverhalten kennen und nutzen: Wenn mehrere NULLs erlaubt sein sollen, ist ein normaler Unique Index (oder Constraint) völlig ausreichend – kein Handlungsbedarf.
Für genau einen NULL-Wert: Verwenden Sie zwei gefilterte Unique Indizes – einen auf WHERE Spalte IS NOT NULL und einen auf WHERE Spalte IS NULL. Das ist die sauberste Lösung.
Bei zusammengesetzten Schlüsseln: Wenn bestimmte NULL-Kombinationen nur einmal vorkommen dürfen, nutzen Sie entsprechende Filter. Beispiel: WHERE SpalteA IS NOT NULL OR SpalteB IS NOT NULL (komplexer).
Dokumentieren Sie das Verhalten: Fügen Sie Kommentare in den Code ein, warum Sie einen gefilterten Index verwenden. Das erspart später Verwirrung.
Testen Sie die Auswirkungen auf Abfragen: Gefilterte Indizes werden nicht immer automatisch genutzt – vor allem bei Verwendung von Parametern.
Vermeiden Sie die berechnete Spalten-Lösung – gefilterte Indizes sind effizienter und einfacher.

⚠️ Häufige Irrtümer zu Unique Indexes und NULL

AUFRÄUMEN

❌ „Ein Unique Index verhindert mehrere NULLs“ → Nein, das ist falsch. Ein normaler Unique Index erlaubt beliebig viele NULL-Werte.
❌ „Ich kann das Verhalten nicht ändern“ → Doch, mit gefilterten Indizes können Sie genau festlegen, welche NULL-Kombinationen eindeutig sein müssen.
❌ „Gefilterte Indizes werden von Entity Framework / ORMs nicht unterstützt“ → Das ist ein Problem des ORMs, nicht von SQL Server. Sie können die Indizes trotzdem anlegen; bei Verstößen erhalten Sie eine Exception.
❌ „Ein Unique Constraint ist immer besser als ein Index“ → Für Standard-Fälle ja, aber für gefilterte Eindeutigkeit brauchen Sie den Index.
❌ „NULL = NULL ergibt true in einigen Datenbanken“ → In SQL Server gilt NULL != NULL. In anderen DBMS (z. B. Oracle) ist das Verhalten anders – dort wird NULL ebenfalls als ungleich betrachtet, aber Unique Indizes lassen dort auch nur einen NULL zu? Nicht ganz: Oracle erlaubt ebenfalls mehrere NULLs in Unique Indizes. Achtung bei Cross-Plattform-Entwicklung.

🚀 Fazit: Das Verhalten von Unique Indizes auf nullable Spalten ist kein Bug, sondern gewollt. Wenn Sie eine strengere Logik benötigen (höchstens ein NULL oder spezifische NULL-Kombinationen), sind gefilterte Unique Indizes Ihr Freund. Nutzen Sie sie, um Datenintegrität zu gewährleisten, ohne auf Trigger oder komplexe Prüfungen zurückgreifen zu müssen.