TypeScript 2.0: Neuheiten und erweiterte Features

TypeScript ist eine typsichere objektorientierte Programmiersprache aus dem Hause Microsoft, welche im Jahr 2012 erstmals veröffentlich wurde und seitdem eine permanente Weiterentwicklung erfährt. Ziel dieses Flurfunkartikels ist es, einige neue Features aus TypeScript 2.0 vorzustellen, dessen Beta-Version im Juni dieses Jahres für Visual Studio 2015 zur Verfügung gestellt wurde und am 22. September als neue fertige Release-Version erscheinen ist.

Non-nullable und non-undefined Types

In JavaScript haben die speziellen Datentypen  bzw. Werte undefined und null eine essentielle Bedeutung. Der Typ ‘undefined’ ist in JavaScript sowohl ein eigener Datentyp als auch der (Default-)Wert für Variablen, denen aus irgendwelchen Gründen (noch) kein Wert zugewiesen wurde. Bei dem Keyword ‘null’ handelt es sich dagegen um keinen eigenständigen Datentypen sondern nur um eine mögliche Wertausprägung vom Typ object.

Vor TypeScript-Version 2.0 konnte bislang nicht ausgeschlossen werden, dass eine Variable oder ein Funktionsparameter den Wert ‘undefined’ oder ‘null’ besitzen. Zuweisungen wie im nachfolgenden Code-Snippet waren daher durchaus gültig:

   1: // vor TypeScript 2.0

   2: let varNumber: number;

   3: varNumber = undefined;

   4: varNumber = null;

   5: varNumber = 73;

Die Folge dieser schwachen Typisierung war, dass sehr viele Prüfungen auf ‘null’ bzw. ‘undefined’ durchgeführt werden mussten, ehe die eigentliche Implementierungslogik umgesetzt werden konnte. Das in TypeScript 2.0 neu eingeführte strictNullChecks-Flag, welches beim Kompilierungsvorgang mit angegeben werden kann, wirkt diesem Problem nun entgegen und sorgt dafür, dass eine strengere Typisierung gewährleistet wird. Sobald versucht wird einen sogenannten non-nullable bzw. non-undefined Typ mit Wert ‘null’ oder ‘undefined’ zu versehen, liefert der Compiler eine entsprechende Fehlermeldung. Dieses Verhalten wird durch folgende Abbildung grafisch belegt:

TypeScript strictNullChecks

Unnötige Checks auf die meistens unerwünschten Werte ‘null’ oder ‘undefined’ können somit entfallen. Der Code wird einfacher, übersichtlicher und somit vermutlich auch wartbarer. Die Codezeilen des folgenden Code-Snippets zeigen – die Verwendung des stricktNullChecks-Flags vorausgesetzt – noch einige weitere Beispiele gültiger und ungültiger Zuweisungsoperationen im Zusammenhang mit numerischen Werten, ‘null’ und ‘undefined’:

   1: // Compiled with --numberictNullChecks

   2: let numberA: number;

   3: let numberB: number | undefined;

   4: let numberC: number | null | undefined;

   5: numberA = 73;  // valid

   6: numberB = 73;  // valid

   7: numberC = 73;  // valid

   8: numberA = undefined;  // invalid

   9: numberB = undefined;  // valid

  10: numberC = undefined;  // valid

  11: numberA = null;  // invalid

  12: numberB = null;  // invalid

  13: numberC = null;  // valid

  14: numberA = numberB;  // invalid

  15: numberA = numberC;  // invalid

  16: numberB = numberA;  // valid

  17: numberB = numberC;  // invalid

  18: numberC = numberA;  // valid

  19: numberC = numberB;  // valid

.

Der Typ ‘never’ in TypeScript 2.0

Viele Programmiersprachen befolgen Prinzipien der Typentheorie, einer mathematischen Disziplin, welche besagt, dass sämtliche Programmieranweisungen, Terme oder Funktionen auf bestimmte Typen beschränkt sind. Daher besitzen viele Programmiersprachen in ihren zulässigen Menge an Datentypen einen sogenannten Bottom Type, welcher keinen konkreten Wert annehmen kann.

Nachdem durch die Einführung des strictNullChecks-Flags aus dem vorherigen Abschnitt des Artikels null und undefinied zu konkreten Datentypen von TypeScript “befördert” wurden, bedarf es eines neuen Datentyps, welcher die Rolle des Falsums – ein gelegentlich verwendetes Synonym für Bottom Type – übernimmt. Hierfür wurde in TypeScript 2.0 der neue (primitive) Datentyp never eingeführt, welcher keine konkreten Werteausprägungen besitzt und folgenden Eigenschaften vorweist:

  • der Typ ‘never’ ist ein Sub-Typ und kann jedem anderen Typen zugewiesen werden (z.B. let n: number = never)
  • kein anderer Sub-Typ kann ‘never’ zugewiesen werden, außer ‘never’ selbst
  • besitzt eine Funktion einen expliziten never-Rückgabewert, so müssen entweder alle return-Statements vom Typ ‘never’ sein oder das Ende der Funktion darf nicht erreichbar sein

Der Datentyp ‘never’ besitzt vermutlich vorrangig theoretische Existenzberechtigungen, um das Typsystem von TypeScript konsistent zu halten. Er sollte auch nicht mit dem void-Operator verwechselt werden, welcher bei Funktionen zwar nichts(!) zurückliefert dafür aber immer die Endpunkte einer Funktion erreicht.

Use-Cases, bei denen der Datentyp ‘never’ tatsächlich auftreten kann, existieren auch und sind beispielsweise folgend Fälle:

  • eine Funktion terminiert niemals (z.B. Endlosschleife)
  • eine Funktion besitzt unerreichbare Code-Abschnitte
  • eine Funktion erzeugt ausschließlich Fehler bzw. Error-Objekte

Daher stellen die folgenden Code-Beispiele gültige Szenarien für den Einsatz des never-Datentyps dar:

   1: function infinityLoop() : never {

   2:     while(true) {

   3:     }

   4:     // unreachable code

   5: }

   6:

   7: function BoolToYesOrNo(value: boolean): never {

   8:     if (value) {

   9:         return "Yes";

  10:     }

  11:     else if (value == false) {

  12:         return "No"

  13:     }

  14:     // unreachable code

  15: }

  16:

  17: function alwaysFails(message: string) {

  18:     throw new Error(message); // implicit use of never type

  19: }

..

Private und protected Konstruktoren

In älteren TypeScript-Versionen ist es nicht möglich, den Konstruktor einer Klasse mit den Schlüsselwort private oder protected zu versehen. Beim Versuch wurde, wie die folgende Abbildung zeigt, vom Compiler immer eine entsprechende Fehlermeldung zurückgegeben:

Compilerfehler für private Konstrzuktor (vor TypeScript 2.0)

Mit TypeScript 2.0 besteht nun die Möglichkeit Konstruktoren mit Private- oder Protected-Modifier hinsichtlich ihrer Sichtbarkeit zu modifizieren. Besitzt eine Klasse einen Private-Konstruktor, so können außerhalb der Klasse weder Instanzen von dieser angelegt werden noch kann der Konstruktor in ableitenden Klassen verwendet werden. Insbesondere bei Realisierung des Singleton-Patterns ist dies ein gängiges Konzept. Klassen mit einem Protected-Konstruktor können über diesen ebenfalls nicht direkt instanziiert werden, allerdings ist es möglich auf diesen in ableitenden Klassen darauf zuzugreifen.

Readonly-Keyword

Als nächstes neues Feature hat das Keyword readonly Einzug in TypeScript 2.0 gehalten. Mit diesem wird sicher gestellt, dass Felder nur einmalig mit einem Wert versehen werden. Weitere Änderungen an readonly-Feldern, sei es durch Funktionsaufrufe oder Property-Zugriffe, sind nicht möglich.

TypeScripts neuste Version orientiert sich hierbei an C#, Microsofts Zugpferd was Programmiersprachen betrifft. Die Initialisierung kann, wie im folgenden Code-Ausschnitt dargelegt, entweder direkt nach der Deklaration des Feldes oder innerhalb des Konstruktor erfolgen:

   1: class GeometricFigure {

   2:

   3:     private readonly _createdAt: Date = new Date(); // declaration and initialization

   4:

   5:     private readonly _name: string; // declaration

   6:

   7:     constructor(name: string) {

   8:         this._name = name; // initialization in constructor

   9:     }

  10:

  11:     get name(): string {

  12:         return this._name;

  13:     }

  14:

  15:     get createdAt() {

  16:         return this._createdAt;

  17:     }

  18:

  19:     get name(): string {

  20:         return this._name;

  21:     }

  22: }

..

Abstract-Properties

In einem früheren SDX-Flurfunkbeitrag, welcher den Titel „abstract“ in TypeScript trägt, wurde das abstract-Keyword in der TypeScript-Version 1.8 bereits ausführlich analysiert. Damals beschränkte sich dessen Verwendung noch auf Klassen und Methoden.

Mit Version 2.0 entfällt diese Restriktion nun. Neben Klassen und Methoden können fortan auch Propertys einer Klasse mit dem abstract-Keyword ausgestattet und ähnlich wie in C# realisiert werden. Dabei muss jede konkrete Unterklasse das abstrakte Property der abstrakten Oberklasse mit einem Wert initialisieren. Ein Code-Snippet, welches sich an den Beispielen des vorangegangen Artikels angelehnt ist, sieht wie folgt aus:

   1: abstract class GeometricFigure {

   2:

   3:     private readonly _createdAt: Date = new Date();

   4:

   5:     private readonly _name: string;

   6:

   7:     constructor(name: string) {

   8:         this._name = name;

   9:     }

  10:

  11:     get createdAt() {

  12:         return this._createdAt;

  13:     }

  14:

  15:     get name(): string {

  16:         return this._name;

  17:     }

  18:

  19:     abstract get area(): number; // abstract getter 

  20:

  21:     abstract set area(v: number); // abstract setter

  22: }

  23:

  24: class Circle extends GeometricFigure {

  25:

  26:     private _radius: number;

  27:

  28:     constructor(name: string, radius: number) {

  29:         super(name);

  30:         this._radius = Math.max(radius, 0);

  31:     }

  32:

  33:     area = Math.pow(this._radius, 2) * Math.PI; // initialization for circle area

  34: }

  35:

  36: class Rectangle extends GeometricFigure {

  37:

  38:     private _height: number;

  39:     private _width: number;

  40:

  41:     constructor(name: string, width: number, height: number) {

  42:         super(name);

  43:         this._height = Math.max(height, 0);

  44:         this._width = Math.max(width, 0);

  45:     }

  46:

  47:     area = this._width * this._height; // initialization for rectangle area

  48: } 

 .

Fazit

Alle in diesem Artikel vorgestellten TypeScript 2.0-Features stellen interessante und vermutlich auch sehr nützliche Erweiterungen dar. Wichtige objektorientierte Konzepte wie Datenkapselung oder das Geheimnisprinzip können nun noch intensiver in Web-Applikationen umgesetzt werden. Des Weiteren dürfte die Einführung des strictNullChecks-Flags besonders bei Sympathistanten typsicherer Programmiersprachen großen Anklang finden.

Der Einsatz der OOP-Sprache TypeScript stößt in Entwicklerkreise allgemein oftmals auf geteiltes Echo, das belegt beispielsweise folgende Forums-Diskussion auf der heise-Webseite. Während es für die einen nichts weiter als “Syntactic Sugar” für JavaScript ist, sehen andere – insbesondere Anhänger der objektorientierten Programmierung – in TypeScript eine Erleichterung hinsichtlich Strukturierung großer und komplexer Web-(Enterprise-)Applikationen.

Egal welche Einstellung nun überwiegen mag – die Wahrheit liegt wie so oft vermutlich irgendwo in der Mitte – eine Tatsache lässt sich nicht leugnen: TypeScript lebt und seine Anhängerschaft wächst! Dazu genügt allein ein Blick auf die Roadmap, welche sich schon mit einer Version 2.1 befasst.

Quellen

R im MS SQL Server 2016: Installation und Grundlagen

R ist eine freie Programmiersprache, welche sich heute vor allem an Hochschulen in vielen mathematischen Disziplinen – besonders in der Statistik und Stochastik – als feste Größe etabliert hat. Aber nicht nur in der Mathematik, auch im Bereich Business Intelligence (kurz: BI) gewinnt dieses Analyse-Tool immer mehr an Bedeutung.

Auch Microsoft ist diese Entwicklung nicht verborgen geblieben und hat reagiert. Der Softwarehersteller aus Redmond ist quasi auf den R-Zug aufgesprungen und hat dafür gesorgt, dass die skriptbasierte Statistiksprache innerhalb der neuesten Version seines intern entwickelten Datenbankmanagementsystems, dem MS SQL-Server 2016, genutzt werden kann.

In meinem letzten Flurfunkartikel mit Titel Advanced T-SQL: IF EXISTS mit MS SQL Server 2016 wurde ein neues SQL-Server-Feature in der Abfragesprache T-SQL vorgestellt. Dieser aktuelle Beitrag soll insbesondere die R-Services, welche ebenfalls als Neuheit in den SQL-Server Einzug gehalten haben und die Grundlage für die Interaktion mit der R bilden, näher analysieren. Dabei geht es zum einen um die Installation der notwendigsten Komponenten sowie deren Konfiguration und zum anderen um das Ausführen eines einfachen R-Statements aus dem SQL-Server heraus.

Allgemeine Installation von R

Bei R handelt es sich um ein GNU-Projekt, dessen Sourcen im Internet für unterschiedliche Betriebssysteme und Plattformen heruntergeladen werden können. Nach der erfolgreichen Installation können R-Befehle entweder direkt über eine Konsole oder eine der vielen im Internet verfügbaren IDEs abgesetzt werden. Die bekannteste Entwicklungsumgebung dürfte vermutlich R-Studio sein, dessen Installationspaket gleich alle notwendigen Komponenten in einem Paket mitliefert.

Der folgende Screenshot zeigt ein erfolgreich installiertes und gestartetes R-Studio, welches sofort verwendet werden kann und für diesen Artikel weiter genutzt werden soll:

R-Studio

Zuweisungen und Grundoperationen in R

Sicherlich wäre es zu umfangfangreich R in diesem Artikel bis in das kleinste Detail zu beleuchten. Aus diesem Grund soll sich der folgende Abschnitt nur auf ein paar rudimentäre Grundlagen beschränken, die notwendig sind um R-Befehle innerhalb des SQL-Servers 2016 ausführen zu können.

Wie bereits in der Einleitung erwähnt, handelt es sich bei R um eine Skript- bzw. Interpretersprache, deren Anweisungen zur Laufzeit ausgewertet und ausgeführt werden. Eine der wichtigsten Grundbefehle stellen Zuweisungen dar, welche über syntaktisch entweder über den ‘<-‘- oder den “=”-Operator erfolgen können.

Die folgenden Code-Zeilen stellen exemplarisch dar, wie eine das Zuweisen eines konstanten Wertes zu einer Variablen erfolgt:

   1: wert <- 73;

 

Es können aber nicht nur einfache Werte in Variablen gespeichert werden, auch Funktionen können, ähnlich wie bei JavaScript oder C#-Delegates, in Variablen abgelegt werden. Das folgende Snippet demonstriert dies an Hand einer einfachen Additions-Funktion, welche die Summe zweier Parameter zurück liefert:

   1: summe <- function(v1, v2) { v1 + v2 };

 

Der Aufruf des zugewiesenen Wertes bzw. der Funktion sieht dann wie folgt aus:

   1: wert;

 

   2: summe(17, 4);

und liefert die erwarteten Ergebnisse 73 bzw. 21 (=17 + 4).

Die folgende Abbildung stellt die eben erläuterten Schritte in der der Reihenfolge Initialisierung (rot), Aufruf (grün) und Ergebnisausgabe (blau) von Wert- und Funktionsvariablen in R-Studio bildlich und zusammenfassend dar:

R-Studio Operationen

Mehr R-(Grund-)Kenntnisse werden an dieser Stelle erst einmal nicht benötigt. Für weitere Information sei an dieser Stelle auf Fachliteratur oder auf diverse Tutorials im Internet verwiesen.

 

R Services im SQL-Server einrichten

Um R-Skripte innerhalb des SQL-Servers 2016 nutzen zu können, müssen die R-Services installiert werden. Dies kann bereits bei der initialen Konfiguration des Datenbank-Servers erfolgen, welche hier detaillierter beschrieben und unter anderem durch folgenden Screenshot veranschaulicht wird:

R-Services-Install

Nachdem der SQL-Server zusammen mit den R-Services erfolgreich installiert wurde, gilt es noch die externe Skriptausführung zu aktivieren. Dies kann – die entsprechenden Berechtigungen vorausgesetzt – durch das Aufrufen der sp_configure-Prozedur mit folgenden Parametern geschehen:

   1: sp_configure 'external scripts enabled', 1;

 

   2: RECONFIGURE

Im Anschluss muss die SQL-Server-Instanz auf jeden Fall neu gestartet werden. Des Weiteren sollte nach dem Neustart verifiziert werden, ob die externe Skriptausführung tatsächlich aktiviert ist. Dies kann durch folgende Kontrollabfrage geprüft werden:

   1: SELECT * FROM sys.configurations

 

   2: WHERE name = 'external scripts enabled'

 

Für ein positives Prüfergebnis wird dann folgendes Abfrageresultat zurückgeliefert:

Externe Skriptausführung aktiv

Damit wäre die Installation der R-Services sowie die Aktivierung der externen Skriptausführung auch schon abgeschlossen.

Skripte mit T-SQL ausführen

Die so aktivierten R-Services können dann im via T-SQL mit der sp_execute_external_script-Prozedur aufgerufen werden. Diese Prozedur ist neuer Bestandteil des SQL-Servers 2016 und ermöglicht generell die Ausführung von Skript-Komponenten.

Der Aufruf der Prozedur kann beispielsweise nach folgendem Syntax-Pattern erfolgen:

   1: EXEC sp_execute_external_script

 

   2: @language = N'<Language>',

 

   3: @script = N'<Script-Code>',

 

   4: @input_data_1 = N'<InputData>'

 

   5: [WITH [<ExecuteOption>]];

Die erste Zeile mit der EXEC-Anweisung sorgt für die Ausführung der sp_execute_external_script-Prozedur. Der @language-Parameter in Zeile 2 legt fest, in welcher (Programmier-)Sprache das Skript implementiert ist. Im zweiten Prozedurargument, dem @script-Parameter (Zeile 3), wird anschließend der auszuführende Skript-Code mitgegeben. Dieser ist natürlich abhängig von der mit dem @language-Parameter festgelegten Sprache. Über den dritten Parameter (Zeile 4), welcher den Namen @input_data_1 trägt, können zusätzlich Daten für die Ausführung des Script-Codes mitgegeben werden. Auch diese hängen wieder von der konkret eingesetzten Skriptsprache ab. Den Abschluss des oben aufgeführten Code-Snippet stellt in Zeile 5 der optionale WITH-Part dar, welcher allgemeine Information bzgl. der Ausführung beinhaltet.

Es gibt noch weitere optionale Parameter, welche beim Aufruf der sp_execute_external_script-Prozedur mitgegeben werden können, allerdings spielen diese im Kontext des aktuellen Artikels keine Rolle. Für nähere Informationen sei auf die Dokumentation der MSDN (=Microsoft Developer Network), welche die neue SQL-Server-Prozedur detailliert beschreibt. Dort steht auch der Hinweis, dass nur registrierte Skriptsprachen ausgeführt werden können und die Ausführung der sp_execute_external_script-Prozedur über Ressource Pooling kontrolliert werden kann. Somit können einige sicherheitsrelevante Risiken, wie beispielsweise die heute immer noch häufig auftretenden Code-Injections, vermindert werden.

R-Skript im SQL-Server ausführen

In den bisherigen Abschnitten wurden die Grundlagen dargelegt. Im Folgenden soll nun tatsächlich R-Code innerhalb des SQL-Servers ausgeführt werden. R stellt dem SQL-Server zwei Variablen zur Verfügung, welche die Namen InputDataSet und OutputDataSet tragen. Mit der InputDataSet-Variablen wird Zugriff auf den Inhalt des @input_data_1-Parameters gewährt. Die OutputDataSet-Variable dient zur Ausgabe von Ergebnissen, welche durch das R-Skript ermittelt werden. Es soll noch erwähnt werden, dass das Ergebnis eines Skripts ein data.frame-Objekt sein muss. Dieser R-spezifische Datentyp repräsentiert eine Tabelle mit Spalten und Zeilen, wobei jede Spalte einen anderen Datentyp besitzen kann.

Im folgenden Beispiel soll eine R-Variable namens wert mit konstantem Wert 73 ausgelesen werden. Die Syntax hierfür sieht wie folgt aus:

   1: EXEC sp_execute_external_script

 

   2: @language = N'R',

 

   3: @script = N' wert <- 73;

 

   4:              OutputDataSet <- data.frame(wert);',

 

   5: @input_data_1 = N''

 

   6: WITH RESULT SETS (([Value] INT NOT NULL));

Der @language-Parameter wird mit “R” initialisiert (Zeile 2), somit wird dem SQL-Server signalisiert, dass es sich beim auszuführenden Skript-Code um R-Befehle handelt. Im @script-Parameter selbst wird dann der eigentliche Code (Zeile 3 und Zeile 4) übergeben. Zunächst erfolgt darin die einfache Zuweisung des numerischen Wertes 73 an die Variable wert (Zeile 3). Anschließend wird der Inhalt der wert-Variablen an die OutputDataSet -Variable weitergereicht (Zeile 4). Der Parameter @input_data_1 enthält nur eine leere Zeichenkette (Zeile 5), da in diesem Beispiel keine Daten an den das Skript übergeben werden. Mit dem “WITH RESULT SETS (([Value] INT NOT NULL));”-Part in Zeile 6 wird abschließend noch ein Alias erzeugt, um das Endergebnis mit dem Spaltenkopf “Value” zu versehen.

Das Resultat dieses T-SQL-Befehls liefert im SSMS (=SQL-Server Management Studio) folgendes Ergebnis:

Berechnungsergebnis von R

Fazit

Dieser erste Artikel  zeigt, welche Voraussetzungen anfangs getroffen werden müssen, damit die Statistiksprache R innerhalb der neusten Version des MS SQL-Server genutzt werden kann. Der gesamte Installationsvorgang des SQL-Servers kann sicherlich etwas länger dauern, was bei großen Microsoft-Produkten aber sicher nicht unüblich ist. Nachdem die Konfiguration abgeschlossen ist, kann auch schon mit der Ausführen von R-Skripten im MS-SQL-Servers begonnen werden.

Sicherlich mag es für den ein oder anderen Entwickler zunächst etwas befremdlich wirken, wenn R-Befehle als einfache Strings-Parameter in den SQL-Server eingegeben werden. Allerdings sollte beachtet werden, dass die R-Integration in den Microsoft SQL-Server vermutlich erst am Anfang steht. Allein die Tatsache, dass Microsoft mit R Open eine kostenlose Client-Applikation anbietet, deutet darauf hin, dass R weiterhin Beachtung durch Microsoft erfahren wird. Es darf deshalb stark davon ausgegangen werden, dass das Zusammenspiel zwischen R und dem SQL-Server noch länger weiter geht oder gar intensiviert wird. In meinem nächsten Artikel zu dieser Reihe werden auch komplexere Bespiele vorgestellt.

 

Quellen

Advanced T-SQL: IF EXISTS mit MS SQL Server 2016

Am 01.06.2016 war es endlich soweit, der MS SQL Server 2016 wurde endgültig der Allgemeinheit zur Verfügung gestellt. Version 13 des (vorwiegend) relationalen Datenbanksystems aus dem Hause Microsoft zeichnet sich durch zahlreiche neue Updates und Feature aus, welche dem Datenblatt zum Produkt entnommen werden können. Auch in die Abfrage-Sprache T-SQL (=Transact SQL) haben Neuerungen Einzug gehalten.

Vor einiger Zeit gab es eine kleine Artikelserie von mir, welche sich dem Thema “Advanced T-SQL” gewidmet hat. Bislang sind hierzu folgende Beiträge erschienen:

Im Rahmen des folgenden Flurfunkbeitrags soll nun das mit dem SQL-Server 2016 neu eingeführte “IF EXISTS”-Statement in Kombination mit DDL-Befehlen (=Data Definition Language) näher analysiert und vorgestellt werden.

Bedingte DDL-Befehle mit T-SQL bisher

Gegeben sei folgende Anforderung: Wenn ein Datenbankobjekt (z.B. eine Tabelle) existiert, so soll es gelöscht werden.

Einigen Datenbankentwickler dürfte dieses Beispielszenario sicherlich aus dem Arbeitsalltag bekannt sein. Wenn es nun darum geht dieses mit T-SQL-Mitteln umzusetzen, so wird häufig ein Pattern verwendet, welches sich in etwa an folgendem Code-Snippet orientiert:

   1: IF OBJECT_ID('[ObjectName]') IS NOT NULL

   2: BEGIN

   3:     DROP [OBJECT_TYPE] [ObjectName]

   4: END

Der Nachteil bei diesem Muster ist, dass zwei unabhängige T-SQL-Befehle – anfänglicher Existenz-Check mit anschließender DROP-Anweisung – notwendig sind, um den gewünschten Zielzustand auf der Datenbank zu erhalten. Insbesondere bei mehrfacher paralleler Ausführung kann es hierbei zu Fehlern kommen.

Das DIE-Statement (=DROP IF EXISTS) mit SQL-Server 2016

Im SQL-Server 2016 ist es nun möglich, das eben geschilderte Problem durch eine einzelne atomare T-SQL-Anweisung zu umgehen. Dieses orientiert sich bzgl. der T-SQL-Syntax an folgendem Code-Ausschnitt:

   1: DROP [OBJECT_TYPE] [IF EXISTS] [OBJECT_NAME]

 

Für eine konkrete Datenbanktabelle mit Namen ‘TestTable’ sieht das entsprechende Statement beispielsweise wie folgt aus:

   1: DROP TABLE IF EXISTS TestTable;

 

Sollte die Tabelle nicht existieren, so schlägt das DIE-Statement nicht fehl und mögliche folgende Befehle können einfach weiter ausgeführt werden. Das bringt wesentliche Vorteile und beugt Fehlern vor.

Das DIE-Pattern beschränkt sich nicht nur auf Tabellen, sondern kann auch auf andere Datenbankobjekte wie Views, Funktionen, Trigger, … angewendet werden. Eine komplette Liste aller möglichen Objekt-Typen wird im Internet, beispielsweise unter folgendem MSDN-Blog, aufgeführt.

Des Weiteren kann das DIE-Pattern auch mit ALTER-Statements kombiniert werden. Das bedingte Löschen einer Tabellenspalte kann mit folgendem T-SQL-Snippet erreicht werden:

   1: ALTER TABLE [TableName] DROP COLUMN IF EXISTS [ColumnName]

Entfernen eines möglicherweise existierenden Constraints erfolgt mittels dieses DROP-Statements:

   1: ALTER TABLE [TableName] DROP CONSTRAINT IF EXISTS [ConstraintName]

Fazit:

In anderen Datenbank-Technologien ist es bereits länger möglich, bedingte DDL-Befehle mit einfacher Syntax abzusetzen. In Oracle kann dies für einige Datenbankobjekte mit Hilfe des “CREATE OR REPLACE”-Patterns, wie beispielsweise hier gezeigt, umgesetzt werden.

Mit der in diesem Artikel vorgestellten “IF EXISTS“-Syntax hat nun endlich auch die Microsoft-Datenbank-Technologie ihr eigenes Muster für einige optionale Datenstrukturbeschreibungen. Bei Datenbankentwicklern mit T-SQL dürfte es sich sicher bald einer großen Beliebtheit erfreuen. Es bleibt die (vermutlich unbeantwortete) Frage an Microsoft: Wieso hat es so lange gedauert 🙂 ?

Quellen:

  1. SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause (letzter Zugriff: 2016-06-17)
  2. DROP IF EXISTS – new thing in SQL Server 2016 (letzter Zugriff: 2016-06-17)
  3. Microsoft macht SQL Server 2016 am 1. Juni allgemein verfügbar (letzter Zugriff: 2016-06-17)

"abstract" in TypeScript

In objektorientierten Programmiersprachen (kurz: OOP-Sprachen) wie C# oder Java hat das Schlüsselwort „abstract“ mehrere Aufgaben. Zum einen suggeriert es dem Entwickler, dass bestimmte Klassen oder Klassen-Member nicht vollständig ausprogrammiert sind, auf der anderen Seite stellt es eine verbindliche Implementierungsrichtlinie für ableitende Klassen dar. Mit Version 1.6, welche Ende 2015 offiziell veröffentlicht wurde, hat das abstract-Keyword auch in die Programmiersprache TypeScript Einzug gehalten. Im Rahmen dieses Flurfunkartikels soll dessen Funktionsweise und Nutzen etwas näher erläutert werden.

Abstrakte TypeScript-Klassen und Konstruktoren-Regeln

Abstrakte Klassen vereinen die Vorteile von konkret implementierten Klassen und reinen Schnittstellen-Klassen. Letztere stellen ausschließlich Signaturen (z.B. von Methoden) zur Verfügung und werden in den Programmiersprachen C# und Java sowie in TypeScript einher mit dem Schlüsselwort interface versehen.  Des Weiteren können Variablen abstrakter Klassen zwar deklariert aber niemals direkt instanziiert werden. Nur durch eine konkrete Spezialisierung innerhalb der Vererbungshierarchie können Instanzen abstrakter Klassen erzeugt werden.

TypeScript orientiert sich bezüglich dieses Verhaltens an den etablierten Programmiersprachen wie C# oder Java.  Die Syntax für eine einfache abstrakte GeometricFigure-Klasse schaut in TypeScript wie folgt aus:

   1: abstract class GeometricFigure {

   2: }

Des Weiteren können abstrakte Klassen mit parametrisierten Konstruktoren ausgestattet werden. Das folgende Code-Snippet zeigt dies exemplarisch:

   1: abstract class GeometricFigure {

   2:     public name: string;

   3:     constructor(name: string) {

   4:         this.name = name;

   5:     }

   6: }

Wie bereits erwähnt, können in C# und Java keine direkten Instanzen von abstrakten Klassen angelegt werden. Auch in TypeScript ist das nicht möglich. Folgender Instanziierungsversuch schlägt daher mit einer entsprechenden Meldung durch den Compiler fehl:

Grafik1a_M.Pohl

Es bedarf also einer konkreten bzw. nicht abstrakten Klasse, welche von der abstrakten Basisklasse ableitet, um Instanzen anzulegen. Das folgende Code-Beispiel repräsentiert eine solche real implementierte konkrete Klasse:

   1: class Circle extends GeometricFigure {

   2:     public radius: number;

   3: }

Nun kann auch eine GeometricFigure-Instanz ohne Kompilierungsfehler angelegt werden.

   1: var geometricFigure: GeometricFigure = new Circle("Circle");

 

Interessant an diesem Beispiel ist die Tatsache, dass ein bestehender Konstruktor in der Basisklasse nicht in der ableitenden Klasse mit ausprogrammiert werden muss. Hierin unterscheidet sich TypeScript von OO-Sprachen wie C# oder Java. Der TypeScript-Compiler erkennt automatisch die Vererbungshierarchie und erzeugt anschließend die konkreten  Objekt-Instanzen.

Wenn der Konstruktor der Basisklasse aufgerufen werden soll, so muss dies mittels des Schlüsselworts super und als erste Code-Anweisung innerhalb des Konstruktor-Codes erfolgen. Fehlt dieser Aufruf, auch wenn die Basisklasse keinen Konstruktor besitzt, kommt wieder eine Compiler-Meldung mit entsprechendem Hinweis:

Grafik2_M.Pohl

Diese Regel dürfte für C#- oder Java-Entwickler ebenfalls vertraut vorkommen. Besitzt beispielswiese eine ableitende C#-Klasse einen weiteren Konstruktor, dessen Signatur sich vom Konstruktor der abstrakten Basisklasse unterscheidet, so muss letzterer mit Hilfe des base-Schlüsselworts aufgerufen werden. Eine korrekt implementierte konkrete Circle-Klasse, mit einem anschließender Instanz-Erzeugung stellt das folgende Code-Snippet dar:

   1: class Circle extends GeometricFigure

   2: {

   3:     radius: number;

   4:

   5:     constructor(name: string, radius: number)

   6:     {

   7:         super(name);

   8:         this.radius = Math.max(radius, 0);

   9:     }

  10: }

  11:

  12: var geometricFigure: GeometricFigure = new Circle("Circle", 73);

 

Abstrakte Methoden

Nicht nur Klassen können in modernen objekt-orientierten Programmiersprachen (kurz: OOP-Sprachen) als abstrakt deklariert werden, auch Methoden können mit dem abstract-Keywort ausgestattet werden. Diese stellen mit ihrer Signatur und Rückgabewert eine Art bindenden Vertrag für ableitende Klassen dar und müssen von diesen dann konkret implementiert werden. Genau nach diesen Vorgaben verhält es sich auch in TypeScript.

Abstrakte Methoden können nur in abstrakten Klassen deklariert werden. Der Versuch, eine abstrakte Methode in einer konkreten Klasse anzulegen, erzeugt, wie in folgender Abbildung exemplarisch dargelegt, eine Compiler-Meldung:

Grafik-M.Pohl3a

Ebenso verhält es sich, wenn in der konkreten Klasse die Implementierung einer abstrakten Methode aus der Basisklasse fehlt:

Grafik M.Pohl4

Eine korrekte Implementierung des oben aufgeführten Beispiels einer abstrakten Basisklasse mit einer abstrakten Methode, stellen die folgenden beiden Code-Ausschnitte dar:

   1: abstract class GeometricFigure {

   2:     public name: string;

   3:

   4:     constructor(name: string) {

   5:         this.name = name;

   6:     }

   7:

   8:     abstract calculateArea(): number;

   9: }

  10:

  11: class Circle extends GeometricFigure

  12: {

  13:     public radius: number;

  14:

  15:     constructor(name: string, radius: number)

  16:     {

  17:         super(name);

  18:         this.radius = Math.max(radius, 0);

  19:     }

  20:

  21:     calculateArea(): number

  22:     {

  23:         return Math.pow(this.radius, 2) * Math.PI;

  24:     }

  25: }

Die anschließende Instanziierung und der anschließende Funktionsaufruf sehen wie folgt aus:

   1: var geometricFigure: GeometricFigure = new Circle("Circle", 73);

   2: var calculatedArea = geometricFigure.calculateArea();

Abstrakte Properties

Die Programmiersprache Java kennt (zum aktuellen Zeitpunkt) keine Propertys. Sämtliche Aktionen erfolgen entweder durch direkten Zugriff auf die einzelnen Felder, sofern mit dem richtigen Modifier versehen, oder über Methoden-Aufrufe. C# kennt Propertys, welche Eigenschaften von Klassen repräsentieren und auch als abstrakt deklariert werden können. TypeScript-Klassen können sich zwar des im .NET-Framework bekannten Property-Konzeptes bedienen, allerdings ist es nicht möglich, diese mit dem abstract-Schlüsselwort zu versehen. Hier gibt der Compiler wieder unmittelbar eine entsprechend Fehlermeldung zurück.

Grafik M.Pohl8 Kopie

Die Verwendung des abstract-Schlüsselwortes beschränkt sich momentan also ausschließlich auf Klassen und Methoden.

Fazit

Wie jede Programmiersprache, hat auch TypeScript einige gewöhnungsbedürftige Eigenschaften.  Die in diesem Artikel aufgezeigte Regel für parameterlose Konstruktoren in konkreten Klassen bzgl. Vererbungshierarchien zählt sicherlich hierzu.

Der Artikel belegt aber auch, dass das eingeführte abstract-Schlüsselwort eine nützlich Erweiterung der noch recht jungen Programmiersprache TypeScript (Erscheinungsjahr 2012) darstellt. Es ermöglicht, bei der Entwicklung von Web-Applikationen, objektorientierte Paradigmen für Vererbungshierarchien zu berücksichtigen, welche aus anderen OOP-Sprachen heute kaum noch wegzudenken sind. Zwar kann das abstract-Schlüsselwort zum aktuellen Zeitpunkt nur auf TypeScript-Klassen und deren Methoden angewendet werden, dennoch dürfte sich seine Verwendung in Zukunft immer größer werdender Beliebtheit erfreuen.

Quellen

  1. Announcing TypeScript 1.6 (Letzter Zugriff: 2016-04-25)
  2. What’s new in TypeScript (Letzter Zugriff: 2016-04-25)
  3. TypeScript (Letzter Zugriff: 2016-04-25)
  4. Wikipedia: TypeScript (Letzter Zugriff: 2016-04-25)

Advanced T-SQL: Vorsicht mit Short-Circuit Evaluation

Kurzschlussauswertungen, welche in IT-Fachkreisen häufig auch als bedingte Auswertungen bezeichnet und im Englischen mit dem Begriff Short-circuit evaluation übersetzt werden, beschreiben in der Informatik Strategien zur Auswertung boolescher Formeln. Mit Hilfe von Kurschlussauswertungen können Analyseprozesse langer und komplexer boolescher Terme vorzeitig abgebrochen werden, sofern das Endergebnis des auszuwertenden Gesamtausdrucks bereits vor Ermittlung der letzten atomaren Bedingung feststeht.

 

Meine Artikelserie zum Thema “Advanced T-SQL” umfasste bislang diese Beiträge:

Im Rahmen meines nun folgenden Artikels wird auf Kurzschlussauswertungen in Verbindung mit T-SQL und dem Microsoft SQL-Server eingegangen. Dabei werden insbesondere auch einige mögliche Stolperfallen bei der Konvertierung numerischer Werte mit den SQL-Datentypen VARCHAR und NVARCHAR aufgezeigt.

 

Short-Circuit Evaluation in modernen Programmiersprachen

Durch das Konzept der Kurschlussauswertung kann oftmals einen Gewinn hinsichtlich der Performance erzielt werden. Insbesondere Compiler moderner objektorientierter Programmiersprachen wie C# oder Java verwenden daher Kurzschlussauswertungen als Mittel zur Optimierung. Anhand des folgendes C#-Snippets wird dies exemplarisch belegt:

   1: private static bool ComplexCheckMethod()

   2: {

   3:     // Some C#-Code

   4:     return [CheckResult];

   5: }

   6:  

   7: static void Main(string[] args)

   8: {

   9:     if (false && ComplexCheckMethod())

  10:     {

  11:         ...

  12:     }

  13: }

Da die Bedingung innerhalb der if-Anweisung (Zeile 9) niemals wahr werden bzw. den Wert “true” annehmen kann, wird das Ergebnis der zuvor implementierten ComplexCheckMethod-Funktion an dieser Stelle nicht benötigt und der Quellcode innerhalb der Funktion somit gar nicht erst ausgeführt. Moderne Entwickler-Tools wie beispielsweise der ReSahrper der Firma JetBranis erkennen dies und weisen den Entwickler darauf hin.

 

Short-Circuit Evaluation in T-SQL

Mit T-SQL innerhalb des SQL-Server verhält es sich bezüglich Kurzschlussauswertungen (leider) etwas anders. Hier hängt der Einsatz einer möglichen Short-Circuit Evaluation vom eingesetzten Datentypen innerhalb des T-SQL-Statements ab. Wird beispielsweise diese Tabellenstruktur

   1: CREATE TABLE TblShortCircuitEvaluation

   2: (

   3:     VarCharColumn VARCHAR(1000),

   4:     NVarCharColumn NVARCHAR(1000),

   5: );

mit folgendem Inhalt verwendet

image

so liefert eine Abfrage mit Filter auf die VARCHAR(1000)-Spalte

SELECT * 

FROM TblShortCircuitEvaluation

WHERE ISNUMERIC(VarCharColumn) = 1 AND (CAST(VarCharColumn AS INT) = 42);

ein gültiges Abfrageergebnis, nämlich:

image

Wird dagegen die gleiche Filterbedingung auf die NVARCHAR(1000)-Spalte angewendet, also folgendes Statement abgesetzt

SELECT * 

FROM TblShortCircuitEvaluation

WHERE ISNUMERIC(NVarCharColumn) = 1 AND (CAST(NVarCharColumn AS INT) = 42);

, so wird statt eines Abfrageergebnisses ein Konvertierungsfehler zurückgegeben:

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value 'TextValue' to data type int.

In zweiten Fall, also bei Verwendung der NVARCHAR(1000)-Spalte, wird für alle Datensätze innerhalb der Datenbanktabelle, auch wenn die erste Bedingung ISNUMERIC(NVarCharColumn)=1 keinen TRUE-Wert zurückliefert, die anschließende Konvertierung versucht durchzuführen und die zweite Filterbedingung ausgewertet. Hierbei entsteht nun der oben aufgeführte der Konvertierungsfehler.

Warum dieses Verhalten genau so an den Tag gelegt wird, ist leider nicht klar ersichtlich. Im Internet existieren zwar zahlreiche Foren und Portale, die sich mit dem Thema Kurzschlussauswertung und SQL auseinander setzen, aber eine genaue Erklärung dafür kann nicht gefunden werden. Selbst dem standardisierten ANSI-SQL ist nur folgendes Zitat zu entlocken:

"Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression." (Kapitel 6.3.3.3 Rule evaluation order, Seite 41 ff)

Auf gut Deutsch: Im SQL wird die abgekürzte boolesche Evaluation leider nicht automatisch verwendet. Die Datenbank-Implementation kann sie gebrauchen, muss aber nicht Zwinkerndes Smiley.   

Damit die obere Abfrage mit Filter auf der NVARCHAR(1000)-Spalte auch fehlerfrei ausgewertet und ein gültiges Ergebnis zurückliefert, empfiehlt es sich eine kleine Sicherheit im SQL-Statement einzubauen. Diese könnte beispielsweise wie folgt aussehen:

SELECT * 

FROM TblShortCircuitEvaluation

WHERE CAST((CASE WHEN ISNUMERIC(NVarCharColumn) = 1 THEN NVarCharColumn END) AS INT) = 42;

Hier wird nun ein gültiges Abfrageergebnis zurückgeliefert, nämlich:

image

Fazit

Im diesem Artikel wurde gezeigt, dass leider weder mit T-SQL innerhalb des Microsoft SQL-Server noch mit standardisierten ANSI-SQL abgekürzte boolesche Evaluationen mittels Kurschlussauswertung wirklich garantiert werden können. Um auf Nummer sicher zu gehen, empfiehlt es sich sogar immer von einer Prüfung und Auswertung alle Terme bzw. sämtliche einzelne Bedingungen eines SQL-Statements auszugehen.

Des Weiteren sollten auch in Anbetracht von Kurzschlussauswertung Änderungen von SQL-Datentypen immer kritisch hinterfragt werden. Selbst anfangs unbedenklich erscheinende  Aussagen wie "Wir ändern mal eben schnell den Datentyp von VARCHAR auf NVARCHAR" können durchaus gefährlich sein und unerwünschte Auswirkungen auf bestehende SQL-Statements haben. Die Code-Beispiele in diesem Artikel zeigen dies deutlich und es gilt auch hier das bekannte Sprichwort: der Teufel steckt im Detail Teufel.

 

Quellen:

  1. char und varchar (Transact-SQL) (Letzter Zugriff: 2014-12-12)
  2. nchar und nvarchar (Transact-SQL) (Letzter Zugriff: 2014-12-12)

Advanced T-SQL: OUTPUT und $IDENTITY

Wenn innerhalb des Microsoft SQL-Servers neue Daten in eine Tabelle mit einer Identitätsspalte eingefügt werden, stellt sich häufig folgende Frage: Wie können am effektivsten die neuen ID-Werte der zuletzt eingefügten Datensätze ermittelt werden?
 
Der folgende Flurfunkartikel repräsentiert einen weiteren Teil einer kleinen Serie zum Thema "Advanced T-SQL", zu welchem bereits die Artikel Advanced T-SQL: ISNULL vs. COALESCE und Advanced T-SQL: WITH TIES erschienen sind.
 
Die eben beschriebene Problemstellung soll im Beitrag zunächst anhand einfacher T-SQL-Code-Beispiele dargelegt werden. Anschließend werden durch Einsatz der OUTPUT-Klausel elegante Lösungsansätze aufgezeigt.
Neue Werte in Identitätsspalte einfügen

Per Default wird der Wert einer Identitätsspalte nach Absetzen des INSERT-Befehls automatisch ermittelt. Wird beispielsweise folgende Tabellenstruktur

   1: CREATE TABLE [TblExpert]

   2: (

   3:     [ExpertID] INT IDENTITY(1, 1) NOT NULL,

   4:      [Name] NVARCHAR(MAX) NOT NULL,

   5:     [Nachname] NVARCHAR(MAX) NOT NULL

   6: );

verwendet und anschließend ein INSERT-Statement in der Form

   1: INSERT INTO TblExpert(Name, Nachname) VALUE ('Matthias', 'Pohl');

auf die neu angelegte Tabelle abgesetzt, so kann mit einer geeigneten Abfrage, z.B.

   1: SELECT [ExpertID], [Name], [Nachname]

   2: FROM [TblExpert]

   3: WHERE [ExpertID] = 

   4: (

   5:     SELECT MAX([ExpertID])

   6:     FROM [TblExpert]

   7: );

der neue Datensatz mit der ID-Spalte (hier im konkreten Fall: “[ExpertId]”) ermittelt werden.

Die nachfolgende Abbildung zeigt das Ergebnis der Abfrage:
image3_thumb
 
Der Nachteil bei diesem Verfahren ist, dass – ohne Kenntnis des CREATE-Statements zur Tabelle – die “[ExpertId]”-Spalte nicht automatisch als Identitätsspalte erkannt wird. Des Weiteren muss sowohl für das Einfügen als auch das anschließende Auslesen der Daten jeweils einmal auf die Datenbank zugegriffen werden. Wird zusätzlich noch berücksichtigt, dass ohne Transaktionsblock um das INSERT- und SELECT-Statement zusätzlich Dateninkonsistenzen durch Nebenläufigkeit bzw. Parallelität entstehen können, zeigt sich sehr schnell, dass diese Variante der Wertermittlung für die ID-Spalte nicht sonderlich geeignet ist.
 
Systemfunktionen für Identitätsspalte
Um Identitätsspalten auszulesen, bietet der SQL-Server nun einige Systemfunktionen an. Die bekanntesten sind hierbei sicherlich: 
Mit vorangestelltem SELECT-Schlüsselwort kann durch diese Funktionen der neu eingefügte Wert der Identitätsspalte ermittelt werden.
Doch auch die Verwendung dieser Systemfunktionen birgt einige Nachteile. Die oben aufgeführten Funktionen im SQL-Server können durch zusätzliche mögliche Schreiboperationen signifikant beeinflusst werden. Hierzu zählen insbesondere parallele Datenbank-Zugriffe ohne Transaktionskontrolle sowie möglicherwiese ausgelöste Trigger-Aktionen.
 
Wird beispielsweise für die [TblExpert]-Tabelle ein Trigger in der Form
   1: CREATE TRIGGER TblExpertAfterInsert

   2: ON [TblExpert]

   3: AFTER INSERT

   4: AS 

   5: BEGIN

   6:     IF TRIGGER_NESTLEVEL() > 1 RETURN

   7:     

   8:     INSERT [TblExpert] 

   9:     (Name, Nachname)

  10:     SELECT Name+'2', Nachname+'2' FROM Inserted;

  11: END

 
hinterlegt und werden anschließend die folgenden beiden SQL-Statements ausgeführt
   1: INSERT INTO TblExpert(Name, Nachname)

   2: VALUES ('Matthias', 'Pohl');

   3:  

   4: SELECT 

   5: @@IDENTITY AS '@@IDENTITY', 

   6: SCOPE_IDENTITY() 'SCOPE_IDENTITY()', 

   7: IDENT_CURRENT('[TblExpert]') AS 'IDENT_CURRENT(''[TblExpert]'')';

so liefern die oben erwähnten Systemfunktionen, wie in der folgenden Abbildung dargestellt, unterschiedliche Werte:
 
image
 
Des Weiteren wird auch hier, wie im vorrangegangen Beispiel, beim Ermitteln des Wertes ein zweiter (unnötiger) Lesezugriff auf die Datenbank erfordert.
Die folgende Abbildung, welche die Message-Konsole des SQL-Server Management Studios zeigt, legt dar, dass sowohl für den INSERT- als auch den anschließenden SELECT-Befehl jeweils ein Zugriff erfolgt:
image_thumb2
 
Verwendung der OUTPUT-Klausel
Datenmanipulationsbefehle (kurz: DML-Anweisungen) wie MERGE, INSERT, UPDATE oder DELETE sind hauptsächlich für das Bearbeiten und Verändern von Datensätzen konzipiert worden. Durch Einsatz der OUTPUT-Klausel können jedoch zusätzlich (und quasi gleichzeitig) noch Datensätze mit zurückgegeben werden. 
Als Beispiel dient folgende Einfüge-Operation, welche sich am obigen Szenario orientiert und das $IDENTITY-Schlüsselwortes/-Prädikats verwendet:
   1: INSERT INTO TblExpert([Name], [Nachname]) 

   2: OUTPUT INSERTED.$IDENTITY

   3: VALUES ('Matthias', 'Pohl');

 
Mit Hilfe der OUTPUT-Klausel und des $IDENTITY-Prädikats innerhalt des obigen SQL-(INSERT)-Statements können mittels Zugriff auf die INSERTED-Tabelle durch eine einzige atomare SQL-Anweisung neue Datensätze eingefügt und zeitgleich die neu eingefügten Werte in der ermittelt werden. Hierfür sind keine(!) weiteren Lesezugriffe auf der Datenbank und somit auch keine Zugriffskontrolle notwendig, der Zugriff auf die Datenbank erfolgt hier nur einmal.
 
Als Ergebnis des obigen INSERT-Statements wird folgender Datensatz zurückgegeben:
image_thumb41
Des Weiteren werden keine zusätzlichen Funktionaltäten (z.B. komplexe Aggregationen etc.) gegenüber einem INSERT-Statement  ohne OUTPUT-Klausel benötigt. Die folgende Abbildung, welche den Ausführungsplan eines INSERT-Statement mit und eines ohne OUTPUT-Klausel gegenüber stellt, belegt dies: 
image_thumb4
 
Fazit
Der Artikel zeigt den Einsatz der OUTPUT-Klausel vorrangig anhand von einfachen INSERT-Befehlen  für Tabellen mit Identitätsspalte. Der Einsatzes des OUTPUT-Schlüsselworts offeriert jedoch noch weitere Vorteile. Zum einen kann er auch für MERGE-, DELETE- und UPDATE-Statements verwendet werden und zum anderen funktioniert er auch wenn eine T-SQL-Anweisungen Einfluss auf mehrere Datensätze, z.B. multipler INSERT-Befehl, hat. Dann können nämlich sämtliche neu eigefügte Datensätze ermittelt werden.
 
Quellen

Advanced T-SQL: WITH TIES

Office Days sind bei der SDX AG monatlich etablierte Veranstaltungen, bei denen sich alle Mitarbeiter in den firmeninternen Räumlichkeiten zusammenfinden. Neben der Förderung und Stärkung der eXpert-Community dienen diese Events auch zum Informationsaustausch und Knowhow-Transfer zu diversen Themen zwischen den Kollegen.

An einem der letzten SDX Office Days habe ich eine Breakout-Session zum Thema “Advanced T-SQL” gehalten, bei dem ich einige spezielle Features der Abfragesprache T-SQL zeigen durfte. Zu diesem Vortrag gehörte u.a. auch die Vorstellung der “WITH TIES”-Option, welche zwar schon seit langer Zeit fester Bestandteil von T-SQL ist, zu meiner großen Überraschung aber dem ein oder anderen Kollegen noch nicht bekannt war.
Im Rahmen dieses Artikels soll gezeigt werden, dass auch im Bereich der Datenbank-Programmierung mit T-SQL beim Vergleichen und Sortieren von Werten bestimmte Bedingungen berücksichtigt werden müssen. Insbesondere wenn mit “SELECT TOP [N]”-Abfragen eine fixe Anzahl von Datensätzen aus der Datenbank auslesen werden, sollte bei mehreren möglichen Abfrageergebnissen (zumindest etwas) Vorsicht geboten sein.

Vorsicht bei SELECT TOP N

Folgende T-SQL-Syntax erlaub es, eine feste Anzahl von Datensätzen, welche nach bestimmten Eigenschaften sortiert sind, aus einem Datenbank-Objekt (z.B. Tabellen oder Views) abzufragen:
 
   1:  SELECT TOP [positivIntValue] [Column1], ... , [ColumnN]
   2:  FROM [DbObject]
   3:  ORDER BY [SortPredicate1] [ASC/DESC], ..., [SortPredicateN] [ASC/DESC]

So weit so gut: Doch was ist, wenn es mehrere mögliche Ergebnisse für ein solches “SELECT TOP N”-Statement mit entsprechendem Sortierkriterium gibt? Ab dem Absetzen des SQL-Befehls ist das Abfrageergebnis von der zugrundenliegenden Datenbank-Implementierung abhängig und kann nicht weiter beeinflusst werden.
Dass dieser Umstand durchaus Probleme mit sich bringen kann, soll folgende Gesamtmenge von günstigen (Klein-)Fahrzeugen Smiley in einer einfachen Tabelle dienen:
 

image_thumb9_thumb

Wenn die drei teuersten Autos ermittelt werden sollen, bietet sich zunächst folgendes T-SQL-Statement an:
 
   1:  SELECT TOP 3 
   2:  Color, Name, Price 
   3:  FROM TblCars 
   4:  ORDER BY Price DESC;
 
Das Problem, bei dieser Abfrage ist, dass sie für die Gesamtmenge mit den angegebene  5 Autos kein eindeutiges Ergebnis zurückliefern kann. Beide der folgenden Abbildungen enthalten Objekte mit den gesuchten Kriterien und stellen somit gültige Ergebnisse der vorherigen SQL-Abfrage dar:
 
image_thumb6
 
oder
image_thumb7

WITH TIES-OPTION

Wenn es mehrere Einträge gibt, die dem angegebenen Sortierkriterium entsprechen und bei der Abfrage mit Berücksichtigung finden sollen, bietet sich die “WITH TIES”-Option mit folgender Syntax an:
 
   1:  SELECT TOP [positivIntValue] 
   2:  WITH TIES [Column1], ..., [ColumnN]
   3:  FROM [DbObject]
   4:  ORDER BY [SortPredicate1] [ASC/DESC], [...], [SortPredicateN] [ASC/DESC]

Die drei teuersten Wagen aus dem aktuellen Beispiel können mit folgender T-SQL-Formulierung ermittelt werden:
 
   1:  SELECT TOP 3 
   2:  WITH TIES 
   3:  Color, Name, Price 
   4:  FROM TblCars
   5:  ORDER BY Price DESC;

Das Ergebnis dieser Abfrage liefert, wie die folgende Abbildung zeigt, alle Datenbankeinträge, die dem angegebene Sortierkriterium entsprechen entsprechen. Der Vorteil hierbei ist, dass insbesondere auch doppelte Einträge berücksichtigt werden. Außerdem ist dieses Abfrageergebnis komplett deterministisch und eindeutig wiederholbar.
 
image

Zusammenfassung

Natürlich hängt es immer vom konkreten Anwendungsfall ab, wie mehrere mögliche Abfrageergebnissen behandelt werden sollen. Wenn es aber darum geht, mehre alle Datensätze mit gleichen Sortierbedingungen zu ermitteln und man sich nicht auf die Willkür der eingesetzten Datenbank-Technologie verlassen will, dann ist die WITH TIES-Option eine valide Empfehlung.
Als praktische Anwendungsszenarien aus dem realen Alltag dienen beispielsweise Sportveranstaltungen wie die olympischen Spiele . Bei solchen Sport-Events muss im Gedanken an das sportliche Fairplay immer eine Antwort bzw. klare Regelung gefunden werden, wenn darum geht, gleiche Platzierungen entsprechend zu honorieren.

Quelle

Advanced T-SQL: ISNULL vs. COALESCE

"To Be or not to Be!" heißt es in einem berühmten Monolog aus William Shakespeares Tragödie Hamlet im dritten Akt. Für Datenbankentwickler stellt oftmals eine ähnliche Frage, welche lautet: "NULL OR NOT NULL?"

Der Grund, weshalb eine Variable oder ein Attribut leer ist bzw. keinen konkreten Wert besitzt, kann mehrere Ursachen haben. Beispielsweise kann es auch in der heutigen Zeit durchaus noch vorkommen, dass eine Person kein Mobiltelefon besitzt und somit ein entsprechendes “Handy-Nr.”-Feld in einer Adressdatenbank nicht mit Informationen versehen werden kann.

Der Umgang mit NULL-Werten ist in der Datenbankentwicklung durchaus nicht immer trivial und kann zu manchem Komplikationen führen. Um  Probleme mit möglichen NULL-Werten in der Programmierung mit reinem T-SQL im Microsoft SQL-Server zu vermeiden kommen daher häufig die ISNULL– oder die COALESCE-Funktion zum Einsatz. Im Rahmen dieses Artikels sollen diese beiden Funktionen etwas genauer vorgestellt und insbesondere auf signifikante Unterschiede hingewiesen werden.

Einsatz von ISNULL und COALESCE

Die ISNULL- und COALESCE-Funktion stellen verkürzte Abfrageformen dar, wenn es darum geht mehrere Attribute oder Variablen hinsichtlich möglicher NULL-Werte zu analysieren.

Sollen beispielsweise mit T-SQL mehrere Attribut-Werte selektiert und der erste Nicht-NULL-Wert zurückgegeben werden, bietet sich, sofern keine Kenntnisse von ISNULL oder COALESCE vorhanden sind, ein CASE-Expression folgender Form an:

   1: SELECT 

   2:     CASE

   3:         WHEN Value1 IS NOT NULL THEN Value1

   4:         WHEN Value2 IS NOT NULL THEN Value2

   5:         [...]

   6:         WHEN ValueN IS NOT NULL THEN ValueN

   7:         ELSE NULL

   8:     END;

Der Nachteil bei dieser Abfrage mit CASE-Expression ist offensichtlich. Sie erscheint weniger SQL-affinen Entwicklern sehr komplex und kann, je nach Anzahl der möglichen Werte, sehr lang und unübersichtlich werden.

Deutlich kürzer und lesbarer kann ein solches Statement werden, wenn die COALESCE-Funktion zum Einsatz kommt:

   1: SELECT COLESCE(Value1, Value2, [...], ValueN);

Kommen gar nur zwei Werte als mögliche Nicht-NULL-Werte in Frage, bietet sich auch die ISNULL-Funktion in folgendem Format an:

   1: SELECT ISNULL(Value1, Value2);

Das Resultat, wenn die beiden letzten SELECT-Statements ausgeführt werden, ist in beiden Fällen gleich: Sowohl das Ergebnis der COALESCE- als auch das der ISNULL-Funktion liefert den ersten Attribut-/Variablen-Wert zurück, welches keinen NULL-Wert besitzt.

 

Unterschiede zwischen ISNULL und COALESCE

Es hat den Anschein, dass sich die ISNULL-und COALESCE-Funktion für sehr ähnliche Anwendungsszenarien eignen. Im Folgenden werden nun die wesentlichen Unterschiede zwischen diesen beiden Funktionen dargelegt:

1. Standardisierung/Normierung

Auch wenn der erste Unterschied nur wie eine kleine Randnotiz erscheint, so sollte es doch zumindest kurz erwähnt werden: Die COALESCE-Funktion ist fixer Bestandteil des vom American National Standards Institute definiert und genormten ANSI SQLs. Sie kann somit eventuell also auch bei anderen SQL-Dialekten oder Datenbank-Providern mit SQL-basierten Abfragesprachen vorgefunden werden. Die ISNULL-Funktion steht in der in diesem Artikel beschriebene Variante dagegen nur explizit beim Einsatz von T-SQL zur Verfügung.

2. Anzahl der Parameter

Während die ISNULL-Funktion genau zwei Parameter erwartet, können an die COALESCE-Funktion mehr als zwei Funktionsargumente übergeben werden. Der folgende Code-Ausschnitt zeigt gültige Aufrufe für die beiden Funktionen, wobei der COALESCE-Funktion natürlich noch weitere Parameter als Funktionsargumente übergeben werden können:

   1: DECLARE @x1 VARCHAR(1) = 'a';

   2: DECLARE @x2 VARCHAR(1) = 'b';

   3: DECLARE @x3 VARCHAR(1) = 'c';

   4: DECLARE @x4 VARCHAR(1) = 'd';

   5: SELECT COALESCE(@x1, @x2, @x3, @x4) [COALESCE], ISNULL(@x1, @x2) [ISNULL];

3. Ergebnis-Typ

Werden unterschiedliche Datentypen an die Funktionen übergeben, so zeigt sich ein sehr signifikanter Unterschied. COALESCE betrachtet die Datentypen aller übergebenen Parameter und liefert den Typ mit der höchsten Präzedenz bzw. Rangfolge zurück. Die ISNULL-Funktion dagegen verwendet den Typen des ersten Parameters als finalen Ergebnistyp. Der folgende Code-Ausschnitt stellt dies exemplarisch dar.

   1: DECLARE @x VARCHAR(3) = NULL;   

   2: DECLARE @y NVARCHAR(10) = '1234567890';

   3: SELECT COALESCE(@x, @y) AS [COALESCE], ISNULL(@x, @y) AS [ISNULL];

Die COALESCE-Funktion liefert den kompletten Inhalt der @y-Variablen zurück. Dieser besitzt eine höhere Datentyp-Rangfolge gegenüber der @x-Variablen. Bei der ISNULL-Funktion wird der Ergebnistyp bereits durch den ersten Parameter (hier: @x-Variable) auf VARCHAR(3) festgelegt. Der zweite Parameter wird dann in den Datentypen des ersten Parameters umgewandelt. Die folgende Abbildung zeigt das Ergebnis der SQL-Statements des vorranggegangene Code-Ausschnitts: 

image

Hierbei ist also durchaus Vorsicht angebracht, damit nicht aus Versehen Daten formatiert und wichtige Informationen verändert werden.

4. NULL-Parameter Validierung

Die COALESCE-Funktion benötigt mindestens einen Parameter, welcher keine NULL-Konstante ist. Ansonsten kann der SQL-Befehl nicht erfolgreich ausgeführt werden. Somit stellt folgender Code-Ausschnitt ein ungültiges SQL-Statement dar:

   1: SELECT COALESCE(NULL, NULL) [COALESCE(NULL, NULL)];

Nach Absetzen dieses Statements wird im SQL-Server Management Studio folgende Fehlermeldung zurückgeliefert:

Msg 4127, Level 16, State 1 
At least one of the arguments to COALESCE must be an expression that is not NULL constant.


Die ISNULL-Funktion kann dagegen ausschließlich mit NULL-Konstanten aufgerufen werden. Folgendes SQL-Statement ist beispielsweise valide:

   1: SELECT ISNULL(NULL, NULL) [ISNULL(NULL, NULL)];

Das Ergebnis ist, wie zu erwarten, NULL:

image

Das Verhalten lässt sich durch die unterschiedliche Typ-Validierung der NULL-Parameter-Validierung durch die beiden Funktionen erklären. Während die ISNULL-Funktion die NULL-Werte in die Datentypen INT bzw. INTEGER konvertiert, verwendet die COALESCE-Funktion die Datentypen der übergebene Parameter. Dieses Problem kann umgangen werden, indem, wie im folgenden Code-Ausschnitt dargestellt, ein NULL-Parameter auf den INT-Datentypen abgebildet wird:

   1: SELECT COALESCE(NULL, CAST (NULL AS INT)) [COALESCE_WITH_CAST];

Das Ergebnis des SQL-Statements ist dann wie bei der vorherigen Abfrage NULL:

image
5. Ausführungspläne

Als letzter interessantester Unterschied zwischen diesen beiden Funktionen sollen noch die unterschiedlichen Ausführungspläne der ISNULL- und COALESCE-Funktion untersucht werden. Dies kann insbesondere bei größeren Datenmengen durchaus extreme Auswirkungen hinsichtlich der Performance des eingesetzten Datenbank-Servers haben. 

Als Beispiel dient eine einfache Tabellenstruktur mit wie im folgenden Code-Ausschnitt beschrieben:

   1: CREATE TABLE Tbl (TblColumn int);

Anschließend wird folgendes SQL-Statements ausgeführt:

   1: SELECT ISNULL((SELECT MAX(TblColumn) FROM Tbl), 1);

Der Ausführungsplan des eben beschriebenen SQL-SELECT-Befehls liefert folgenden Verlauf:

image

Wird im SQL-Statements nun die ISNULL-Funktion durch die COALESCE-Funktion ersetzt, so ergibt sich folgendes Code-Snippet:

   1: SELECT COALESCE((SELECT MAX(TblColumn) FROM Tbl), 1);

Letztes SQL-SELECT-Statement ausgeführt liefert schließlich einen Ausführungsplan welcher in der folgenden Grafik abgebildet ist:

image

Es zeigt sich, dass der Ausführungsplan des SQL-Statements mit der COALESCE-Funktion mehr Stream-Aggregationen, Table-Scans und Join-Operationen beinhaltet. Die unterschiedlichen Ausführungspläne zwischen ISNULL und COALESCE erklären sich daraus, dass COALESCE für den Query-Optimizer opak ist. Das bedeutet, dass der Optimizer das Ergebnis der Funktion nicht vorhersagen kann. Dagegen ist die Anwendung von ISNULL transparent und wird bei der Optimierung mit einbezogen. In der Regel wird der Einsatz von ISNULL daher zu besseren Query-Plänen führen.

 

Zusammenfassung

Für die Programmiersprache T-SQL stellen diese beiden Funktionen im Zusammenhang mit NULL-Werten sicherlich eine nützliche Unterstützung für Datenbank-Entwickler dar. Dennoch sollte ihr Einsatz immer mit Vorsicht bedacht werden und zielgerichtet erfolgen, denn der Teufel Teufel steckt bekanntlich im Detail Zwinkerndes Smiley.

 

Quellen:

  1. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (Letzter Zugriff: 21.05.2014)
  2. BNF Grammar for ISO/IEC 9075:1992 – Database Language SQL (SQL-92) (Letzter Zugriff: 21.05.2014)
  3. Differences between ISNULL and COALESCE (Letzter Zugriff: 21.05.2014)

FTS im MS SQL-Server (3. Teil): Thesauri und Synonyme

Vom Philosophen Ludwig Wittgenstein stammt folgendes Zitat: "Die Grenzen meiner Sprache bedeuten die Grenzen meiner Welt". Während einzelne Bilder und Grafiken häufig eindeutige Sachverhalte beschreiben – daher vermutlich auch das berühmte Sprichwort „Ein Bild sagt mehr als 1000 Worte“ – können je nach Kontext unterschiedliche Worte ähnliche oder gar die gleiche Bedeutungen haben. Im letzteren Fall wird dann konkret von Synonymen gesprochen.

Mein erster Beitrag Flexionen-Abfragen mit FTS und MS SQL-Server zeigte, wie die Volltextsuche für Abfragen von Wortbeugungen bzw. Flexionen eingesetzt werden kann. In meinem zweiten Artikel konnten die wichtigsten Komponenten, die für die Volltextsuche innerhalb des SQL-Servers benötigt werden, sowohl vorgestellt als auch deren Zusammenspiel untereinander näher beschrieben werden.

In diesem dritten und letzten Blog-Beitrag dieser kleinen Serie wird nun auf das Thesauri-Konzept innerhalb der Volltextsuche des MS SQL-Server eingegangen. Mit Hilfe von Thesauri bzw. Thesauren – beide Plural-Formen sind laut aktueller deutscher Rechtschreibung gültig – können Begrifflichkeiten organisiert werden, zwischen denen eine bestimmte Beziehung besteht oder die einen ähnlichen Bedeutungsumfang besitzen.

Motivation – Suchmaschinen

Ein klassisches Anwendungsbeispiel für die Verwendung ähnlicher Begriffe oder Synonyme in der IT sind Suchmaschinen wie Bing oder Google. Ein Benutzer, welcher im Internet nach dem Begriff "Schreibutensilien" recherchiert, wird sich meisten auch freuen, wenn ihm Artikel aus der Rubrik "Büromaterial" als Suchergebnis zurückgeliefert werden. Wird in einem konkreten Fall beispielsweise ein ”Heftgerät” gesucht, so werden sicherlich auch Produkte mit der synonymischen aber auch umgangssprachlicheren Bezeichnung "Tacker" akzeptiert. Vielleicht wird neben einem Heftgerät gleich noch ein neuer "Locher" benötigt, der sofort bestellt werden kann, und wäre es dann nicht optimal, wenn all diese "Schreibwaren” – schon wieder ein Synonym Smiley – strukturiert und übersichtlich auf einer Seite angeboten würden?

Eine Stärke moderner Suchmaschinen – egal welcher Anbieter nun präferiert wird – besteht darin, die eingegeben Suchbegriffe nicht nur als lose Worte oder Textbausteine zu interpretieren, sondern diese in den semantischen Kontext des Suchers einzugliedern. Die folgende Abbildung zeigt mögliche Suchergebnisse der von Microsoft entwickelten Suchmaschine Bing für die beiden Begriffe "schreibwaren" und "heftgerät" (in diesem Beispiel bewusst klein geschrieben) und stellt somit eine sicherlich gute Lösung für das eben beschriebene Szenario dar:

image

Verwaltung von Thesaurus-Wörterbüchern

Zur Verwaltung ähnlicher oder verwandter Wörter für die Volltextsuche innerhalb des SQL-Servers bedarf es Thesaurus-Wörterbücher, welche die einzelnen Begrifflichkeiten in eine bestimmte Relation setzen und miteinander verknüpfen. Diese Thesaurus-Wörterbücher werden nach der Installation des SQL-Servers standardmäßig als einfache XML-Dateien im MSSQLFTData-Verzeichnis abgelegt[1].

Jede Sprache besitzt dabei ihr eigenes Thesaurus-Wörterbuch, welches durch einen eindeutigen Dateinamen gekennzeichnet ist. So lautet der Name für das englischsprachige Wörterbuch "tseng.xml", während für einen Thesaurus in deutscher Sprache die Datei "tsdeu.xml" zum Einsatz kommt. Neben den sprachspezifischen Wörterbüchern gibt es zusätzlich noch eine XML-Datei, welche für alle Sprachen gültig ist. Diese besitzt den Dateinamen "tsglobal.xml" [1].

Sowohl der Speicherort als auch die Dateinamen der Thesaurus-Datei können individuell verändert werden. Hierzu sind allerdings einige Konfigurationen notwendig, u.a. auch das Anpassen einiger Registrierungsschlüssel[1].

Die folgende Abbildung zeigt Ausschnitte des MSSQLFTData-Verzeichnisses, bei dem noch keine Änderungen bezüglich der Standardkonfiguration vorgenommen wurden, mit einigen Thesaurus-Wörterbüchern, insbesondere dem für die deutsche Sprache:

image
 

Aufbau von Thesaurus-Wörterbüchern

Eine Thesaurus-Datei, in welcher verwandte Begriffe oder Synonyme verwaltet werden, besitzt allgemein folgende XML-Struktur [1]:

   1: <XML ID="Microsoft Search Thesaurus">

   2:     <thesaurus xmlns="x-schema:tsSchema.xml">

   3:         <diacritics_sensitive>0/1</diacritics_sensitive>

   4:         <expansion>

   5:             <sub>Term1</sub>

   6:             <sub>Term2</sub>

   7:             <sub>...</sub>

   8:         </expansion>

   9:         <replacement>

  10:             <pat>Pattern</pat>

  11:             <sub>substitution1</sub>

  12:             <sub>substitution2</sub> 

  13:             <sub>...</sub>

  14:         </replacement>

  15:     </thesaurus>

  16: </XML>

Besonderer Bedeutung kommen hierbei drei Bereichen zu[1]:

  • Diakritische Zeichen:
Durch das <diacritics_sensitive>-Element wird festgelegt, wie diakritische Zeichen bzw. Wörter mit Akzenten innerhalb der Volltextsuche behandelt werden. Sollen beispielsweise die beiden Worte "café" und "cafe" nicht unterschieden werden, so muss “0” als Wert in dieses XML-Element eingefügt werden. Entsprechend muss, sofern die beiden Worte unterschiedlich behandelt werden sollen, der Wert “1” als Konfigurationseinstellung hinterlegt werden.

  • Erweiterungen:

Mit Hilfe von Erweiterungen, repräsentiert durch die <expansion>-Elemente, werden verwandte Begriffe und Synonyme zu einer Gruppe zusammen gefasst. Des Weiteren müssen alle Begriffe einer solchen Gruppe jeweils von einem <sub>-Elemente umgeben in das Thesaurus-Wörterbuch eingebettet werden, damit diese von der Volltextsuche gefunden werden können.
  • Ersetzungen:

Eine Ersetzung ist zunächst in ein <replacement>-Elemente eingeschlossen. Innerhalb dieses Elements können Begriffe, vom <pat>-Element umgeben, durch andere Textmuster, welche wiederum in ein  <sub>-Elemente eingebettet sein müssen, ausgetauscht werden. Im Gegensatz zu Erweiterungen werden durch die Volltextsuche bei Ersetzungen die substituierten Begriffe nicht mit zurück geliefert.

 

Modifizieren und Verwenden von Thesauri-Wörterbüchern

Im Folgenden wird anhand eines kleinen Beispielsszenarios schrittweise erläutert, wie Thesaurus-Wörterbücher angepasst und für die Volltextsuche zur Verfügung gestellt werden können. Des Weiteren wird noch gezeigt, wie mit Hilfe von SQL-Statements Synonyme abgefragt und gefunden werden können.
1.) Thesaurus-Datei anpassen

Im ersten Schritt gilt es zu überlegen, welche Begriffe zu einzelnen Gruppen von Synonymen zusammengefasst werden sollen. Entsprechend muss das Thesaurus-Wörterbuch bzw. die XML-Datei editiert werden.

Sollen beispielsweise, am Szenario aus Abschnitt “Motivation – Suchmaschinen” dieses Artikels orientiert, die Begriffe “Bürobedarf” und “Schreibutensilien” einer ersten Thesaurus-Gruppe zugeordnet werden und “Tacker”, “Hefter” und “Heftgerät” eine zweite Gruppe bilden, so könnte ein entsprechendes Thesaurus-Wörterbuch dem folgenden XML-Segment entsprechen:

   1: <XML ID="Microsoft Search Thesaurus">

   2:     <thesaurus xmlns="x-schema:tsSchema.xml">

   3:         <diacritics_sensitive>1</diacritics_sensitive>

   4:         <!-- 1. Gruppe-->

   5:         <expansion>

   6:             <sub>Bürobedarf</sub>

   7:             <sub>Schreibutensilien</sub>

   8:         </expansion>

   9:         <!-- 2. Gruppe-->

  10:         <expansion>

  11:             <sub>Tacker</sub>

  12:             <sub>Hefter</sub>

  13:             <sub>Heftgerät</sub>

  14:         </expansion>

  15:     </thesaurus>

  16: </XML>

2.) Thesaurus-Datei laden
Im zweiten Schritt muss die modifiziert Thesaurus-Datei in die SQL-Server-Instanz geladen werden. Dies erfolgt mit Hilfe der System-Prozedur sys.sp_fulltext_load_thesaurus_file. Als Parameter werden hier sowohl der LCID (=Language Code Identifier), d.h. der Ländercode für die gewünschte Sprachregion, als auch ein Kennzeichen für eventuell gewünschtes Überschreiben bereits bestehender Thesauri erwartet.

Der folgende Code-Ausschnitt zeigt das Laden der deutschsprachigen Thesaurus-Datei (LCID=1031) mit Überschreiben-Option (@loadOnlyIfNotLoaded = 0):

   1: EXEC sys.sp_fulltext_load_thesaurus_file 1031, @loadOnlyIfNotLoaded = 0;

3.) Thesaurus-Tabelle mit Begriffen anlegen

Als nächstes muss in der Datenbank, in welcher die Volltextsuche verwendet werden soll, eine Tabelle mit den entsprechenden Begriffen angelegt werden. Die Tabellen-Spalten, die für die Volltextsuche verwendet werden sollen, müssen dabei einem Volltext-Index zugewiesen werden. Wie dies erfolgen kann, wurde bereits in meinem zweiten Artikel detailliert erläutert. Insbesondere gilt es hier die speziellen Datentypen zu berücksichtigen, für die die Volltextsuche des SQL-Server zum Einsatz kommen können. Auch dies ist in meinem vorherigen Artikel dargelegt worden.

Die folgende Abbildung soll als Anschauungsbeispiel dienen. Hier wird der Design-Modus einer einfachen Datenbank-Tabelle (Name: “TblProduct”) gezeigt, welche eine Spalte vom Datentyp Integer (Name: “Id”) und eine Spalte des varchar-Datentyps (Name: “ProductDescription”) besitzt. In den Spalten-Eigenschaften kann "im “Full-text Specification”-Abschnitt überprüft werden, ob eine Datenbank-Spalte (hier die ProductDescription-Spalte) für die Volltextsuche benutzt werden kann:

 
image
 
4.) Thesauri-Begriffe einfügen
Nachdem die Thesauri-Tabelle erzeugt und konfiguriert worden ist, müssen als nächstes die Begriffe, die von der Volltextsuche eventuell gefunden werden sollen, in die Tabelle eingetragen werden. Für die  Begriffe “Büromaterial”, “Schreibutensilien”; “"Tacker”, “Heftgerät” und “Hefter”, welche auch im Beispiel-Thesaurus-Wörterbuch angegeben sind, kann dies durch folgendes INSERT-Statement erfolgen:
   1: INSERT INTO TblProduct(Id, ProductDescription)

   2: (

   3:     SELECT (1, 'Bürobedarf') UNION ALL

   4:     SELECT (2, 'Schreibutensilien') UNION ALL

   5:     SELECT (3, 'Tacker') UNION ALL

   6:     SELECT (4, 'Hefter')UNION ALL     

   7:     SELECT (5, 'Heftgerät')

   8: );

Mit einem einfachen “SELECT *”-Befehl sollte abschließend überprüft werden, ob die Begriffe wirklich in die Tabelle eingefügt worden sind. Im positiven Fall würde ein Abfragefrageergebnis wie folgt aussehen: 
image
5.) Abfragen von Thesauri bzw. Synonymen

Im letzten Schritt gilt es nun zu überprüfen, ob die verwandten Begriffe und Synonyme auch wirklich mit Hilfe von SQL-Statements gefunden werden können. Die allgemeine Abfrage-Form für sämtliche folgende SQL-Anweisungen orientieren sich ausschließlich am folgenden Muster [2]:

   1: SELECT * FROM @TabellenOrView -- Tabelle oder View, die abgefragt werden sollen

   2: WHERE CONTAINS(@FTSColumns, -- @FTSColumns: Spalten mit für Volltextsuche 

   3: N'FORMSOF (THESAURUS, ' + @SearchTerm + ')') -- @SearchTerm: Suchbegriff

 
Soll entsprechend des Beispiel-Thesaurus-Wörterbuchs neben “Schreibutensilien” gleichzeitig auch “Büromaterial, beide Begriffe sind laut Konfiguration zu einer Gruppe zusammengefasst, gefunden werden, so kann dies durch folgendes Abfragestatement realisiert werden:  
   1: SELECT * FROM TblProduct

   2: WHERE CONTAINS(ProductDescription, N'FORMSOF (THESAURUS, Schreibutensilien)');    

Sämtliche Synonyme der ersten Gruppe werden durch folgendes Abfrageergebnis repräsentiert:
image
Um “Heftgerät”-Synonyme als Abfrageergebnis der Volltextsuche zu erhalten, was der zweiten Synonyme-Gruppe innerhalb der konfigurierten Thesaurus-Datei entspricht, muss folgender SQL-Befehl abgesetzt werden:
   1: SELECT * FROM TblProduct

   2: WHERE CONTAINS(ProductDescription, N'FORMSOF (THESAURUS, Heftgerät)');    

Als Ergebnis werden von der Datenbank folgende Einträge zurückgeliefert:
image

Zusammenfassung und Fazit

Im Rahmen dieses Beitrags wurde gezeigt, wie mit Hilfe von Thesauri innerhalb des SQL-Servers Ontologien erstellt und Begrifflichkeiten unter einem bestimmten Verwendungskontext in einem kontrollierten Vokabular verwaltet werden können. Des Weiteren wurde dargelegt, wie durch relativ einfache SQL-Statements – ähnlich wie bei modernen Suchmaschinen – erfolgreiche Abfragen nach Synonymen oder verwandten Begrifflichkeiten abgesetzt werden können.

Mit entsprechenden Programmierkenntnissen, einer eigenen Webseite sowie etwas Budget für Speicher- und Rechenkapazitäten ist es somit schon möglich, eine individuelle Suchmaschine zu entwickeln. Sie muss sich ja nicht gleich an der erfolgreichen Unternehmensgeschichte von Google orientieren Zwinkerndes Smiley.

Quellen

  1. Konfigurieren und Verwalten von Thesaurusdateien für die Volltextsuche (Letzter Zugriff: 04.11.2013)
  2. CONTAINS (Transact-SQL) (Letzter Zugriff: 04.11.2013)

FTS im MS SQL-Server (2. Teil): Komponenten der FTS

Vielen Entwicklern, die mit SQL-basierten Datenbanken arbeiten, dürfte bekannt sein, dass sich mit Hilfe des LIKE– oder des ‘=’-Operators Zeichenketten auf teilweise oder exakte Übereinstimmung überprüfen lassen. Häufig reichen solche einfachen Textabfragen allerdings nicht mehr aus, um den gewünschten Informationsgewinn zu erzielen.

Suchen in der IT können sich in der heutigen Zeit nicht mehr ausschließlich auf die Überprüfung eindeutiger Begrifflichkeiten oder einzelner Textbausteine beschränken. Spätestens seitdem moderne Suchmaschinen wie Bing oder Google Einzug in das World Wide Web gehalten haben und dort kaum noch wegzudenken sind, hat sich das Suchen und Finden der relevanten Informationseinheiten teilweise sogar zu einem regelrechten Wettkampf entwickelt.

Nachdem in meinem ersten Beitrag “Flexionen-Abfragen mit FTS und MS SQL-Server” zunächst ein paar allgemeine Grundlagen zur Volltextsuche  anhand von Beispielen aus der Linguistik erläutert wurden, sollen in diesem Artikel die wichtigsten Datenbank-Objekte für eine individuelle Volltextsuche innerhalb des Microsoft SQL-Servers vorgestellt werden.

Dabei konzentriert sich der Artikel insbesondere auf folgende Komponenten:

  • Suchkataloge
  • Volltextindizes
  • Stoppwortlisten

Es wird sowohl der Verwendungszweck als auch das Zusammenspiel der einzelnen Komponenten analysiert und erläutert. Des Weiteren wird auch gezeigt, wie die Volltextsuche an bestimmten Stellen konfiguriert werden kann.

Allgemeine Grundlagen

Installationsüberprüfung

Als Erstes gilt es zu überprüfen, ob die Volltextsuche für eine SQL-Server-Datenbank verfügbar ist. Dies kann durch Absetzen des folgendes SQL-Statements einfach und schnell verifiziert werden:

   1: SELECT SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled;

Ist die Volltextsuche erfolgreich installiert, so liefert diese Abfrage folgendes Ergebnis:

image

Sollte die Volltextsuche noch nicht vorhanden sein, muss diese über das SQL-Server Setup installiert werden. Nähere Informationen zur Einrichtung der Volltextsuche können zum Beispiel unter Quelle [1] recherchiert werden.

Daten und Dateitypen

Mit Hilfe der Volltextsuche können nicht nur “einfache” und textbasierte Spalten in Datenbank-Tabellen oder -Views abgefragt werden, sondern auch Spalten mit binären oder XML-basierenden Dateien, die umfangreichen Text-Dokumente enthalten. Damit erweist sich die Volltextsuche des SQL-Server durchaus als geeignet für komplexe Text-Analysen oder gar Text Mining– Verfahren. Die Volltextsuche innerhalb des SQL-Server kann für folgenden Datentypen angewendet werden [2]:

  • CHAR
  • VARCHAR
  • NCHAR
  • NVARCHAR
  • TEXT
  • NTEXT
  • XML
  • VARBINARY

Suchkataloge

Um die Spalten von Tabellen einer Datenbank zu einer Volltextsuche im SQL-Server hinzuzufügen, muss als Erstes ein Suchkatalog (engl.: “Full-Text Catalog”)  angelegt werden. Bei einem solchen Suchkatalog handelt es zunächst nur um ein virtuelles Objekt bzw. ein logisches Konzept mit dem andere Komponenten der Volltextsuche, die so genannten “Volltextindizes” (siehe nachfolgenden Abschnitt), zu einer Gruppe zusammengefasst werden können [2], [3]

Die Erstellung eines Suchkatalogs kann beispielsweise mit folgender T-SQL-Syntax erfolgen [3]:

   1: CREATE FULLTEXT CATALOG catalog_name 

   2:     [ON FILEGROUP filegroup ] 

   3:     [IN PATH 'rootpath'] 

   4:     [WITH <catalog_option>] 

   5:     [AS DEFAULT] 

   6:     [AUTHORIZATION owner_name ] 

   7:     <catalog_option>::= ACCENT_SENSITIVITY = {ON|OFF} 

Hierbei können unterschiedliche Modifikationen für das Verhalten des Suchkatalogs vorgenommen werden. Beispielsweise kann über die Option ACCENT_SENSITIVITY gesteuert werden, ob Akzente (z.B. im Wort “Café”) für Unterscheidungen bei Textabfragen berücksichtigt werden sollen oder nicht.

Alternativ kann ein Suchkatalog auch über Dialogmasken innerhalb des SQL-Server Management Studios konfiguriert werden. Die folgenden beiden Abbildungen stellen dies bildlich dar:

image

image

Nachdem der neue Suchkatalog erstellt wurde, wird er in der Liste der Volltextkataloge aufgeführt und steht der Datenbank – wie die folgende Grafik zeigt – zur Verfügung:

image 

Volltextindizes

Die wichtigste Komponente der Volltextsuche stellen die Volltextindizes dar. Alle Spalten der Datentypen CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT oder NTEXT, welche mit Hilfe der Volltextsuche abgefragt werden sollen, müssen in den Volltextindex der Tabelle aufgenommen werden.

Volltextindizes können, ähnlich wie bei den Suchkatalogen, mit Hilfe von SQL-Statements angelegt und verwaltet werden. Beispielsweise können hierbei bestimmte Spracheinstellungen hinterlegt oder das Änderungsverhalten des Volltextindex bei Änderungen der Spalteninhalte beeinflusst werden. Das Anlegen eines neuen Volltextindex per Programmierung orientiert sich dabei am SQL-Template aus folgendem Code-Ausschnitt [5], [6]:

   1: CREATE FULLTEXT INDEX ON table_name/view_name

   2:    [ ( { column_name 

   3:              [ TYPE COLUMN type_column_name ]

   4:              [ LANGUAGE language_term ] 

   5:              [ STATISTICAL_SEMANTICS ]

   6:         } [ ,...n] 

   7:       ) ]

   8:     KEY INDEX index_name 

   9:     [ ON <catalog_filegroup_option> ]

  10:     [ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]

  11: [;]

  12:  

  13: <catalog_filegroup_option>::=

  14:  {

  15:     fulltext_catalog_name 

  16:  | ( fulltext_catalog_name, FILEGROUP filegroup_name )

  17:  | ( FILEGROUP filegroup_name, fulltext_catalog_name )

  18:  | ( FILEGROUP filegroup_name )

  19:  }

  20:  

  21: <with_option>::=

  22:  {

  23:    CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] } 

  24:  | STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }

  25:  | SEARCH PROPERTY LIST [ = ] property_list_name 

  26:  }

Etwas einfacher und intuitiver dürfte das Anlegen und Verwalten eines Volltextindex über die Benutzeroberfläche des SQL-Server Management Studio sein. Die folgenden Abbildungen zeigen die einzelnen Konfigurationsschritte:  

image

image

image

Sonderfall: Dateistrukturen

Für Spalten, welche komplette Dateien speichern und vom Datentyp XML oder VARBINARY sind, muss eine weitere Voraussetzung erfüllt sein, um in den Volltextindex aufgenommen werden zu können. Neben der Spalte mit dem zu durchsuchenden Inhalt selbst muss im Rahmen der Konfiguration zusätzlich eine Typ-Spalte mit hinterlegt werden. Diese Typ-Spalte gibt an, welches Dateiformat (z.B. Microsoft Word, XML-Datei, PDF-Format, …) der Spalteninhalt besitzt.

Die folgende Abbildung zeigt, wie diese Typ-Spalte, in der die entsprechende Dateiendung steht, ausgewählt werden kann:

image

Des Weiteren kann zur Recherche aller im SQL-Server unterstützen Dateiformate  (z.B. “.doc” für Word-Dateien, “.html” für Web-Formulare, …) folgende View-Abfrage abgesetzt werden [7]

   1: SELECT * FROM sys.fulltext_document_types;

Stoppwortlisten

Suchabfragen in großen Datenmengen und umfangreichen Textdokumenten können viel Rechenzeit und Ressourcen beanspruchen. Auch würde der Volltextindex sehr viel Speicherkapazität benötigen, wenn alle Worte einer Sprache in den Volltextindex aufgenommen werden.

Meistens sind aber gar nicht alle Wörter für die Suche relevant und sollten daher auch nicht(!) in den Volltextindex aufgenommen werden. Worte, die nur geringe Information enthalten und daher als Stoppwörter (englisch: Noise Word) bezeichnet werden, sollen bei einer Volltextsuche am besten ignoriert werden. In der deutschen Sprache zählen hierzu insbesondere 

  • Artikel (z.B. ‚der‘, ‚die‘, ‚das‘, …),
  • Präpositionen (z.B. ‚an‘, ‚ab‘, ‚aus‘, …) oder
  • Konjunktionen (z.B. ‚und‘, ‚oder‘, ‚da‘, ‚weil’, …)

Für die Volltextsuche im SQL-Server können  Stoppwörter in Stoppwortlisten organsiert und verwaltet werden, welche wiederum als Datenbank-Objekte innerhalb der Datenbank abgelegt werden.

Verwaltungsoptionen für Stoppwortlisten und Stoppwörter

Für die Verwaltung der Stopplisten und Stoppwörter in einer Datenbank stehen dem Anwender, analog zu den Suchkatalogen und Volltextindizes, zum einen diverse SQL-Befehle zur Verfügungen. Der folgende Code-Ausschnitt zeigt sie wichtigsten SQL-Statements, mit deren Hilfe Stopplisten angelegt (Zeile 2) oder gelöscht (Zeile 11) werden können. Außerdem wird erläutert, wie einzelne Wörter zu einer Stoppliste hinzugefügt werden (Zeile 5) können und schließlich wie eine Stoppliste zu einem Volltextindex hinzugefügt (Zeile 8) werden kann (in Anlehnung an [8], Seite 95):

   1: -- Anlegen einer Stoppwordliste

   2: CREATE FULLTEXT STOPLIST NameOfStopList FROM SYSTEM STOPLIST;

   3:  

   4: -- Hinzufügen eines Stoppwortes

   5: ALTER FULLTEXT STOPLIST NameOfStopList ADD 'Noise Word' LANGUAGE GERMAN;

   6:  

   7:Stoppliste für Volltextindex einer Tabelle oder eines Views konfigurieren

   8: ALTER FULLTEXT INDEX ON TableOrViewName SET STOPLIST NameOfStopList 

   9:  

  10: -- Löschen einer Stoppwortliste

  11: DROP FULLTEXT STOPLIST NameOfStopList; 

Alternativ können Stoppwortlisten mit ihren darin enthaltenen Stoppwörtern auch wieder mit Hilfe von Dialogen und ein paar Mausklicks innerhalb des SQL-Server Management Studios erstellt und konfiguriert werden. Die nachfolgenden Abbildungen stellen das Anlegen von Stopplisten und das Einfügen von Stoppwörtern in die Stoppwortliste bildlich dar:

image

image
clip_image001

image

Abfragen für Stoppwortlisten und Stoppwörter

Je umfangreicher eine Volltextsuche wird, desto mehr Stoppwörter und auch Stoppwortlisten müssen organsiert und verwaltet werden. Aus diesem Grund bietet es sich an, Stoppwörter zunächst in bestimmten Kategorien zu untergliedern (z.B. Artikel, Präpositionen, Konjunktionen, …) und dann für jede Kategorie eine eigene Stoppwortliste anzulegen.Dies kann je nach Sprache durchaus unterschiedlich, wenn beispielsweise die Anzahl der Artikel in der deutschen und englischen Sprache miteinander verglichen werden.

Damit auch die Anzahl und der Inhalt der einzelnen Stoppwortlisten kontrolliert werden kann, bietet der MS SQL-Server diverse System-Sichten und – Funktionen an, mit denen verwaltete Stoppwörter überprüft und analysiert werden können.

Sollen beispielsweise sämtliche Stopplisten eines Datenbank aufgeführt werden, so kann folgendes SQL-Statement abgesetzt werden (vgl. [8], Seite 96):

   1: -- Alle Stoppwortlisten anzeigen

   2: SELECT * FROM sys.fulltext_stoplists;

Ergebnisliste mit Stoppwortlisten:

Stoppwordlists

Um dagegen alle Stoppworte zu erhalten, unabhängig davon welcher Stoppwortliste diese im Einzelnen zugeordnet sind, bietet sich folgende Abfrage (vgl. [8], Seite 96) an:

   1: -- Alle Stoppworte anzeigen

   2: SELECT * FROM sys.fulltext_stopwords;

Ergebnisliste mit sämtliche Stoppworten:

Stoppwords 

 

Zusammenfassung und Ausblick

In diesem Artikel konnten die wichtigsten Komponenten samt Konfigurationsmöglichkeiten für eine Volltextsuche innerhalb des MS SQL-Servers vorgestellt werden. Des Weiteren konnte gezeigt  werden, dass für eine individuelle Volltextsuche durchaus einige wesentliche Punkte (.z.B. Umfang des Volltextindizes) zu berücksichtigen sind.

Im nächsten Artikel soll auf Thesauris und semantische Suchabfragen eingegangen werden. Dabei wird erläutert, wie in Beziehung stehende Begrifflichkeiten oder Synonyme in SQL-Server-Datenbanken gesucht und gefunden werden können.

 

Quellen:

  1. Volltextsuche (SQL Server) (Letzter Zugriff: 27.10.2013)
  2. CREATE FULLTEXT CATALOG (Transact-SQL) (Letzter Zugriff: 27.10.2013)
  3. Erstellen und Verwalten von Volltextkatalogen (Letzter Zugriff: 27.10.2013)
  4. CREATE FULLTEXT INDEX (Transact-SQL) (Letzter Zugriff: 27.10.2013)
  5. sys.fulltext_document_types (Transact-SQL) (Letzter Zugriff: 27.10.2013)