Letzte Woche wurde ich beauftragt, die Ursachen für eine schlechte Performance innerhalb einer Datenbank zu analysieren und gegebenenfalls Hinweise zu geben, wie man die Wartezeiten im Frontend verkürzen kann. Bei der Prüfung der Ausführungspläne und Indexstrukturen ist aufgefallen, dass fast 10% der Relationen HEAPS sind und in vielen Abfragen mit anderen Relationen über JOINS verwendet wurden. Meine erste Empfehlung war demzufolge – basierend auf den Ausführungsplänen – die Implementierung und Verwendung von Clustered Indexes. Da die Anwendung nicht “in House” entwickelt worden ist, wurde sich mit der Bitte um Prüfung und Zusendung von Skripten an den Hersteller gewandt. Das nachfolgende Script soll stellvertretend für den Lösungsansatz des Herstellers dienen. Bei der im Beispiel benannten Relation handelt es sich um eine simple Relation für die Speicherung historischer Daten ohne Fremdschlüssel-Referenzen. 

1. CREATE CLUSTERED INDEX ix_relation_id ON dbo.tbl_relation(id);
2. DELETE dbo.relation WHERE id IN (SELECT id FROM dbo.relation GROUP BY id HAVING COUNT(id) > 1);
3. DROP INDEX ix_relation ON dbo.tbl_relation;
4. ALTER TABLE dbo.tbl_relation ADD CONSTRAINT p_relation_id PRIMARY KEY (id);

Als ich das Script von meinem Auftraggeber erhalten habe, musste ich erst mal tief Luft holen und mich fragen, welcher “Experte” solche Skripte an seine Kunden verschickt. Zunächst wird ein Clustered Index auf dem Attribut [Id] der Relation [dbo].[tbl_relation] erstellt (Zeile 1). Anschließend werden ALLE Datensätze, die eine redundante [Id] besitzen gelöscht (Zeile 2). Nachdem die Daten gelöscht wurden, wird der Clustered Index wieder entfernt und durch einen PRIMARY KEY ersetzt.

Der brisanteste Teil in diesem Script ist sicherlich die Zeile 2; sollen doch tatsächlich Daten aus einem Produktionssystem gelöscht werden ohne diese im Vorfeld in einer Staging-Tabelle zu analysieren. Des Weiteren ist aufgefallen, dass dem Programmierer scheinbar der Unterschied zwischen einem Clustered Index und der Einschränkung eines PRIMARY KEY nicht bekannt ist. Die Intention des Programmierers wird beim Lesen der obigen Skriptzeilen relativ schnell klar; zunächst wird ein “Index” erzeugt, um schnell die redundanten Datensätze zu löschen. Anschließend soll mittels PRIMARY KEY erneut ein Clustered Index erzeugt werden, um unsere Anforderungen zu erfüllen.

Was ist ein Primary Key

Um mitzuteilen, welchen der Schlüsselkandidaten man zur Identifikation eines Datensatzes (Tupel) in einer Relation bevorzugt, wird aus allen Schlüsselkandidaten der Primärschlüssel ausgewählt. Der Primärschlüssel wird üblicherweise so ausgewählt, dass er möglichst klein ist, das heißt möglichst wenige Attribute umfasst bzw. einen möglichst simplen Datentyp hat. Ein Primärschlüssel ist KEIN Index ist sondern eine Einschränkung(CONSTRAINT). Ein Primärschlüssel besitzt die nachfolgenden Besonderheiten:

• Eine Relation kann nur einen PRIMARY KEY besitzen
• In den Attributen des PRIMARY KEY sind keine NULL-Werte zugelassen
• Die Attribute des PRIMARY KEY dürfen nicht redundant sein (doppelte Schlüsselwerte!)
• Die Einschränkung PRIMARY KEY erzwingt immer die Definition eines EINDEUTIGEN Index und nicht zwingend einen CLUSTERED INDEX!
• Ein PRIMARY KEY ist NICHT zwingend für DRI (deklarative referenzielle Integrität)

Die nachfolgenden Beispiele soll die Besonderheiten eines PRIMARY KEY verdeutlichen:

CREATE TABLE dbo.master_table
(
    Id    int       NULL PRIMARY KEY,
    col1  char(20)  NULL
);
GO

Der Aufruf schlägt fehl, da das Attribut [Id] NULL-Werte zulässt während der nächste Aufruf im nächsten Beispiel funktioniert, da die Einschränkung NOT NULL für das Attribut [Id] verwendet wird.

Meldung 8111, Ebene 16, Status 1, Zeile 2 Eine PRIMARY KEY-Einschränkung kann für eine Spalte in der master_table-Tabelle, die NULL zulässt, nicht definiert werden.

CREATE TABLE dbo.master_table
(
    Id    int       NOT NULL PRIMARY KEY,
    col1  char(20)  NULL
);
GO

Sofern für die Relation nicht explizit ein Clustered Index definiert wird, erzeugt SQL Server automatisch für den PRIMARY KEY einen clustered Index. Beweis liefert die Untersuchung der Indexe und Einschränkungen für die zuvor erstellte Relation

SELECT OBJECT_NAME(parent_object_id)  AS table_name,
       k.name                         AS constraint_name,
       k.type                         AS constraint_type,
       k.type_desc                    AS constraint_type_desc,
       unique_index_id,
       is_system_named,
       i.name                         AS index_name,
       i.type_desc                    AS index_type
FROM   sys.key_constraints k LEFT JOIN sys.indexes i
       ON (
            k.parent_object_id = i.object_id AND
            k.unique_index_id = i.index_id
          )
WHERE  parent_object_id = OBJECT_ID('dbo.master_table', 'U');

Das DMO sys.key_constraints enthält jeweils eine Zeile für jedes Objekt, dass entweder eine PRIMARY KEY oder eine UNIQUE Einschränkung besitzt. Die obige Abbildung zeigt, dass der PRIMARY KEY eindeutig ist und der Name der Einschränkung automatisch durch SQL Server erstellt wurde. Handelt es sich um eine eindeutige Einschränkung (UNIQUE), kann der entsprechende UNIQUE Index über die [index_id] der zugehörigen Relation ermittelt werden.

Da eine Einschränkung auch durch individuelle Namensgebung erstellt werden kann, führt das nachfolgende Beispiel ebenfalls zur Erstellung des PRIMARY KEY mit dem Unterschied, dass der Name durch den Ersteller vordefiniert wird.

CREATE TABLE dbo.master_table
(
    Id    int       NOT NULL,
    col1  char(20)  NULL,
    CONSTRAINT pk_master_table_id PRIMARY KEY (Id)
);
GO

Da nun der Name des PRIMARY KEY selbst festgelegt worden ist, ist das Attribut [is_system_named] nun 0. Weitere Einstellungen haben sich nicht geändert. Das ein PRIMARY KEY nicht zwingend ein Clustered Index ist, zeigt das nächste Beispiel sehr deutlich:

CREATE TABLE dbo.master_table
(
    Id    int       NOT NULL,
    col1  char(20)  NULL,
    CONSTRAINT pk_master_table_id PRIMARY KEY NONCLUSTERED (id)
);
GO

CREATE CLUSTERED INDEX ix_master_table_col1 ON dbo.master_table (col1);
GO

Unabhängig von der Existenz eines Clustered Index kann der PRIMARY KEY definiert werden. Da ein PRIMARY KEY nur dann als Clustered Index angelegt wird, wenn noch kein Clustered Index existiert, musste in dem obigen Beispiel der PRIMARY KEY explizit mit der Option NONCLUSTERED definiert werden um eine automatische Erstellung des Clustered Index zu verhindern. Ein Blick auf die Einschränkungen und die vorhandenen Indexe zeigt das Ergebnis.

Wozu wird ein Primary Key verwendet?

Der Sinn und Zweck eines PRIMARY KEY ergibt sich aus den Eigenschaften der Einschränkung selbst. Da ein PRIMARY KEY keine NULL-Werte enthalten darf und eindeutig sein muss, eignet er sich für die Durchsetzung von DRI (Deklarative Referenzielle Integrität).  Unter Referentieller Integrität versteht man Bedingungen, die zur Sicherung der Datenintegrität bei Nutzung relationaler Datenbanken beitragen können. Nach der RI-Regel dürfen Datensätze (über ihre Fremdschlüssel) nur auf existierende Datensätze verweisen.

Danach besteht die Referenzielle Integrität grundsätzlich aus zwei Teilen:

1. Ein neuer Datensatz mit einem Fremdschlüssel kann nur dann in einer Tabelle eingefügt werden, wenn in der referenzierten Tabelle ein Datensatz mit entsprechendem Wert im Primärschlüssel oder einem eindeutigen Alternativschlüssel existiert.
2. Eine Datensatzlöschung oder Änderung des Schlüssels in einem Primär-Datensatz ist nur möglich, wenn zu diesem Datensatz keine abhängigen Datensätze in Beziehung stehen.

SQL Server benötigt für Referenzielle Integrität nicht zwingend einen PRIMARY KEY; vielmehr sind nur die Eigenschaften eines PRIMARY KEY für SQL Server von Relevanz für die Durchsetzung von Referenzieller Integrität. Die beiden nachfolgenden Skriptbeispiele sollen diesen Zusammenhang verdeutlichen. Für die Definition eines Fremdschlüssels ist zwingend erforderlich, dass das Primärattribut der Masterrelation weder NULL-Werte besitzt noch mehrfach vorkommen darf. Das nachfolgende Script wird fehlschlagen, da SQL Server nicht in der Lage ist, eindeutige Schlüsselwerte in [master_table] sicher zu stellen:

CREATE TABLE dbo.master_table
(
    Id   int       NOT NULL,
    col1 char(20)  NULL
);
GO

CREATE TABLE dbo.detail_table
(
    Master_id   int       NOT NULL,
    detail_id   int       NOT NULL,
    col1        char(20)  NOT NULL,

    CONSTRAINT fk_master_table_id FOREIGN KEY (master_id)
    REFERENCES dbo.master_table(Id)
    ON DELETE CASCADE
);

Wird das obige Script ausgeführt, wird der nachfolgende Fehler ausgelöst. Die Fehlerbeschreibung ist selbsterklärend:

Meldung 1776, Ebene 16, Status 0, Zeile 2 In der dbo.master_table-Tabelle, auf die verwiesen wird, befinden sich keine primären oder Kandidatenschlüssel, die mit der verweisenden Spaltenliste im fk_master_table_id-Fremdschlüssel übereinstimmen.

Für eine Fremdschlüsselbeziehung wird ENTWEDER ein PRIMARY KEY oder ein UNIQUE NOT NULL Index benötigt. Statt also eines PRIMARY KEY kann auch eine UNIQUE-Einschränkung verwendet werden. Ändert man das Script zur Erstellung der Relationen wie folgt um, kann die Einschränkung problemlos erstellt werden:

CREATE TABLE dbo.master_table
(
    Id   int       NOT NULL,
    col1 char(20)  NULL,

    CONSTRAINT ux_master_table_id UNIQUE (Id)   
);
GO

CREATE TABLE dbo.detail_table
(
    Master_id   int       NOT NULL,
    detail_id   int       NOT NULL,
    col1        char(20)  NOT NULL,

    CONSTRAINT fk_master_table_id FOREIGN KEY (master_id)
    REFERENCES dbo.master_table(Id)
    ON DELETE CASCADE
);

Das Script läuft fehlerfrei durch und die Fremdschlüssel-Einschränkung wird erstellt, da für das Fremdschlüssel-Attribut [Id] die Einschränkungen “NOT NULL” und “UNIQUE” vorhanden sind. Es bedarf keiner weiteren Einschränkungen mehr. Schaut man sich anschließend die in der Relation [dbo].[master_table] befindlichen Indexe an, stellt man fest, dass die Relation selbst immer noch ein HEAP ist und keinen clustered Index besitzt.

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.master_table', 'U');

Fazit

Die Definition eines PRIMARY KEY für eine Relation hat NICHTS mit der Definition und der Funktionalität eines Clustered Index zu tun. Beide Verfahren sind unabhängig voneinander zu betrachten und verfolgen ganz verschiedene Ansätze. Während ein PRIMARY KEY eine Eigenschaft einer Relation ist, ist der Clustered Index die Relation selbst. Während ein Clustered Index ausschließlich für die physikalische Ordnung der Datensätze verantwortlich ist, dient der PRIMARY KEY der Durchsetzung von klar vordefinierten Vorgaben für das Schlüsselattribut eines Datensatzes (Eindeutigkeit und NOT NULLABLE).

Herzlichen Dank für’s Lesen