User defined types in SQL Server

Zuletzt hatte ich das Problem, dass ich temporäre Daten in einer TABLE-Variable hatte und diese in einer Tabellenwert Funktion weiter verarbeiten musste. Problem dabei war, wie gebe ich meine Tabelle an die Funktion weiter?

Die erste Idee war, dass ich eine temporäre Tabelle erstelle und die Daten dort platziere und dann in der Funktion lese – tsja, dass geht leider nicht, bzw. dies lässt der SQL Server nicht zu.

Okay, und nun? Da diese Funktionalität ständig zur Verfügung (zur Verwendung in einem SSRS Bericht) stehen musste, brauchte ich eine stabile und multiuser-fähige Lösung.

Nächste Idee wäre, eine physische Tabelle zu erstellen und darin die Daten zu speichern. Für die multiuser-Fähigkeit könnte ich Prozess- oder die Benutzer ID aufnehmen… ist aber irgendwie umständlich, da einerseits Berechtigungen auf die Tabelle erteilt werden müssen, Indizierung, Speicher etc …

Die Lösung war nun, benutzerdefinierte Typen im SQL Server zu erstellen. Bisher kannte ich das mehr von CLR’s, aber der SQL Server bietet bereits seit Version 2008 die Möglichkeit, eigene Typen für Tabellenwert-Variablen zu erstellen.

Via CREATE TYPE wird der Typ mit der entsprechenden Definition erstellt, bspw.:

CREATE TYPE tmpDataType As Table (
 data VARCHAR(255));
GO

Aus diesem Type kann dann ein entsprechendes Objekt erstellt werden:

DECLARE @tmpDataTable As tmpDataType;

Dieses Objekt kann dann wie eine bisherige Tabellenwert Variable mit Daten befüllt werden. Das Verhalten ist völlig identisch mit den bisherigen Tabellewert Variablen, die Daten können darin manipuliert, ergänzt oder gelöscht werden.  Der eigentliche Vorteil ist nun, dass ich dieses Objekt an eine Funktion weitergeben kann:

CREATE FUNCTION dbo.testFunc(@tmpDataTable tmpDataType READONLY)
RETURNS TABLE
RETURN (
 -- return the data or do somethin else ...
 SELECT data FROM @tmpDataTable
);
GO
DECLARE @tmpDataTable As tmpDataType;
INSERT INTO @tmpDataTable
SELECT data FROM dbo.data;
SELECT * FROM dbo.testFunc(@tmpDataTable);
GO

 

Das war’s schon!

 

 

Vollständiges Demo-Script (Verwendung auf eigene Gefahr!)

-- create a tmp.-DB
CREATE DATABASE TYPETEST;
GO
USE TYPETEST;
GO
-- create a demo table
CREATE TABLE dbo.data (
 id INT not null primary key identity(1,1)
 , data VARCHAR(255));
GO
-- insert demo data
INSERT INTO dbo.data (data) VALUES ('VAL1'), ('VAL2'), ('VAL3');
GO
-- create a temp table
CREATE TABLE ##tmpData (data VARCHAR(255));
GO
-- fill the demo table
INSERT INTO ##tmpData
SELECT data FROM dbo.data;
GO
-- create a function to read the demo data from the temp table
--CREATE FUNCTION dbo.testFunc()
--RETURNS TABLE
--RETURN (
-- SELECT data FROM ##tmpData
--);
--GO
-- Does not work, in functions you do not have the option to read from temporaly tables ...
-- and now?!
-- but how can i move my temporally data to my function?
-- one option is to create a pyhsically table with the process id ... but do you need this for sure?!
-- NO!
-- create a user type table variable in the database (be carefull, this is in the database a global type!!
CREATE TYPE tmpDataType As Table (
 data VARCHAR(255));
GO
-- create a function with a parameter from the user defined typ
CREATE FUNCTION dbo.testFunc(@tmpDataTable tmpDataType READONLY)
RETURNS TABLE
RETURN (
 -- return the data or do somethin else ...
 SELECT data FROM @tmpDataTable
);
GO
-- create a object from our new type
DECLARE @tmpDataTable As tmpDataType;
-- fill our object with demo data
INSERT INTO @tmpDataTable
SELECT data FROM dbo.data;
-- execute our new function
SELECT * FROM dbo.testFunc(@tmpDataTable);
GO
-- clean up
DROP TABLE ##tmpData;
GO
USE tempdb;
GO
DROP DATABASE TYPETEST;

Markierte Transaktionen – Auf die Transaktion genaues zurückrollen!

Bei größeren Transakionen bietet es sich an, zu wissen wann und welche Aktion die Änderung in der Datenbank verursacht hat, um bei einem Restore der Datenbank mit entsprechenden Transaktions-Protokoll Sicherungen auch wieder auf genau diesen Zeitpunkt zurücksichern zu können. Besser als sich die Zeit zu notieren, ist es beim Ausführen einer Transaktion diese zu markieren. Dafür erhält die Transaktion einen Namen und kann dann bei einem Restore mit einem Haltepunkt angesteuert werden.

Hier das Beispiel:

  1. Zuerst brauchen wir wieder eine Datenbank mit Daten:
CREATE DATABASE markedtran
GO
USE markedtran
GO
CREATE TABLE dbo.test (id int not null primary key, test varchar(255) null);
go
insert into test (id, test) values (1, 'a');
insert into test (id, test) values (2, 'b');
insert into test (id, test) values (3, 'c');
insert into test (id, test) values (4, 'd');
insert into test (id, test) values (5, 'e');
GO
  • Danach sichern wir diese mit einer Vollsicherung, fügen erneut einen Datensatz ein (damit eine Änderung erfolgt) und sichern danach diese Änderung mit einer Transaktionslog Sicherung:
BACKUP DATABASE [markedtran] TO  DISK = N'C:\temp\markedtran.bak' WITH FORMAT, INIT,  NAME = N'markedtran-Vollständig Datenbank Sichern', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
INSERT INTO dbo.Test (id, test) VALUES(6, 'f');
GO
BACKUP LOG [markedtran] TO  DISK = N'C:\temp\markedtran.bak' WITH NOFORMAT, NOINIT,  NAME = N'markedtran-Transaktionsprotokoll  Sichern', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
  • Nun starten wir eine Transaktion, benennen diese, Aktualisieren entsprechene Datensätze mit einem Update und bestätigen diese Transaktion:
BEGIN TRANSACTION HierBinIch
WITH MARK 'Beginne die große Aktualisierung';
GO
UPDATE dbo.test SET test = 'd' WHERE id < 6
GO
COMMIT TRANSACTION HierBinIch
GO
  • Nun fügen wir nochmals einen Datensatz ein (nur um zu sehen, das nach einem Restore mit der nachfolgenden Transaktionslogsicherung dieser Datensatz dann nicht verfügbar ist, wenn nur bis zum markierten Punkt zurückgesichert wurde:
insert into test (id, test) values (9, 'z');
BACKUP LOG [markedtran] TO  DISK = N'C:\temp\markedtran.bak' WITH NOFORMAT, NOINIT,  NAME = N'markedtran-Transaktionsprotokoll  Sichern', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
  • Nun sichern wir die Vollsicherung zurück mit den beiden Transaktionslogsicherungen im Anschluss. Dabei wird dann der markierte Punkt als „Breakpoint“ angegeben:
RESTORE DATABASE [markedtran] FROM  DISK = N'C:\temp\markedtran.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO
RESTORE LOG [markedtran] FROM  DISK = N'C:\temp\markedtran.bak' WITH  FILE = 2, NORECOVERY, NOUNLOAD,  STATS = 10
GO
RESTORE LOG [markedtran] FROM  DISK = N'C:\temp\markedtran.bak' WITH  FILE = 3,  NOUNLOAD,  STATS = 10,  STOPBEFOREMARK = N'HierBinIch'
GO

Das war’s!

Komprimierung von Partitionen

Datenbanken wachsen. Wenn der Speicherplatz knapp wird, können einzelne Tabellen komprimiert werden, jedoch sollte dies mit Vorsicht angegangen werden, denn wenn hoch frequentierte Tabellen komprimiert werden, kostet das unnötig Zeit und CPU Ressourcen. Daher sollte diese Technologie eher bei partitionierten Tabellen zum Einsatz gebracht werden, wenn man ältere und nicht häufig verwendete Daten von den aktuellen differenziert werden können. Die Partitionen können dann durch die Zeilen/Seiten Komprimierung des SQL Servers komprimiert werden.

Hinweis:
Dieser Artikel bezieht sich auf die folgende Artikel in den Beispielen:
Patritionierung von bestehenden Tabellen
Partitionierung von SQL Server Tabellen

Nimmt man das Beispiel „Patritionierung von bestehenden Tabellen“ als Grundlage und fügt dort weitere Daten ein (pro Partition 2000 Zeilen):
DECLARE @counter INT = 9
WHILE @counter < 10000
BEGIN
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Januar 2008', '20080308', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'März 2009', '20090308', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Februar 2010', '20100209', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Januar 2011', '20110120', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Januar 2012', '20120115', '300');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
END

Nun ergibt sich folgende Speicherbelegung (SQL Server Bericht, Speicherbelegung durch Partitionen):

Wenn nun die Partitionen durch folgendes Statement komprimiert werden …
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 1 WITH(DATA_COMPRESSION = ROW )
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 2 WITH(DATA_COMPRESSION = ROW )
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 3 WITH(DATA_COMPRESSION = ROW )
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 5 WITH(DATA_COMPRESSION = ROW )

… ergibt sich folgendes Bild:

Gesamt ergibt das in diesem Beispiel ~100 KB an Speicherplatz-Gewinn. Die Komprimierung kann auch hervorragend durch den Assistenten des SMSS durchgeführt werden.

Partitionierung von bestehenden Tabellen

In dem Artikel Partitionierung von SQL Server Tabellen habe ich bereits beschrieben, wie man die Partionierung auf eine neue Tabelle anwendet. In der Praxis führt man solche Technologien doch eher an bestehenden Tabellen ein, wenn man merkt, das die Tabelle doch größer als erwartet wird. Daher stellt sich die Frage: Wie partitioniere ich eine bestehende Tabelle?

Gründsätzlich könnte man die bestehende Tabelle auslagern, neu erstellen und dann wieder alle Daten reinkopieren. Das ist u.U nicht immer ganz einfach, besonders dann, wenn die Tabelle referentielle Integritäten besitzt (Fremdschlüssel, bzw. „Verlinkungen in andere Tabellen“) und man durch ein Löschen und Neuerstellen doch mehr Arbeit produziert, als notwendig.

Zuerst erstellen wir uns die Umgebung (auf Basis des Beispiels von dem Artikel: Partitionierung von SQL Server Tabellen):
CREATE TABLE dbo.buchungen (
[uid] INT NOT NULL PRIMARY KEY,
[name] VARCHAR(50),
[datum] DATETIME DEFAULT GETDATE() NOT NULL,
[wert] MONEY);
GO

CREATE TABLE dbo.buchungen_Log (
[uid] INT NOT NULL PRIMARY KEY,
[buchungen_uid] INT NOT NULL);
GO

ALTER TABLE dbo.buchungen_Log ADD CONSTRAINT FK_buchungen_Log_buchungen FOREIGN KEY
(buchungen_uid) REFERENCES dbo.buchungen
(uid)
GO

INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (1, 'Januar 2008', '20080308', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (2, 'März 2009', '20090308', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (3, 'Februar 2010', '20100209', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (4, 'Januar 2011', '20110120', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (5, 'Januar 2012', '20120115', '300');
GO
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (1, 1);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (2, 2);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (3, 3);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (4, 4);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (5, 5);
GO

Nun haben wir die Tabelle „Buchungen“ die die zu partitionierenden Daten enthält und auch den Fremschlüssel für die Tabelle „buchungen_log“ stellt.
Ansich geht man nun wir bei der Partitionierung einer neuen Tabelle vor, indem man zuerst die Dateigruppen und die jeweiligen Dateien erstellt:

ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
GO

ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2009.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2010.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2011.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2012.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\Archiv.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun benötigt man die Partitionierungs-Funktion und das Schema für die Datenverteilung:
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
GO

ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2009.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2010.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2011.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2012.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\Archiv.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun wird die Partionierungsfunktion wie auch das Schema erstellt, um die Datentrennung zu regeln:
CREATE PARTITION FUNCTION ArchivierungsFunktion (DATETIME) AS RANGE RIGHT
FOR VALUES (
'20090101' -- Kleiner als 01.01.2009
, '20100101' -- Kleiner als 01.01.2010, größer als 01.01.2009
, '20110101' -- Kleiner als 01.01.2011, größer als 01.01.2010
, '20120101' -- Kleiner als 01.01.2012, größer als 01.01.2011
);

CREATE PARTITION SCHEME ArchivierungsSchema AS PARTITION ArchivierungsFunktion
TO ('Archiv', 'Archiv2009', 'Archiv2010', 'Archiv2011', 'Archiv2012');

Nun benötigt man einen Primär Schlüssel, wo jedoch auch das Datums Feld (Kennzeichen für die Partionierung) enthalten ist. Dafür muss jedoch der Schlüssel erstmal entfernt werden, wie auch die Fremschlüssel Verbindung:
ALTER TABLE [dbo].[buchungen_Log] DROP CONSTRAINT [FK_buchungen_Log_buchungen]
GO
ALTER TABLE [dbo].[buchungen] DROP CONSTRAINT [PK__buchunge__DD7012647F60ED59]
GO

Nun kann der Primärschlüssel erstellt werden, der dann an die Partionierungsfunktion gehängt wird (dieser muss ein Gruppierter Index sein, eine HEAP Tabelle (ohne Gruppierten Index) wird hier nicht unterstützt, da die Daten sonst nicht verschoben werden können):
ALTER TABLE dbo.buchungen ADD CONSTRAINT
PK_buchungen PRIMARY KEY CLUSTERED
([uid], [datum]) on ArchivierungsSchema(datum);

Kontrolliert werden kann dies folgendermaßen:
SELECT
sys.filegroups.name 'Partitionsname' , sys.partitions.rows 'Zeilenanzahl'
FROM
sys.partitions INNER JOIN sys.allocation_units
ON sys.allocation_units.container_id = sys.partitions.hobt_id
INNER JOIN sys.filegroups ON sys.filegroups.data_space_id = sys.allocation_units.data_space_id
WHERE sys.partitions.object_id = OBJECT_ID('dbo.buchungen')

Das wars.

Datenbank Snapshots

Ab Microsoft SQL Server 2005 ist es möglich Datenbank Snapshots zu erstellen (ab der Enterprise Edition). Durch diese Technologie wird ein Abbild der Quell-Datenbank in eine Datendatei (je nach Menge der enthaltenen Datendateien) geschrieben, womit es möglich ist, die Datenbank abzufragen ohne das Laufende Änderungen aus der eigentlichen Datenbank in das Ergebnis aus dem Snapshpt einfließen. Der Datenbank Snapshot ist schreibgeschützt (verfügt über kein Transaktionsprotokoll) und es können keine Daten- oder Strukturellen Änderungen am Snapshot vorgenommen werden, lediglich Lesende Anweisungen sind zugelassen und es können auch keine Benutzer oder Rollen hinzugefügt werden.

Zur Praxis: Ein Datenbank Snapshot wird folgendermaßen erzeugt:
CREATE DATABASE DBSNAPSHOT ON (name=LOGISCHER_DATEINAME, filename='PHSISCHER_PFAD_UND_DATEINAME') AS SNAPSHOT OF DATENBANK
Durch diesen Befehl wird erstmal ein Container erzeugt in dem keine Datenseiten enthalten sind. Sobald eine Datenseite in der Quell-Datenbank verändert wird, wird diese in den Snapshot rüberkopiert. Das bedeutet, das Abfragen auf den Snapshot auf unveränderte Daten in der Produktiven Datenbanken in die eigentliche Datenbank umgeleitet werden. Sobald Daten verändert worden sind, werden diese Datenseiten aus dem Snapshot gelesen.
Strukturelle Änderungen (DDL) die auf der Produktiven Datenbank vorgenommen werden, werden nicht in den Snapshot übernommen, sondern sind lediglich in der eigentlichen Datenbank enthalten, da diese nicht zum Zeitpunkt des Snapshots existierten.

Besteht eine Datenbank aus meheren Datendateien, müssen diese ebenfalls mit angegeben werden. Es können keine Dateien oder Dateigruppen ausgelassen werden.

Möchte man eine Datenbank wiederherstellen, muss zuerst der Datenbank Snapshot entfernt werden, da ansonsten keine Rücksicherung möglich ist. Das begründet sich durch die Verlinkung der Seiten, da der Snapshot auf die unveränderten Datenseiten der Produktiven Datenbank zurückgreift und der Snapshot sonst inkonsistent wäre. Ein Snapshot wird durch ein einfaches DROP DATABASE entfernt.

Weiter kann man auch aus einem Datenbank Snapshot Datenänderungen zurücknehmen, indem man die Datenbank aus dem Snapshot wiederherstellt:
RESTORE DATABASE DATENBANK FROM DATABASE_SNAPSHOT = 'DBSNAPSHOT'
Dadurch werden alle im Snapshot enthaltenen Datenseiten in die eigentliche Datenbank zurückgeschrieben, bzw. überschrieben.

Wichtig: Wenn man dies auf einer Datenbank macht, die mit einer Transaktionsprotokollsicherung gesichert wird, muss danach eine Vollsicherung durchgeführt werden, da ab diesem Zeitpunkt keine konsistente Vollsicherung mehr vorliegt.

Für das Verwalten/Erstellen/Löschen von Snapshots benötigt man die Server-Sicherheitsrolle „dbcreator“.

Fazit:
Snapshots eigenen sich hervorragend für Auswertungszwecke von Daten aus einem Live System, sofern hier nicht das Motto gilt: „Traue keiner Statistik, die Du nicht selbst gefälscht hast“. :-)
Ansonsten eignet sich dies auch als „kleines Backup“ vor einer größeren Datenänderung.