Alles Neue macht der Mai

Die neuen T-SQL-Befehle für SQL Server 2012
Kommentare

Die neueste Version von Microsofts SQL Server 2012 bietet im Gegensatz zu seinem Vorgänger eine ganze Reihe von Neuerungen und Verbesserungen für die servereigene T-SQL-Sprache. Dieser Artikel stellt sie in einer kurzen Zusammenfassung vor.

SQL Server 2012 hat für den interessierten Programmierer weit mehr als nur das neue und wirklich phantastische FILETABLE-Feature [1] zu bieten. Dieser Artikel stellt die T-SQL-Neuerungen kurz vor und gibt Ihnen so die notwendige Übersicht für das nächste Projekt. Einige der Abfragen in den Listings dieses Artikels beziehen sich dabei auf einfache imaginäre Tabellen oder auf die der altbekannten Adventure-Works-Datenbank, die es nun auch für SQL Server 2012 gibt.

Die neuen T-SQL-Befehle für SQL Server 2012

Sequenzen

Sequenzen sind definierbare, numerische Nummernkreise, aus denen Werte (einzelne oder mehrere auf einmal) entnommen werden können. Solch eine Sequenz kann über einen Anfangs- und Endwert verfügen, aber auch zirkulär sein. Für Sequenzen sind Transaktionen nicht sichtbar. Wenn also in einer begonnenen Transaktion Werte aus einer Sequenz abgerufen werden und anschließend die Transaktion zurückgerollt wird, so bleiben diese Werte entnommen. In einem Mehrbenutzersystem wie dem SQL Server, in dem Anfragen parallel bearbeitet werden, ist dies auch nicht anders realisierbar (bei der Vergabe von Identitätswerten ist die Situation ähnlich gelagert). Weitere Details finden Sie unter [2]. Listing 1 zeigt, wie Sequenzen erstellt und abgerufen werden können.

-- Sequenz erstellen
CREATE SEQUENCE MeineSequenz
    AS INT
    MINVALUE 1
    NO MAXVALUE
    INCREMENT BY 3
    START WITH 1;

-- Genau einen Wert aus der Sequenz abrufen
DECLARE @EinzelWert INT = NEXT VALUE FOR MeineSequenz;

-- Mehrere Werte (12 Stück) aus der Sequenz abrufen
DECLARE @firstValue SQL_VARIANT, 
        @lastValue SQL_VARIANT;

EXEC sys.sp_sequence_get_range
    @sequence_name = 'MeineSequenz',
    @range_size = 12,
    @range_first_value = @firstValue OUTPUT,
    @range_last_value = @lastValue OUTPUT;

SELECT FirstValue = CONVERT(INT, @firstValue),
       LastVlaue = CONVERT(INT, @lastValue);

Offset-Klausel

Mit der neuen OFFSET-Klausel ist es nun einfacher, aus einer Ergebnismenge die ersten n-Zeilen zu überspringen. Zusammen mit der FETCH NEXT-Klausel, die die maximale Anzahl der anschließend zurückgelieferten Zeilen begrenzen kann, kann recht einfach und leicht verständlich das gleiche Paging realisiert werden, für das zuvor die ROW_NUMBER()-Funktion inklusive Unterabfrage verwendet werden musste (Listing 2).

-- Paging bis SQL Server 2008 R2
DECLARE @Von INT = 1, @Bis INT = 3;

SELECT * FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY Name) AS 'RowNr', * FROM dbo.Tabelle) T
WHERE RowNr BETWEEN @Von AND @Bis;

-- Paging ab SQL Server 2012
SELECT * FROM dbo.Tabelle 
ORDER BY Name -- Sortierung muss vorhanden sein
OFFSET (@Von - 1) ROWS -- Zeilen überspringen 
FETCH NEXT @Bis - @Von + 1 ROWS ONLY; -- Und dann die gewünschte Zeilenmenge liefern 

TRY_CONVERT

Die CONVERT()-Funktion ist schon lange in der T-SQL verfügbar. Ihr Nachteil ist, dass es zu einem Fehler kommt, wenn der übergebene Wert nicht in den gewünschten Datentyp konvertiert werden kann. Die neue TRY_CONVERT()–Funktion hingegen liefert in einem solchen Fehlerfall lediglich NULL (Listing 3).

-- Liefern bei den korrekten Wert als Integer
SELECT CONVERT(INT, '100') AS ValueInt;
SELECT TRY_CONVERT(INT, '100') AS ValueInt;

-- Erzeugt einen Fehler
SELECT CONVERT(INT, 'A100.000') AS ValueInt;

-- Liefert lediglich NULL
SELECT TRY_CONVERT(INT, 'A100.000') AS ValueInt;

In Kombination mit der ISNULL()-Funktion kann so jeder Import- oder ETL-Prozess robust auf unsaubere Daten mit einem geeigneten Standardwert reagieren.

FORMAT

Auch wenn die Persistenzschicht eigentlich nicht die Ebene sein sollte, die Daten für die Ausgabe formatiert, ist es dennoch praktisch, dass mit der FORMAT()-Funktion nun diese Möglichkeit offen steht. Sowohl die Maske, wie ein Wert genau formatiert werden soll, als auch die dazu zu verwendende Sprache (Kultur) können dafür übergeben werden (Listing 4).

DECLARE @date DATE = '2012-01-12';
DECLARE @amount DECIMAL(12,2) = 1234567.34;
DECLARE @value int = 18;

SELECT FORMAT( @date, 'dd/MM/yyyy', 'en-US' );
SELECT FORMAT( @date, 'dd/MM/yyyy', 'de-DE' );
SELECT FORMAT( @date, 'yyyy-MM-dd' );
SELECT FORMAT( @date, 'dddd, dd. MMM yyyy', 'de-DE' );

SELECT FORMAT(@amount, 'C', 'de-DE')
SELECT FORMAT(@amount, 'C', 'en-US')

SELECT FORMAT(@value, 'X');

-- Mehrere Sektionen für positive, negative Werte und 0
SELECT FORMAT(@amount, '#,##0;(#,##0)');
SELECT FORMAT(-@amount, '#,##0;(-#,##0)');
SELECT FORMAT(0, '#,##0;(-#,##0);(Null)'); 

Besonders interessant ist die Option, bei numerischen Formatierungen bis zu drei Sektionen (jeweils durch Semikolon voneinander getrennt) für jeweils positive, negative Werte und 0 festzulegen. Damit lässt sich z. B. mit dieser Maske #,##0;(-#,##0);(Null) der Wert 42 zu 42,00, -42 zu (-42,00) und 0 zu (Null) formatieren. Das System ist ähnlich dem, das auch die String.Format()-Funktion des .NET Frameworks verwendet.

CONCAT

Zugegeben, der erste Gedanke des Autors über die neue CONCAT()-Funktion war „Wozu das? Der ‚+‘-Operator kann doch auch dazu verwendet werden, um einzelne Werte zu einer Zeichenkette zusammenzusetzen“. Richtig! Nur müssen diese Werte bereits Zeichenketten sein. Ansonsten werden ungewollte Additionen durchgeführt, oder es kommt zu Fehlern. Und da kann die neue CONCAT()-Funktion ihre Stärken ausspielen. Ihr können beliebig viele Werte übergeben werden, die zu einer Zeichenkette konkateniert werden (Listing 5).

-- Führt zu einem Fehler "Conversion failed when converting date and/or time from character string." 
SELECT Name + ' ' + Geburtsdatum FROM Personen;

-- Fügt fehlerfrei alle Inhalte als Zeichenkette zusammen
SELECT CONCAT(Name, ' ', Geburtsdatum) FROM Personen;

Damit sorgt die CONCAT-Funktion für übersichtlichere Abfragen, da die explizite Umwandlung zu einer Zeichenkette (CAST oder CONVERT) entfallen kann.

THROW

Die THROW-Anweisung erlaubt das Auslösen von Fehlern. Solche Fehler können dann entweder in einem TRY/ CATCH –Block im T-SQL selbst (Listing 6) oder von der aufrufenden Codeschicht (z. B. ADO.NET) behandelt werden.

BEGIN TRY
  -- ...
  THROW 51000, 'THROW wirft einen Fehler', 1;
END TRY
BEGIN CATCH
    PRINT 'Fehler erneut werfen';
    THROW;
END CATCH;

Die Funktionsweise ist damit recht ähnlich zu der der altbekannten RAISERROR-Anweisung. Die THROW-Anweisung ist allerdings zudem in der Lage, innerhalb eines CATCH-Blocks den auslösenden Fehler „hochzureichen“, damit der Aufrufer ihn behandeln kann. In Tabelle 1 werden die weiteren Unterschiede zwischen THROW und RAISERROR im Detail gegenübergestellt.

Tabelle 1: Vergleich THROW und RAISERROR

THROW RAISERROR
msg_id optional in sys.messages msg_id in sys.messages
Keine printf-Formatierung printf-Formatierung möglich
Schweregrad (Severity) immer 16 Severity-Parameter

Aufmacherbild: Blank directional road signs over blue sky von Shutterstock / Urheberrecht: Mykhaylo Palinchak

[ header = Die neuen T-SQL-Befehle für SQL Server 2012 – Teil 2 ]

Analytische Funktionen

Mit SQL Server 2012 wird eine ganze Reihe von unterschiedlichen Analysefunktionen eingeführt, die mit bisherigen SQL-Server-Versionen lediglich durch recht komplizierte Abfragen realisiert werden konnten.

FIRST_VALUE und LAST_VALUE

Wie der Name dieser Funktion wohl schon suggeriert, liefert sie den ersten oder letzten Wert aus einer Ergebnismenge. Die dafür notwendige Sortierung wird mittels ORDER BY bestimmt, während die optionale PARTITION BY–Klausel dazu genutzt werden kann, diese Menge in einzelne Abschnitte aufzuteilen, sodass aus jedem Abschnitt der erste und/oder letzte Wert geliefert wird (Listing 7).

SELECT FIRST_VALUE(Name) OVER (PARTITION BY Ort ORDER BY Geburtsdatum ASC) FROM dbo.Personen;

SELECT LAST_VALUE(Name) OVER (PARTITION BY Ort ORDER BY Geburtsdatum ASC) FROM dbo.Personen; 

LAG und LEAD

Die neuen Funktionen LAD und LEAD sind in der Lage, auf Werte folgender (LEAD) oder vorheriger (LAG) Zeilen zuzugreifen. Dazu muss zwingend die Sortierung via ORDER BY im OVER-Zweig existieren, damit die Reihenfolge der Zeilen klar definiert ist. Beide Funktion besitzen mehrere Überladungen, die jeweils auf die nächste bzw. vorherige Zeile zugreifen oder per (positivem) Offset für den Zugriff mehrere Zeilen überspringen. Existiert die entsprechende Zeile nicht, wird entweder NULL geliefert oder der Wert, der bei der 3-Parameter-Überladung übergeben wurde. Listing 8 zeigt die Funktionen.

-- Wert aus der nächsten Zeile
SELECT Name, LEAD(Name) OVER (ORDER BY Geburtsdatum ASC) FROM dbo.Personen;
-- Wert aus der übernächsten Zeile
SELECT Name, LEAD(Name ,2) OVER (ORDER BY Geburtsdatum ASC) FROM dbo.Personen;
-- Wert aus der übernächsten Zeile mit Standardwert '-'
SELECT Name, LEAD(Name ,2, '-') OVER (ORDER BY Geburtsdatum ASC) FROM dbo.Personen;

-- Wert aus vorheriger Zeile
SELECT Name, LAG(Name) OVER (ORDER BY Geburtsdatum ASC) FROM dbo.Personen;
-- Wert aus der Zeile 2 zuvor
SELECT Name, LAG(Name ,2) OVER (ORDER BY Geburtsdatum ASC) FROM dbo.Personen;
-- Wert aus der Zeile 2 zuvor mit Standardwert '-'
SELECT Name, LAG(Name ,1, '-') OVER (ORDER BY Geburtsdatum ASC) FROM dbo.Personen; 

Die Abbildungen 1 und 2 zeigen die Funktionsweise der beiden Funktionen noch einmal anschaulich.

Abb. 1: LEAD liefert den Wert aus einer der nächsten Zeilen

Abb. 2: LAG liefert den Wert aus einer der vorherigen Zeilen

Beide Funktionen können einander übrigens leider nicht ersetzen, da kein negativer Offset-Wert angegeben werden darf.

CUME_DIST

Zur Ermittlung der kumulierten Verteilung [4] einer numerischen Datenmenge bietet SQL Server 2012 die neue CUME_DIST()-Funktion. Mit ihrer Hilfe lassen sich Aussagen treffen, wie z. B. „n % aller Werkstücke sind schwerer als 250 g“. Diese n % umfassen dann alle Werkstücke mit einer Masse ab 250g und vergleichen deren Anzahl mit der Gesamtmenge der Werkstücke.

USE AdventureWorks2012;

SELECT Department, LastName, Rate,
CUME_DIST() OVER (PARTITION BY Department ORDER BY Rate) 
FROM HumanResources.vEmployeeDepartmentHistory AS edh
INNER JOIN HumanResources.EmployeePayHistory AS e  
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department 
    IN (N'Information Services',N'Document Control') 
ORDER BY Department, Rate DESC;
 

Die PARTITION BY-Klausel ist bei dieser Funktion optional (Listing 9).

PERCENT_RANK

Seit SQL Server 2005 gibt es bereits die RANK()- und die DENSE_RANK()-Funktionen, mit deren Hilfe die Position (Rang) einer Zeile in einer Ergebnismenge ermittelt werden konnte [5]. Diese Position wurde jedoch immer mit einer absoluten Zahl angegeben. Die neue PERCENT_RANK()-Funktion liefert im Gegensatz dazu einen relativen Prozentwert als Position, sodass nun einfach Abfragen erstellt werden können, um z. B. die wenigen Zeilen zu ermitteln, die zu den 25 Prozent der besten gehörten. Die dazu notwendige Reihenfolge wird wieder über die ORDER BY-Klausel bestimmt (Listing 10).

USE AdventureWorks2012;

SELECT Department, LastName, Rate, 
PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate)
FROM HumanResources.vEmployeeDepartmentHistory AS edh
    INNER JOIN HumanResources.EmployeePayHistory AS e  
    ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department 
   IN (N'Information Services',N'Document Control') 
ORDER BY Department, Rate DESC; 

Die PARTITION BY-Klausel ist auch bei dieser Funktion wieder optional.

PERCENTILE_CONT/PERCENTILE_DISC

Mit diesen beiden Funktionen mit den etwas unhandlichen Namen PERCENTILE_CONT() und PERCENTILE_DISC() kann der Quantilwert einer sortierten Zahlenreihe ermittelt werden. Wird der Funktion ein Wert von 0,5 übergeben, so wird der statistische Median (auch Zentralwert genannt) berechnet [6]. Durch Werte kleiner 0,5 bzw. größer (erlaubt sind Werte von 0,0 bis 1,0) kann eine Gewichtung nach links bzw. rechts erfolgen. Der Unterschied zwischen beiden Funktionen liegt darin, dass PERCENTILE_CONT() den korrekten Wert interpoliert, egal, ob er in der Wertemenge vorhanden ist, während PERCENTILE_DISC() immer einen Ist-Wert aus der Wertemenge liefert (Listing 11).

USE AdventureWorks2012;

SELECT DISTINCT Name AS DepartmentName,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate) OVER (PARTITION BY Name),
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ph.Rate) OVER (PARTITION BY Name)
FROM HumanResources.Department AS d
INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh 
    ON dh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.EmployeePayHistory AS ph
    ON ph.BusinessEntityID = dh.BusinessEntityID
WHERE dh.EndDate IS NULL; 

Abweichend zu den anderen neuen Analysefunktionen wird hier die Sortierung in der WITHIN GROUP-Klausel festgelegt, sodass in der OVER-Klausel nur noch PARTITION BY oder schlicht nichts definiert werden darf.

Fazit: Die neuen T-SQL-Befehle für SQL Server 2012

SQL Server 2012 bringt eine ganze Reihe von neuen Funktionen und Befehlen für den T-SQL-Sprachumfang mit. Sicherlich wird es so sein, dass die eine Neuerung dabei mehr, die andere dem geneigten Leser weniger wichtig oder gar unnötig erscheint. Doch der Autor ist davon überzeugt, dass gerade Sequenzen, LEAD() und LAG(), TRY_CONVERT() und FORMAT(), Anklang in der Entwicklergemeinde finden werden. Auf ein erfolgreiches Programmieren!

Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

X
- Gib Deinen Standort ein -
- or -