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. Dieser Artikel ist der zweite Artikel in einer Artikelreihe über “System versioned Temporal Tables” Dieser Artikel beschäftigt sich mit der Frage, ob man mit [sp_rename] Tabellen / Spalten von System Versioned Temporal Tables umbenennen kann.
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” (englisch) bei Microsoft nachgelesen werden.
Umbenennung von Metadaten
Frage: “…funktioniert sp_rename und wird die Umbenennung durchgereicht?” Eigentlich besteht diese Frage aus zwei Elementen. Die Antwort ist “Ja” und “Nein”.
- JA – Objekte können mit der Systemprozedur sp_rename jederzeit umbenannt werden. Das Umbenennen von Objekten wird nicht dadurch blockiert, dass eine Tabelle als “System Versioned Temporal Table” gekennzeichnet und eingebunden ist.
- NEIN – wenn eine Tabelle, die als System Versioned Temporal Table dient, umbenannt wird, wird nicht automatisch die History-Tabelle mit umbenannt. Jedoch muss man beim Umbenennen zwei wichtige Aspekte beachten; mit [sp_rename] können nicht nur Tabellen umbenannt werden sondern auch Spaltennamen! Wie unterschiedlich [sp_rename] auf beide Objekttypen reagiert, zeigen die die folgenden Beispiele und Erklärungen.
Wie funktioniert sp_rename?
Bei [sp_rename] handelt es sich um eine Systemprozedur, die von Microsoft bereitgestellt wird. Mit [sp_rename] kann der Name eines benutzerdefinierten Objekts in der aktuellen Datenbank geändert werden. Bei diesem Objekt kann es sich um eine Tabelle, einen Index, eine Spalte, einen Aliasdatentyp oder einen CLR-benutzerdefinierten Typ (Common Language Runtime) von Microsoft .NET Framework handeln.
Umbenennen von Tabellennamen
Interne Verwaltung von Tabellenobjekten
Obwohl ein Objekt immer eindeutig benannt werden muss, verwaltet Microsoft SQL Server alle Objekte intern mittels einer OBJECT_ID. Dieses Verfahren gilt sowohl für Tabellen als auch für Indexe, Einschränkungen, usw. Wir lesen und adressieren Objekte nach ihren Namen aber intern verwenden viele Funktionen und Prozeduren für den Zugriff die interne OBJECT_ID.
Mit dem folgenden Code wird eine Tabelle [dbo].[Customers] erzeugt. Ebenfalls wird für die Speicherung der historischen Daten eine entsprechende Tabelle mit gleichen Schemaeigenschaften erstellt.
USE [temporal];
GO
-- 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
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
ALTER TABLE dbo.Customers SET(SYSTEM_VERSIONING = ON); GO
Ein Blick hinter die Kulissen zeigt, wie die beiden Objekte in der Datenbank verwaltet werden:
SELECT object_id,
QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) AS TableName,
temporal_type,
temporal_type_desc,
history_table_id
FROM sys.tables
WHERE object_id = OBJECT_ID(N'dbo.Customers', N'U')
UNION ALL
SELECT object_id,
QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) AS TableName,
temporal_type,
temporal_type_desc,
history_table_id
FROM sys.tables
WHERE object_id = OBJECT_ID(N'history.Customers', N'U');
GO
In der Abbildung sind die Abhängigkeiten der beiden Tabellen untereinander gut zu erkennen. Wichtig für die Fragestellung ist, dass beide Tabellen unterschiedliche [object_id] besitzen! Den für die Umbenennung von “Objekten” verantwortliche Code aus [sp_rename] zeigt die folgende Abbildung.
Der Codeausschnitt zeigt, dass die Umbenennung einer Tabelle (%%object) auf der korrespondierenden object_id (@objid) basiert. Das Objekt mit der ID = @objid erhält den Namen @newname. Sollte die Umbenennung einen Fehler verursachen, wird als Fehlermeldung ausgegeben, dass ein Objekt mit gleichem Namen bereits in der Datenbank existiert! Unabhängig von Art der Programmierung sollte jedoch klar sein, dass [sp_rename] für Objekte nur auf die ObjektId verweist und keine weiteren Tabellenabhängigkeiten überprüft und/oder anpasst.
EXEC sp_rename @objname = N'dbo.Customers', @newname = N'NewCustomers'; GO
Für das Beispiel wird die Tabelle [dbo].[Customers] in [dbo].[NewCustomers] umbenannt. Diese Operation wird ohne Fehlermeldungen durchgeführt und die Überprüfung der Tabellennamen zeigt, dass ausschließlich die „System Versioned Temporal Table“ umbenannt wurde; die „History Table“ blieb unberührt!
Der Name hat sich geändert aber die [object_id] bleibt von einer Neubenennung unberührt. Der Name einer Tabelle ist für eine “System Versioned Temporal Table” nicht wichtig. Die Verwaltung erfolgt über die [object_id].
Umbenennung von Spaltennamen
Die Systemprozedur [sp_rename] wird nicht nur für die Umbenennung von Tabellen verwendet sondern kann auch verwendet werden, um Attribute einer Tabelle umzubenennen. Bei den Tests müssen zwei Situationen berücksichtigt werden:
Umbenennen von Attributen bei aktivierter “System Versioned Temporal Table”
Umbenennen von Attribut aus “System Versioned Temporal Table”
Im ersten Beispiel wird versucht, das Attribut [Name] aus der Tabelle [dbo].[Customers] neu zu benennen. Dabei bleibt die Systemversionierung aktiviert. Das Ergebnis sollte nicht überraschen – die Umbenennung funktioniert einwandfrei und ohne Fehler.
EXEC sp_rename @objname = N'dbo.Customers.Name', @newname = N'CustomerName', @objtype = N'COLUMN'; GO
Microsoft SQL Server kann das Attribut in der „System Versioned Temporal Table“ ohne Probleme ändern und wendet die Anpassungen automatisch auf die „History Table“ an.
Umbenennen von Attribut aus “History Table”
Im nächsten Beispiel soll versucht werden, das geänderte Attribut [CustomerName] wieder in [Name] umzubenennen. Diesmal wird die Prozedur auf die “History Table” angewendet. Auch dieses Ergebnis sollte nicht überraschen; der Versuch schlägt fehl mit dem Hinweis, dass Änderungen an der “History Table” nicht erlaubt sind!
EXEC sp_rename @objname = N'history.Customers.Name', @newname = N'Name', @objtype = N'COLUMN'; GO
Die Fehlermeldung zeigt, dass eine Änderung an der aktivierten “History Table” nicht erlaubt ist. Die Fehlermeldung ist korrekt, wenn man in die Sicherheitsprinzipien von “System Versioned Temporal Tables” kennt. Hier heißt es: “When SYSTEM_VERSIONING is ON users cannot alter history data regardless of their actual permissions on current or the history table. This includes both data and schema modifications.”
Somit ist klar, warum weder Datensätze noch Schemamodifikationen möglich sind – die Sicherheitsrichtlinien von Microsoft SQL Server lassen Manipulationen an der “History Table” nicht zu, so lange die die Systemversionierung aktiviert ist.
Umbenennen von Attributen bei deaktivierter “System Versioned Temporal Table”
Mit dem letzten Beispiel wird geprüft, ob eine Änderung der Attribute ohne aktivierter Systemversionierung möglich ist. Hierzu wird der folgende Code ausgeführt:
-- Deactivate System Versioning and change the attributes
ALTER TABLE dbo.Customers
SET (SYSTEM_VERSIONING = OFF);
GO
-- Now we can change the attributes in the history table
EXEC sp_rename
@objname = N'history.Customers.Name',
@newname = N'CustomerName',
@objtype = N'COLUMN';
GO
-- can we now activate System Versioning?
ALTER TABLE dbo.Customers
SET
(
SYSTEM_VERSIONING = ON
(HISTORY_TABLE = History.Customers)
);
GO
Zunächst wird die Systemversionierung deaktiviert und anschließend das Attribut [Name] umbenannt. Dieser Schritt ist nur möglich, da keine Systemversionierung mehr aktiviert ist. Versucht man anschließend, die Systemversionierung wieder zu aktivieren, erhält man den folgenden Fehler:
Die Systemversionierung kann nicht mehr aktiviert werden, da nach der Änderung des Attributs die Metadaten beider Tabellen unterschiedlich sind. Um die Systemversionierung wieder erfolgreich aktivieren zu können, muss das geänderte Attribut auch in der “System Versioned History Table” geändert werden.
EXEC sp_rename @objname = N'dbo.Customers.CustomerName', @newname = N'Name', @objtype = N'COLUMN'; GO
Nachdem die Namen der Attribute in beiden Tabellen wieder identische Namen besitzen, lässt sich die Tabelle erneut als “System Versioned Temporal Table” konfigurieren.
Zusammenfassung
Das Umbenennen von “System Versioned Temporal Tables” sowie deren “History Tables” ist mit [sp_rename] möglich, da für die interne Verwaltung nicht der Name der Tabelle entscheidend ist sondern die ObjektId. Sofern Attribute in einer Relation umbenannt werden sollen, können nur Attribute der “System Versioned Temporal Table” ohne Deaktivierung umbenannt werden. Eine Neubenennung von Attributen in der „History Table“ kann nur durchgeführt werden, wenn die Systemversionierung aufgelöst wird.
Bisher erschienen Artikel zu System Versioned Temporal Tables
Herzlichen Dank fürs Lesen!
