Mehr als nur Unterstützung von Linux

Der SQL Server 2017 – Neuerungen der Database Engine im Überblick
Kommentare

SQL Server 2017 bedeutet nicht nur Unterstützung von Linux. Vielmehr bietet die Version einige neue Features, die durchaus interessant sind. Dieser Artikel stellt diese interessanten Neuerungen der Database Engine vor und soll Interesse an ihnen wecken, auch wenn vielleicht nicht jedes Detail beschrieben wird. Die längeren Scripts findet der interessierte Leser im Blog.

Um sich die neue SQL-Server-Version anzuschauen, bietet sich der Einsatz von Docker an. Microsoft stellt ein aktuelles Image bereit, das das Datenbankmodul in neuester Version auf Ubuntu bereitstellt. Das Herunterladen und Starten des Images erfolgt dann über folgende Anweisung:

docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=P@ssw0rd99 -p 2433:1433 -v C:\Temp\Docker\Databases:/var/opt/mssql/data -d microsoft/mssql-server-linux

Der SQL Server ist damit über TCP-Port 2433 erreichbar, hat ein sicheres SA-Passwort und seine Datenbankmedien werden auf C:\Temp\Docker\Databases auf dem Host abgelegt. Damit sind die erzeugten Datenbanken auch nach dem Beenden des Containers verfügbar.

Nach dem Start und einem Verbindungsaufbau mit dem aktuellen SQL Server Management Studio 17.1 liefert die kleine Abfrage SELECT @@version; als Beweis die neue Versionsnummer und das Hostbetriebssystem im Docker-Container (Abb. 1).

Abb. 1: Eine einfache Abfrage offenbart Ubuntu als OS im Docker-Container

Das SQL Server Management Studio muss übrigens seit SQL Server 2016 durch ein eigenständiges Set-up installiert und vorher selbstredend erst einmal heruntergeladen werden. Die Verbindung wird dann, wie in Abbildung 2 zu sehen, auf den freigegebenen TCP-Port mit SQL-Server-Authentifizierung hergestellt.

Abb. 2: Verbindungsdialog für Verbindung mit SQL Server in Docker

Damit steht der Erkundung des SQL-Server-2017-Datenbankmoduls nichts mehr im Weg.

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. 3), dann sieht das T-SQL wie in Listing 1 aus.

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

/*------------------
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. 4), die für interne Zwecke benötigt werden.

Abb. 4: 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.

-- Bier 1-3 werden in Brauerei #1 gebraut
INSERT [dbo].[GebrautVon]
SELECT (SELECT $node_id FROM [dbo].[Biere] WHERE ID = 1),
       (SELECT $node_id FROM [dbo].[Brauereien] WHERE ID = 1);
INSERT [dbo].[GebrautVon]
SELECT (SELECT $node_id FROM [dbo].[Biere] WHERE ID = 2),
       (SELECT $node_id FROM [dbo].[Brauereien] WHERE ID = 1);
INSERT [dbo].[GebrautVon]
SELECT (SELECT $node_id FROM [dbo].[Biere] WHERE ID = 3),
       (SELECT $node_id FROM [dbo].[Brauereien] WHERE ID = 1);

-- Brauerei #1 liegt in Stadt #7
INSERT [dbo].[Brauereistandort]
SELECT (SELECT $node_id FROM [dbo].[Brauereien] WHERE ID = 1),
       (SELECT $node_id FROM [dbo].[Staedte] WHERE ID = 7);

-- Und Stadt #7 wiederrum in Land #5
INSERT [dbo].[LiegtInLand]
SELECT (SELECT $node_id FROM [dbo].[Staedte] WHERE ID = 7),
       (SELECT $node_id FROM [dbo].[Laender] WHERE ID = 5)

Für Abfragen wurde der neue MATCH-Operator eingeführt. Dessen Parameter beschreibt mit einfacher Syntax, welche Beziehungen vorhanden sein müssen, damit ein Treffer erzielt werden kann. Dabei können Beziehungen auch mehrfach verkettet werden. Listing 3 zeigt dafür zwei Beispiele.

-- 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.

Und das war es auch schon mit dieser kleinen Übersicht über die Neuerungen von SQL Server 2017. Viel Spaß beim Ausprobieren!

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;

Das Ergebnis ist in Abbildung 5 zu sehen.

Abb. 5: „STRING_AGG()“ in Action

In-Memory OLTP

Auch der Bereich In-Memory OLTP hat Neues zu verzeichnen. Mit SQL Server 2014 eingeführt, hat Microsoft die Möglichkeiten von in Memory optimized Tables und Natively compiled Modules (also Prozeduren und Funktionen) immer weiter ausgebaut.

Dazu gehören nun seit SQL Server 2017 berechnete Spalten und die Unterstützung der altbekannten sp_spaceusedProzedur, die die Größe (Speicher in MB) der gesamten Datenbank oder auf Wunsch einzelner Tabellen (mit Anzahl Zeilen) ausgibt. Interessant ist auch die Möglichkeit, nun berechnete Spalten in Memory optimized Tables verwenden zu dürfen. Diese können aus Performancegründen auch persistiert und indexiert werden. Zusätzlich können nun die in SQL Server 2016 eingebauten JSON-Funktionen für Berechnungen oder Einschränkungen verwendet werden.

Listing 4 zeigt ein Beispiel für den Einsatz von berechneten Spalten und JSON-Funktionen in Memory optimized Tables.

CREATE TABLE xtp.Product(
  Idint PRIMARY KEY NONCLUSTERED,
  Name nvarchar(400) NOT NULL,
  Price float,
  Data nvarchar(4000)
    CONSTRAINT [Data must be JSON] CHECK (ISJSON(Data)=1),
  YearMadeIn AS CAST(JSON_VALUE(Data, '$. YearMadeIn') as NVARCHAR(50)) PERSISTED,
  TotalCost AS CAST(JSON_VALUE(Data, '$. TotalCost') as FLOAT)
) WITH (MEMORY_OPTIMIZED=ON);

Die gesamten JSON-Funktionen stehen nicht nur für Tabellen, sondern auch in Natively compiled Modules zur Verfügung. Weitere neue Funktionalitäten, die nun eingesetzt werden können, sind TOP (N) WITH TIES und CASE. Um den Namen von Prozeduren und Funktionen zu ändern, kann jetzt die Systemprozedur sp_rename zum Einsatz kommen. Der Bereich In-Memory OLTP wird also auch mit SQL Server 2017 nach und nach erweitert und ausgebaut.

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.

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:

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.

-- Erweiterte Optionen anzeigen
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- clr strict security auslesen. Das Ergebnis sollte 1 (aktiv) sein
EXEC sp_configure 'cl#r strict security';

-- Entgegen Microsofts Ratschlag ausschalten
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

Listing 6 zeigt, wie clr strict security ausgelesen und geändert werden kann. Eine Deaktivierung ist also möglich, und SQL Server 2017 verhält sich dann wie sein Vorgänger. Microsoft rät von diesem Vorgehen jedoch ab.

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.

Schlussstrich

Und das war es auch schon mit dieser kleinen Übersicht über die Neuerungen von SQL Server 2017. Viel Spaß beim Ausprobieren!

Windows Developer

Windows DeveloperDieser Artikel ist im Windows Developer erschienen. Windows Developer informiert umfassend und herstellerneutral über neue Trends und Möglichkeiten der Software- und Systementwicklung rund um Microsoft-Technologien.

Natürlich können Sie den Windows Developer über den entwickler.kiosk auch digital im Browser oder auf Ihren Android- und iOS-Devices lesen. In unserem Shop ist der Windows Developer ferner im Abonnement oder als Einzelheft erhältlich.

Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

X
- Gib Deinen Standort ein -
- or -