Graph Data Processing

Eine größere Neuerung ist der Einbau einer neuen Art von Tabellen: „Graph Tables“ (Graphtabellen). Mit diesen lassen sich Beziehungen zwischen Tabellen definieren. Zu diesem Zweck gibt es Knotentabellen (Nodes), die Benutzerdaten speichern und Kantentabellen (Edges), die den Beziehungen zwischen diesen Platz bieten. Insgesamt ist dies ein alternativer Ansatz zu relationalen Beziehungen zwischen Tabellen, um Daten mit Beziehungen zu speichern und abzufragen. Der wohl größte Unterschied dabei dürfte sein, dass eine Beziehung (Eintrag in Edge-Tabelle) zwischen jeder existierenden Node Table definiert werden kann; anders als relationale Beziehungen (mit Fremdschlüsseln), die fest zwischen zwei Tabellen angelegt werden.
Wenn also Beziehungen zwischen Bieren, Brauereien, Städten und Ländern abgebildet werden sollen (Abb. 1), dann sieht das T-SQL wie in Listing 1 aus.

Abb. 1: Biere, Brauereien, Städte und Länder

Listing 1
/*------------------
Knotentabellen
-------------------*/

-- Biere
CREATE TABLE [dbo].[Biere]
(
  Id INT PRIMARY KEY,
  [Name] VARCHAR(50),
) AS NODE;
CREATE NONCLUSTERED INDEX idx_Biername ON [dbo].[Biere] ([name]);

-- Brauereien
CREATE TABLE [dbo].[Brauereien]
(
  Id INT PRIMARY KEY,
  [Name] VARCHAR(50),
) AS NODE;
CREATE NONCLUSTERED INDEX idx_Brauereiname ON [dbo].[Brauereien] ([name]);

-- Städte
create table [dbo].[Staedte]
(
  Id INT PRIMARY KEY,
  [Name] VARCHAR(50),
) AS NODE;
CREATE NONCLUSTERED INDEX idx_Stadtname ON [dbo].[Staedte]([name]);

-- Länder
CREATE TABLE [dbo].[Laender]
(
  Id INT PRIMARY KEY,
  [Name] VARCHAR(50),
) AS NODE;
CREATE NONCLUSTERED INDEX idx_Laendername ON [dbo].[Laender]([name]);

/*-------------
Kanten Tabellen
---------------*/

-- Gebraut von
CREATE TABLE [dbo].[GebrautVon] AS EDGE;
CREATE UNIQUE NONCLUSTERED INDEX idx_GebrautVon ON [dbo].[GebrautVon] ($from_id, $to_id);

-- Brauereistandort
CREATE TABLE [dbo].[Brauereistandort] AS EDGE;
CREATE UNIQUE NONCLUSTERED INDEX idx_Brauereistandort ON [dbo].[Brauereistandort] ($from_id, $to_id);

-- Im Land
CREATE TABLE [dbo].[LiegtInLand] AS EDGE;
CREATE UNIQUE NONCLUSTERED INDEX idx_Herkunftsland ON [dbo].[LiegtInLand] ($from_id, $to_id);

Die T-SQL-Zusätze AS NODE beziehungsweise AS EDGE sind beide neu und legen fest, welche Funktion eine Tabelle einnehmen soll. Für die Graph Tables wird dadurch eine Reihe zusätzlicher Spalten angelegt (Abb. 2), die für interne Zwecke benötigt werden.

Abb. 2: Node- und Edge-Tabellen im SQL Server Management Studio

Beim Befüllen (und auch beim Ändern) der Tabellen mit Inhalten muss zwischen den beiden Arten von Tabellen unterschieden werden. Node-Tabellen werden befüllt, indem die beiden internen Spalten am Anfang ignoriert werden können; um ehrlich zu sein, kann auf die graph_id…-Spalte überhaupt nicht zugegriffen werden, während die $node_id…-Spalte ein internes JSON enthält.

Bei Edge-Tabellen muss wiederum angegeben werden, von welcher Entität (aus welcher Node-Tabelle) zu welcher Entität (aus welcher anderen Node-Tabelle) die Verbindung existieren soll. Listing 2 zeigt dafür einige Beispiele.

Listing 3
-- Welche Biere wurden von Brauerei #1 gebraut?
SELECT [Biere].[Name] AS 'Bier'
FROM [dbo].[Biere], [dbo].[GebrautVon], [dbo].[Brauereien] 
WHERE MATCH (Biere-(GebrautVon)->Brauereien) AND [Brauereien].[name] = 'Brauerei #1';

-- Welche Biere kommen aus Stadt #7?
SELECT [Biere].[Name] as 'Bier',
       [Brauereien].[Name] as 'Brauerei',
       [Staedte].[Name] AS 'Stadt'
FROM [dbo].[Biere], [dbo].[GebrautVon], [dbo].[Brauereien],[dbo].[Brauereistandort],[dbo].[Staedte]
WHERE MATCH (Biere-(GebrautVon)->Brauereien-(Brauereistandort)->Staedte) AND [Staedte].[Name] = 'Stadt #7';

Die erste Version ist jedoch mit Einschränkungen versehen. Bestehende Tabellen können weder in Node- noch in Edge-Tabellen umgewandelt werden. In-Memory OLTP und Polybase sind ebenso tabu. Abfragen mittels MATCH können weder rekursiv noch polymorph sein. All dies soll in späteren Versionen oder Service-Packs nachgereicht werden.

Neue T-SQL-Funktionen

Aber es gibt noch einige weitere T-SQL-Funktionen, die kleinere Verbesserungen bringen sollen. Zunächst einmal (Trommelwirbel) gibt es endlich eine TRIM()-Funktion, die RTRIM() und LTRIM() zusammenfasst und Whitespaces am Anfang und Ende einer Zeichenkette entfernt. Des Weiteren gibt es nun eine CONCAT_WS(), die ähnlich der CONCAT()-Funktion beliebige Werte zu einer Zeichenkette zusammenfügt. Der Unterschied: CONCAT_WS() akzeptiert als Erstes einen Parameter, der als Separator dient und zwischen allen weiteren Parametern eingefügt wird. So liefert CONCAT_WS(‘;’, @@SERVERNAME, GETDATE(), SUSER_NAME()) beispielsweise SQLServer2017;Jun 29 2017  6:07PM;sa. Alle Werte werden mit einem Semikolon getrennt, ohne dass dieses Trennzeichen wiederholt werden muss.

Ebenfalls eine Erleichterung soll die TRANSLATE()-Funktion mit ihren drei Parametern bieten. Sie durchsucht den ersten Parameter nach jedem Zeichen des zweiten Parameters und setzt das Zeichen des dritten Parameters ein, das sie an derselben Position findet. Klingt kompliziert? Ist es aber nicht. Ein Beispiel: TRANSLATE(‘2*[3+4]/{7-2}’, ‘[]{}’, ‘()()’) sorgt dafür, dass alle eckigen Klammern (Stelle 1 und 2) durch runde Klammern ersetzt werden (auch an Stelle 1 und 2). Das Gleiche geschieht mit geschweiften Klammern (Stelle 3 und 4), die ebenfalls durch runde Klammern (auch an Stelle 3 und 4) ersetzt werden. Das Ergebnis ist somit 2*(3+4)/(7-2) und ersetzt damit diesen, etwas unübersichtlichen Ausdruck REPLACE(REPLACE(REPLACE(REPLACE(‘2*[3+4]/{7-2}’,'[‘,'(‘), ‘]’, ‘)’), ‘{‘, ‘(‘), ‘}’, ‘)’).

Mit SQL Server 2016 hat die Funktion STRING_SPLIT() Einzug in den Funktionsumfang gehalten. Mit ihr kann man Zeichenketten anhand eines Trennzeichens (Delimiter) auftrennen und somit in eine Liste verwandeln. In Version 2017 hat es nun auch das Pendant geschafft. Mittels STRING_AGG() können Werte aus mehreren Zeilen zu einem Wert aggregiert werden; der zweite Parameter ist dabei das gewünschte Trennzeichen. Auf Wunsch kann die Aggregation auch unabhängig einer Zeilensortierung erfolgen.

-- Alle Datenbanken ohne bestimmte Sortierung
SELECT STRING_AGG([name], '; ') AS Datenbanken FROM sys.databases;
-- Ebenfalls alle Datenbanken, diesmal sortiert nach Namen
SELECT STRING_AGG([name], '; ') WITHIN GROUP (ORDER BY [name])
 AS Datenbanken FROM sys.databases;

Abb. 3: „STRING_AGG()“ in Action

Der Access & Storage Track auf der BASTA!

Automatic Tuning

Mit SQL Server 2016 wurde der Query Store eingeführt, der verdichtete Perfomancedaten für die spätere Analyse durch einen Administrator bereitstellte. SQL Server 2017 geht einen Schritt weiter, indem laufend Ausführungsdaten gesammelt und analysiert werden. SQL Server „lernt“ also quasi, welcher Ausführungsplan für welche Abfrage die beste Performance mit den geringsten Ressourcenkosten ergeben hat. Genauer: Sie erlauben die Erkennung von sogenannten „Plan regressions“-Problemen, was bedeutet, dass Ausführungspläne (die ansonsten gut funktionieren) bei bestimmen Parametern nicht optimal sind. SQL Server kann nun für eine solche Kombination einen anderen Ausführungsplan verwenden und die Abfrage damit tunen. Somit wird also ein Vorgehen automatisiert, das zuvor nur aufwendig und fehlerbehaftet manuell durchgeführt werden konnte. Die dafür zuständige Stored Procedure heißt sp_force_plan und wird exemplarisch so aufgerufen: EXEC sp_force_plan @query_id = 4711, @plan_id = 1812;. Wohl dem, der @query_id und @plan_id bei einer großen Menge an Abfragen schnell ermitteln kann.

Die Aktivierung des neuen Automatismus geschieht auf Datenbankebene ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );. Um SQL Server bei seinen Entscheidungen dabei in die Karten zu schauen, gibt es eine neue DMV (Database Management View) mit dem Namen sys.dm_db_tuning_recommendations. Ein Teil der zurückgelieferten Daten sind im JSON-Format, sodass eine brauchbare Abfrage in Listing 5 entsteht.

Listing 5
SELECT [name], [reason], [score],
  JSON_VALUE(details, '$.implementationDetails.script') as script,
  details.* 
FROM sys.dm_db_tuning_recommendations
  CROSS APPLY OPENJSON(details, '$.planForceDetails')
    WITH (  query_id int '$.queryId',
      regressed_plan_id int '$.regressedPlanId',
      last_good_plan_id int '$.forcedPlanId') as details
WHERE JSON_VALUE(state, '$.currentValue') = 'Active';

Das gesamte Vorgehen unterscheidet sich übrigens grundlegend vom dem des schon länger vorhandenen Database Engine Tuning Advisors, der die Daten aus einem Zeitraum nutzt, um Indizes vorzuschlagen.

System-versioned/Temporal Tables

Mit SQL Server wurden System-versioned/Temporal Tables eingeführt, die eine automatische serverseitige Historisierung ermöglichen. Nachteil war bis dato, dass die historischen Daten ohne manuelles Einschreiten niemals entsorgt wurden. Das ist nun anders. Bei der Aktivierung der Historisierung kann bestimmt werden, wie alt historische Daten maximal werden dürfen. Ältere Daten werden bei Abfragen nicht mehr berücksichtigt und automatisch aus der Datenbank entfernt. Als Einheiten stehen DAYS, WEEKS, MONTHS und YEARS zur Auswahl. Wird HISTORY_RETENTION_PERIOD nicht angegeben, findet kein automatisches Entfernen satt. Außerdem muss das neue Feature auf Datenbankebene eingeschaltet werden:

— Aktivierung auf Datenbankebene
ALTER DATABASE dotnetconsultingDb SET TEMPORAL_HISTORY_RETENTION ON;

— Historische Daten auf maximal 9 Monate beschränken
ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

CLR-Integration

Bis SQL Server 2016 wurden die Sicherheitsstufen, die festlegten, über welches API eine Assembly aufgerufen durfte, mittels Code Access Security (CAS) festgelegt. Da CAS nicht mehr als Sicherheitsgrenze unterstützt wird, wurde der Konfigurationswert clr strict security, dessen Wert angibt, ob SQL Server pauschal alle Assemblies wie mit der ehemaligem Sicherheitsstufe unsafe behandeln soll. Damit müssen faktisch alle Assemblies, die auf dem SQL Server zum Einsatz kommen sollen, signiert sein. Für die Entwicklung reicht es allerdings nach wie vor, die TRUSTWORTHY-Datenbankeigenschaft zu setzen.

SQL-Server-Machine-Learning-Services-Python-Unterstützung

Unter diesem Schlagwort wurde die beliebte Sprache Python in SQL Server eingebaut. Damit lassen sich schneller größere Datenmengen verarbeiten und auswerten, schließlich müssen keine Daten von SQL Server wegbefördert werden, da die Verarbeitung intern stattfindet. Damit stehen die Leistungsfähigkeit und die Erweiterungsmöglichkeiten Data Scientists für Auswertungen zur Verfügung. Administratoren können dabei mittels Richtlinien festlegen, welche Ressourcen die Python Runtime verwenden darf.

SQL auf der BASTA!

Noch viel mehr zu den Neuerungen erfahren Sie im Data Access und Storage Track.