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;

Stichtagsinformationen zu einem Datum

uhr(english Version below)

Durch einen Eintrag auf http://sqlmag.com/t-sql/cheat-sheet-calculating-important-dates habe ich mich inspirieren lassen, eine kleine Funktion aus den Statements zu erstellen, die zu einem Datum Informationen liefert. Ganz praktisch bei Reporting Services oder im PowerBI Umfeld, wenn man in einem Bericht verschiedenen Zeit Informationen zu den Daten anzeigen will, bspw. aus welcher Zeit-Periode abgefragt wurde wenn ein Report bspw. nur einen Stichtag aufnimmt, aber alle Daten aus dem aktuellen Jahr selektiert oder dem Quartal …

 

By an entry on http://sqlmag.com/t-sql/cheat-sheet-Calculating-important-dates I’ve inspired me to create a small function of the statements that delivers information to a date. Useful in Reporting Services or in PowerBI environment, if you will, in a report several times to display information about the data. For example, if a report only takes a date as a parameter, but selects all data from the current year or the last quarter …

Funktion / Function:

CREATE FUNCTION dbo.InformationForADate(@QualifyingDate DATETIME)
RETURNS TABLE 
RETURN (
	SELECT
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) - 1 , '19000101')) AS [FIRST DAY OF LAST YEAR], 
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate), '19000101')) AS [FIRST DAY OF This YEAR],
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 1 , '19000101')) AS [FIRST DAY OF NEXT YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate), '19000101'))) AS [LAST DAY OF Last YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 1 , '19000101')))  AS [LAST DAY OF This YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 2 , '19000101'))) AS [LAST DAY OF NEXT YEAR],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) - 1, '19000101')) AS [FIRST DAY Previous MONTH],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate), '19000101')) AS [FIRST DAY CURRENT MONTH],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 1, '19000101')) AS [FIRST DAY NEXT MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate), '19000101'))) AS [LAST DAY Previous MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 1, '19000101'))) AS [LAST DAY This MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 2, '19000101'))) AS [LAST DAY NEXT MONTH],
	(SELECT DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Yesterday],
	(SELECT DATEADD(d, -0, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Today],
	(SELECT DATEADD(d, 1, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Tomorrow],
	(SELECT DATEADD(ss, (60*60*24)-1, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [11:59:59 Yesterday],
	(SELECT DATEADD(hh, 12, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [Noon Yesterday],
	(SELECT DATEADD(ms, (1000*60*60*24)-2, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [11:59:59.997 Yesterday]);

Abfrage / Query:

DECLARE @QualifyingDate DATETIME = GETDATE();
SELECT * FROM dbo.InformationForADate(@QualifyingDate)

Script zum Erstellen einer Datums-Dimensions Tabelle

PowerPivot_LogoDa ich ständig immer wieder Systeme habe, wo ich keine ausreichende Datums-Dimensions Tabelle vorfinde, bspw. für ein Data Warehouse, habe ich nachfolgendes TSQL Script entwickelt welches mir alle nötigen Felder zurückliefert. Das Script erstellt hier erstmal eine Table Variable und füllt diese, dies sollte man dann durch ein CREATE TABLE austauschen und die Tabelle so erzeugen. Das Script ist teils etwas umfänglich und redundant geschrieben, für aber einen nicht so TSQL Profi ist es so übersichtlich. Gerne dazu auch Feedback!

Frohes Neues Jahr!

Die Tabelle

Das Script

SET NOCOUNT ON;

-- Start Datum ab wann gezählt werden soll
DECLARE @Start DATE = '20000101';
-- Ende Datum bis wann gezählt werden soll
DECLARE @Ende DATE = '20401231';

-- Table Variable; sollte umgestellt werden auf ein CREATE TABLE wenn die Tabelle physisch angelegt werden soll
DECLARE @DateDimension TABLE (
    DateKey INT NOT NULL PRIMARY KEY,
    Tagesdatum DATE NOT NULL,
    TagDerWocheNummer TINYINT NOT NULL,
    Wochentagsname NVARCHAR(10) NOT NULL,
    TagDesMonatsNummer TINYINT NOT NULL,
    MonatsNummer TINYINT NOT NULL,
    Quartal TINYINT NOT NULL,
    QuartalName NVARCHAR(20) NOT NULL,
    KalenderJahr SMALLINT NOT NULL,
    Semester TINYINT NOT NULL,
    QuartalMitJahr NVARCHAR(30) NOT NULL,
    KalenderWoche TINYINT NOT NULL,
    KalenderWocheName NVARCHAR(20) NOT NULL,
    MonatsnummerMitJahr NVARCHAR(20) NOT NULL,
    KalenderWocheMitJahr NVARCHAR(20) NOT NULL,
    Monatsname NVARCHAR(30) NOT NULL,
    SemesterMitJahr NVARCHAR(20) NOT NULL
    );

DECLARE @DateKey INT;
DECLARE @Tagesdatum DATE;
DECLARE @TagDerWocheNummer TINYINT;
DECLARE @Wochentagsname NVARCHAR(10);
DECLARE @TagDesMonatsNummer TINYINT;
DECLARE @MonatsNummer TINYINT;
DECLARE @Quartal TINYINT;
DECLARE @QuartalName NVARCHAR(20);
DECLARE @KalenderJahr SMALLINT;
DECLARE @Semester TINYINT;
DECLARE @QuartalMitJahr NVARCHAR(30);
DECLARE @KalenderWoche TINYINT;
DECLARE @KalenderWocheName NVARCHAR(20);
DECLARE @MonatsnummerMitJahr NVARCHAR(20);
DECLARE @KalenderWocheMitJahr NVARCHAR(20);
DECLARE @Monatsname NVARCHAR(30);
DECLARE @SemesterMitJahr NVARCHAR(20);

DECLARE @inkrement INT = 1

WHILE @Start < @Ende
BEGIN
    
    SET @DateKey = CONVERT(INT, CONVERT(VARCHAR(8), @Start, 112));
    SET @Tagesdatum = @Start;
    SET @TagDerWocheNummer = DATEPART(dw, @Tagesdatum);
    SET @Wochentagsname = DATENAME(dw, @Tagesdatum);
    SET @TagDesMonatsNummer = DATEPART(dd, @Tagesdatum);
    SET @MonatsNummer = MONTH(@Tagesdatum);
    SET @Quartal = DATEPART(qq, @Tagesdatum);
    SET @QuartalName = 'Q ' + CONVERT(VARCHAR, @Quartal);
    SET @KalenderJahr = YEAR(@Tagesdatum);
    SET @Semester = CASE WHEN @MonatsNummer < 6 THEN 1 ELSE 2 END;
    SET @QuartalMitJahr = @QuartalName + ' ' + CONVERT(VARCHAR, @KalenderJahr);
    SET @KalenderWoche = DATEPART(wk, @Tagesdatum);
    SET @KalenderWocheName = 'KW ' + CONVERT(VARCHAR, @KalenderWoche);
    SET @MonatsnummerMitJahr = CONVERT(VARCHAR, @Monatsnummer) + ' ' + CONVERT(VARCHAR, @KalenderJahr);
    SET @KalenderWocheMitJahr = CONVERT(VARCHAR, @KalenderWoche) + ' ' + CONVERT(VARCHAR, @KalenderJahr);
    SET @Monatsname = DATENAME(m, @Tagesdatum);
    SET @SemesterMitJahr = CONVERT(VARCHAR, @Semester) + ' ' + CONVERT(VARCHAR, @KalenderJahr);
    
    -- Auch hier muss eine Anpassung dann auf die physische Tabelle erfolgen
    INSERT INTO @DateDimension
        ( DateKey, Tagesdatum, TagDerWocheNummer, Wochentagsname, TagDesMonatsNummer, MonatsNummer,
          Quartal, QuartalName, KalenderJahr, Semester, QuartalMitJahr, KalenderWoche, KalenderWocheName,
          MonatsnummerMitJahr, KalenderWocheMitJahr, Monatsname, SemesterMitJahr)
        VALUES 
        (@DateKey, @Tagesdatum, @TagDerWocheNummer, @Wochentagsname, @TagDesMonatsNummer, @MonatsNummer,
         @Quartal, @QuartalName, @KalenderJahr, @Semester, @QuartalMitJahr, @KalenderWoche, @KalenderWocheName,
         @MonatsnummerMitJahr, @KalenderWocheMitJahr, @Monatsname, @SemesterMitJahr);
         
    SET @Start = DATEADD(day, @inkrement, @Start);
END

SELECT * FROM @DateDimension

TSQL: Partition by in Kombination mit einem DELETE

Nicht ganz so spektakulär, aber vor kurzem hatte ich das Problem, dass ich mit PARTITION BY Datensätze selektieren wollte, die ich danach löschen wollte.Vorab einfach mal, was macht den überhaupt ein PARTITION BY? Eigentlich sehr simpel, ich selektiere Datensätze, versehe diese mit einer Row_Number damit jeder DS eine eindeutige ID erhält und danach Gruppiere ich diese um somit Partitionen von Datensätzen zu bilden.

Beispiel anhand der Northwind Datenbank:

SELECT * FROM (
SELECT CustomerID, PostalCode, City, row_number()
OVER (Partition by PostalCode, City ORDER BY PostalCode, City) As Row
FROM dbo.Customers) t
WHERE t.Row > 1

Ergebnis:

 

 

 

Nun den, so haben wir alle Datensätze wo ich eine doppelte PLZ und einen doppelten Ort habe, in einer Partition. Aber das Partion by ist ja schnell geschrieben, aber die Partition bilde ich ja in der Feldliste des SELECT’s, und nicht in der WHERE Condition. Wie lösche ich nun diese Datensätze?

Ganz einfach … aber vielleicht auch etwas ungewöhlich(?)

DELETE FROM t from  (
SELECT CustomerID, PostalCode, City, row_number()
OVER (Partition by PostalCode, City ORDER BY PostalCode, City) As Row
FROM dbo.Customers2) t
WHERE t.Row > 1

 

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!

SQL Server Testprogramm

Nachdem ich nun immer wieder mit ODBC Verbindungen experimentiert habe, wenn ich einen Clsuter oder einen Spiegel installiert habe, um die Verbindungen und den Failover zu testen, habe ich nun ein VB.NET Tool auf Basis von .NET 4.0 programmiert.
Das Tool kann eine einfache SQL Server Verbindung zu einer beliebigen Instanz herstellen und ruft jede Sekunde Ergebnisse einer vordefinierten Abfrage ab und stellt diese in der Konsole dar. Weiterhin kann man damit auch eine Verbindung zu einem Spiegelpartner herstellen, um hier auch den Failover wie bei einem Cluster zu testen.

Der Code befindet sich hier als PDF zum Download. Kompilieren kann man diesen simpel mit mit dem kostenfreien Visual Studio 2010 und auf dem Client-Rechner wird .NET 4.0 zum Ausführen benötigt.

Hinweis: Der Code wurde schnell und einfach runter programmiert – hier geht es rein um Funktionalität :-)

SQLServerConnector Download