Während meines Vortrags über “Temporal Tables” auf dem SQL Saturday Rheinland 2016 wurden einige Fragen gestellt, die ich nicht “ad hoc” beantworten konnte, da ich zu den Fragen noch keine ausreichenden Tests gemacht hatte. Mit diesem Artikel beginne ich eine Artikelreihe über “System versioned Temporal Tables”, zu der mich insbesondere Thomas Franz inspiriert hat. Ihm danke ich sehr herzlich für die vielen Fragen, die er mir per Email zugesendet hat.

Inhaltsverzeichnis

Hinweis

Diese Artikelreihe befasst sich nicht mit den Grundlagen von “System Versioned Temporal Tables”! Die grundsätzliche Funktionsweise über “System versioned Temporal Tables” kann im Artikel “Temporal Tables” (english) bei Microsoft nachgelesen werden.

NULL oder NOT NULL

Frage: “… kann ein Attribut mit einer NULL-Einschränkung nachträglich eine NOT NULL-Einschränkung erhalten?”
Diese Frage kann man ganz eindeutig beantworten: “It depends!”
Wenn Attribute in einer Tabelle NULL-Werte zulassen, dann muss die abhängige “System Versioned Temporal Table” die gleichen Einschränkungen besitzen. Sollte diese Einschränkung in beiden Tabellen unterschiedlich sein, können Informationen eventuell nicht abgespeichert werden und die Historisierung ist unvollständig. Das folgende Skript erstellt eine Tabelle [dbo].[Customers] sowie die korrespondierende Tabelle für die Speicherung der historischen Daten mit gleichem Namen im Schema [history].

-- Create a dedicated schema for the history data
IF SCHEMA_ID(N'history') IS NULL
EXEC sp_executesql N'CREATE SCHEMA [history] AUTHORIZATION dbo;';
GO

-- Create the base table for the application data
IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
    DROP TABLE dbo.Customers;
    GO

CREATE TABLE dbo.Customers
(
    Id         INT             NOT NULL    IDENTITY (1, 1),
    Name       VARCHAR(100)    NOT NULL,
    Street     VARCHAR(100)    NOT NULL,
    ZIP        CHAR(5)         NOT NULL,
    City       VARCHAR(100)    NOT NULL,
    Phone      VARCHAR(20)     NULL,
    Fax        VARCHAR(20)     NULL,
    EMail      VARCHAR(255)    NULL,
    ValidFrom  DATETIME2(0)    GENERATED ALWAYS AS ROW START  NOT NULL  DEFAULT ('2016-01-01T00:00:00'),
    ValidTo    DATETIME2(0)    GENERATED ALWAYS AS ROW END    NOT NULL  DEFAULT ('9999-12-31T23:59:59'),

    CONSTRAINT pk_Customers_ID PRIMARY KEY CLUSTERED (Id),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
);
GO

-- Create the System Versioned Temporal Table for history data
CREATE TABLE history.Customers
(
    Id         INT             NOT NULL,
    Name       VARCHAR(100)    NOT NULL,
    Street     VARCHAR(100)    NOT NULL,
    ZIP        CHAR(5)         NOT NULL,
    City       VARCHAR(100)    NOT NULL,
    Phone      VARCHAR(20)     NULL,
    Fax        VARCHAR(20)     NULL,
    EMail      VARCHAR(255)    NULL,
    ValidFrom  DATETIME2(0)    NOT NULL,
    ValidTo    DATETIME2(0)    NOT NULL
);
GO

-- Activate System Versioning
ALTER TABLE dbo.Customers
SET
(
    SYSTEM_VERSIONING = ON
    (HISTORY_TABLE = History.Customers)
);
GO

In der Tabelle können die Attribute [Phone], [Fax] und [Email] leer sein (NULL). Anschließend wird ein Datensatz in die Tabelle [dbo].[Customers] eingetragen. Der einzutragende Datensatz besitzt eine Emailadresse aber Phone und Fax bleiben leer!

INSERT INTO [dbo].[Customers]
(Name, Street, ZIP, City, Phone, Fax, EMail)
VALUES
('db Berater GmbH', 'Bahnstrasse 33', '64390', 'Erzhausen', NULL, NULL, 'info@db-berater.de');
GO

Testszenarien

Die Tabellen- und Datenstruktur lässt unterschiedliche Testszenarien zu, mit denen die Fragestellung analysiert werden kann. Für die Fragestellung werden vier verschiedene Szenarien untersucht:

NULL wird zu NOT NULL in einer leeren Tabelle
NULL wird zu NOT NULL in einer gefüllten Tabelle
NOT NULL wird zu NULL in einer gefüllten Tabelle
NOT NULL wird zu NULL

NULL wird zu NOT NULL in leerer Tabelle

Im ersten Beispiel wird die Einschränkung ohne Inhalt in der Tabelle geändert

BEGIN TRANSACTION;
GO

    ALTER TABLE dbo.Customers
    ALTER COLUMN [Phone] VARCHAR(20) NOT NULL;
    GO

    SELECT  SCHEMA_NAME(o.schema_id) + N'.' + O.name,
            DTL.request_mode,
            DTL.request_type,
            DTL.request_status
    FROM    sys.dm_tran_locks AS DTL
            INNER JOIN sys.objects AS O
            ON (DTL.resource_associated_entity_id = O.object_id)
    WHERE   DTL.request_session_id = @@SPID
            AND DTL.resource_type = N'OBJECT'
    GO

COMMIT TRANSACTION;
GO

Der Code wickelt die Änderung innerhalb einer expliziten Transaktion ab, um so die gesetzten Sperren nach der Änderung nachverfolgen zu können. Tatsächlich ist eine Änderung von Einschränkungen in der Tabelle ohne Weiteres möglich, wenn sich noch keine Daten in der “System versioned Temporal Table” befinden.

Die Abbildung zeigt, dass für die Anpassungen in Systemtabellen Änderungen vorgenommen werden müssen. Die beiden Benutzertabellen werden mit einer LCK_M_SCH_M-Sperre versehen. Hierbei handelt es sich um Schemasperren, die verhindern, dass während eines DDL-Prozesses auf die betroffenen Objekte zugegriffen wird. Ohne Fehler wird die Änderung implementiert. Für eine Anpassung muss “System Versioning” nicht deaktiviert werden!

Vorher	Nachher

Die Abbildung zeigt, dass die Einschränkungen nicht nur auf [dbo].[Customers] angewendet wurde sondern ebenfalls auf die Tabelle [history].[Customers] übertragen wurde. Identische Metadaten sind essentiell für “System Versioned Temporal Tables”! Dieser Test lässt sich auch in die andere Richtung wiederholen. Solange noch KEINE DATEN in der “System Versioned Temporal Table” vorhanden sind, können NULL-Einschränkungen ohne Probleme auf die Objekte angewendet werden.

NULL wird zu NOT NULL in einer gefüllten Tabelle

Das nächste Szenario muss differenziert betrachtet werden. Für das Beispiel soll das Attribut [Email] verwendet werden. Zunächst wird ein Datensatz in die Tabelle [dbo].[Customers] eingetragen, der eine EMail-Adresse besitzt.

INSERT INTO [dbo].[Customers]
(Name, Street, ZIP, City, Phone, Fax, EMail)
VALUES
('db Berater GmbH', 'Bahnstrasse 33', '64390', 'Erzhausen', NULL, NULL, 'info@db-berater.de');
GO

Anschließend wird versucht, die NULL-Einschränkung für das Attribut [Email] zu ändern:

ALTER TABLE dbo.Customers
ALTER COLUMN [Email] VARCHAR(255) NOT NULL;
GO

Tatsächlich läuft die Änderung ohne Probleme durch und für das Attribut [Email] sind keine NULL-Werte mehr erlaubt. Nachdem für das Attribut [Email] die NULL-Einschränkung erneut geändert wurde (NULL-Werte erlaubt) , wird ein weiterer Datensatz eingetragen, der keine Email-Adresse besitzt:

ALTER TABLE dbo.Customers
ALTER COLUMN [Email] VARCHAR(255) NULL;
GO

INSERT INTO [dbo].[Customers]
(Name, Street, ZIP, City, Phone, Fax, Email)
VALUES
('Microsoft GmbH', 'Musterstrasse 33', '12345', 'Musterhausen', NULL, NULL, NULL);
GO

Wird nun erneut versucht, für das Attribut [Email] die NULL-Einschränkung zu ändern, schlägt die Änderung fehl. Die Fehlermeldung lässt sehr schnell erkennen, worin die Ursache dafür liegt:

Meldung 515, Ebene 16, Status 2, Zeile 155
Der Wert NULL kann in die Email-Spalte, temporal.dbo.Customers-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei UPDATE.

Microsoft SQL Server überprüft vor der Konfiguration der NULL-Einschränkung zunächst das vorhandene Datenmaterial. Befinden sich Datensätze in der Tabelle, die einen NULL-Wert im Attribut besitzen, dann können diese Datensätze bei einer Änderung die vorherige Version nicht abspeichern. Für das Beispiel des zweiten Datensatzes würde eine Änderung an der Adresse dazu führen, dass der ursprüngliche Datensatz (mit einen NULL-Wert in [Email]) nicht in der “System Versioned Temporal Table” eingetragen werden kann, da bei erfolgreicher Anpassung dieses Attribut keine NULL-Werte zulassen würde!

NOT NULL wird zu NULL in einer gefüllten Tabelle

Wie sieht es aber aus, wenn ein Attribut bereits bei der Erstellung eine NOT NULL-Einschränkung besitzt die nachträglich geändert werden soll? Diese Frage wird mit dem folgenden Szenario untersucht und beantwortet:

ALTER TABLE dbo.Customers
ALTER COLUMN [Name] VARCHAR(100) NULL;
GO

Nicht ganz so überraschend ist das Ergebnis – es funktioniert einwandfrei ohne dabei “System Versioning” zu unterbrechen. Die Erklärung für dieses Verhalten liegt im gleichen Verhalten wie bereits im vorherigen Test. In diesem Szenario muss Microsoft SQL Server keine Validierungen durchführen, da die Restriktion NOT NULL zu einem NULL wird. Somit sind leere Werte erlaubt. Unabhängig davon, ob bereits Werte im Attribut stehen, verletzen sie keine NULL-Einschränkung.

Ausblick

Dieser Artikel ist der erste Artikel in einer Reihe von unterschiedlichen Artikeln zum Thema “System Versioned Temporal Tables”. Ich bin sehr an Fragen zu diesem Thema interessiert und sofern ein Leser Fragen zu diesem Thema hat, möchte ich sie sehr gerne aufgreifen und darüber bloggen. Fragen können jederzeit über das Kontakt-Formular gesendet werden.

Herzlichen Dank fürs Lesen!

Temporal Tables – Behandlung von NULL-Einschränkungen