Design Pattern // Sichere Typ-Konvertierung mit T-SQL — Fehler erkennen, statt den ETL-Prozess abzubrechen

Ein einziger nicht konvertierbarer Wert — ein 25.5 in einer Integer-Spalte, ein leerer String, ein Datum wie 20240230 — und der ETL-Lauf bricht mitten im Import ab. Wer Textdaten aus Vorsystemen lädt, kennt das: Die Lieferung hält sich nicht an die vereinbarte Schnittstelle, und ein nacktes CONVERT wirft eine Exception, statt den fehlerhaften Wert sauber zu protokollieren.

Dieser Artikel beschreibt ein Design Pattern für die sichere Typ-Konvertierung: eine Systematik, die jeden Konvertierungsfehler einzeln identifizierbar macht, ohne den ETL-Prozess abzubrechen. Sie ruht auf drei Paradigmen, die der nächste Abschnitt herleitet.

Das lernst du hier:

• Materialisierung — warum die Zwischenergebnisse persistiert gehören, damit das Ergebnis jederzeit einsehbar bleibt: nach einem Abbruch wie nach erfolgreicher Verarbeitung.
• Fehler-Identifikation — wie eine einzige WHERE-Klausel jeden fehlgeschlagenen Wert findet, statt den Lauf zu killen.
• Datentyp-Feinheiten — warum TRY_CONVERT(int, '') eine 0 liefert und wann das fachlich falsch ist.
• Wiederverwendbare UDFs — fn_try_convert_* mit Empty-String-→-NULL-Handling als Baustein pro Zieltyp.

Voraussetzung: SQL Server / T-SQL und ein ETL-Kontext, in dem Textdaten in typisierte Spalten zu überführen sind. Für die reinen Konvertierungs-Funktionen CAST, CONVERT, TRY_CAST und TRY_CONVERT siehe die Grundlagen der Typ-Konvertierung mit T-SQL — dieser Artikel baut darauf das Pattern auf.

Inhalt

• Die drei Paradigmen
• Eingangs- und Ausgangswerte
• Materialisierung der extrahierten Daten
• Identifikation von Konvertierungs-Fehlern
• Konvertierung in Abhängigkeit vom Ziel-Datentyp
• Wiederverwendbare Konvertierungs-Funktionen
• Kritische Würdigung der Systematik
• FAQ
• Verwandte Artikel

Die drei Paradigmen

Die Systematik ruht auf drei Paradigmen:

1. NULL statt Abbruch. Die Konvertierungsfunktion gibt NULL zurück, wenn der Eingangswert nicht in den Zieldatentyp konvertiert werden kann — kein Laufzeitfehler, kein ETL-Abbruch.
2. Eingangs- und Ausgangswert materialisieren. Der ETL-Prozess speichert in einer Tabelle sowohl den Eingangswert (Text) als auch den konvertierten Ausgangswert.
3. Fehler durch Vergleich identifizieren. Der Vergleich von Eingangs- und Ausgangswert findet jeden fehlgeschlagenen Wert per einfacher WHERE-Klausel.

SQL Server liefert mit TRY_CONVERT das erste Paradigma frei Haus: Die Funktion gibt im Fehlerfall NULL zurück, statt eine Exception zu werfen. Allein ihre Anwendung sichert aber noch keine fachlich korrekte Konvertierung — dafür muss man die Besonderheiten je Zieldatentyp kennen (siehe unten). Bei einigen Datentypen lässt sich TRY_CONVERT zudem gar nicht sinnvoll anwenden: Eine Ja/Nein-Information kommt etwa als Text (J, N, Y, Yes, No, …), und auch Datumswerte brauchen oft Vorverarbeitung. Für diese Fälle schreibt man benutzerdefinierte Funktionen, die das erste Paradigma (NULL bei Fehler) erfüllen — der Abschnitt Wiederverwendbare Konvertierungs-Funktionen zeigt sie.

Eine robuste und sichere Typ-Konvertierung ist insbesondere in Datenmigrationsprojekten wichtig, bei denen die zu verarbeitenden Daten in Form von Dateien (Excel, CSV, XML, JSON, …) geliefert werden.

Eingangs- und Ausgangswerte

Als Eingangswerte werden Daten bezeichnet, die extrahiert und in einer Datenbank in Tabellen und Spalten mit dem Datentyp nvarchar gespeichert wurden. Als Ausgangswerte werden Daten bezeichnet, die aus den Eingangswerten in den Zieldatentypen konvertiert wurden. Zu jedem zu verarbeitenden Eingangswert gibt es auch einen Ausgangswert.

Materialisierung der extrahierten Daten

Eine reine In-Memory-Verarbeitung verleitet dazu, Zwischenergebnisse gar nicht erst zu persistieren: Extraktion, Typ-Konvertierung und Fehler-Identifikation laufen dann in einem einzigen Verarbeitungsfluss im Arbeitsspeicher (SSIS mit seinem Control Flow ist ein bekanntes Beispiel). Können Werte nicht konvertiert werden, ist eine umfangreiche Fehlerbehandlung mitten im Fluss nötig — und enthält ein Datensatz mehrere Fehler, wird oft nur der erste behandelt und protokolliert. Fehlerhafte Datensätze landen allenfalls in einer Textdatei, die sich nie jemand anschaut. So mächtig solche Werkzeuge sind: Eine umfassende Fehlerbehandlung bei der Typ-Konvertierung wird in der Realität selten konsequent umgesetzt.

Besser ist es, die Arbeitsschritte strikt voneinander zu trennen und die Zwischenergebnisse in einer Datenbank zu materialisieren — unabhängig davon, welches Werkzeug die Verarbeitung übernimmt. Der entscheidende Gewinn ist die Persistierung selbst: Das Konvertierungsergebnis bleibt jederzeit einsehbar — nicht nur nach einem Abbruch, sondern auch nach einer erfolgreichen Verarbeitung. Man kann jederzeit hineinschauen, einzelne Fehler nachvollziehen und Ursachen analysieren. Für nichts anderes steht das Akronym ETL: Die Daten werden zunächst in einer Datenbank extrahiert, anschließend robust typ-konvertiert, und im letzten Schritt werden fehlerfreie Daten identifiziert und weiterverarbeitet:

Diese Abbildung zeigt einen ETL-Prozess, in dem für jede durchzuführende Aufgabe ein separates Datenbankschema erzeugt wird:

Die vollständige Herleitung dieser Schema-Schichtung — von der Extraktion (E0/E1) über die Transformation bis zum Laden (L1/L2) — liefert der Artikel Design Pattern // Architektur eines ETL-Prozesses. Dieser Artikel hier vertieft den Schritt der Typ-Konvertierung von Schema E1 nach T1 — konzentrieren wir uns also auf diese beiden.

Schema E1

Extrahierte Daten werden in Tabellen des Schemas E1 in Spalten mit dem Datentyp nvarchar gespeichert. Gegebenenfalls ist die Länge der Textfelder nicht zu beschränken. Die Länge der Text-Felder ist jedenfalls so zu wählen, dass eine vollständige Extraktion aller Daten sichergestellt ist. Die Extraktion der Daten kann dann nur bei Problemen mit der Infrastruktur zu einem Fehler führen. Die extrahierten Daten werden auch als Eingangswerte bezeichnet.

Schema T1

Zu jeder Tabelle aus dem Schema E1 existiert eine gleichnamige Tabelle in dem Schema T1. In den Tabellen des Schemas T1 werden die Spalten der Eingangswerte in dem Datentyp nvarchar übernommen und zusätzlich eine zweite Spalte je Eingangswert eingefügt, dieses Mal aber mit dem Datentyp des Zielsystems. Aus pragmatischen Gründen erhalten diese Spaltenpaare den gleichen Spaltennamen, wobei die Spalten, die den Eingangswert aufnehmen den Suffix _E1 erhalten. Ein Beispiel…

  1: CREATE TABLE [T1].[Table]
  2: (
  3:     [Id]         int IDENTITY(1,1) NOT NULL
  4:    ,[PK_E1]      nvarchar(256)         NULL
  5:    ,[PK]         int                   NULL
  6:    ,[Text_E1]    nvarchar(256)         NULL
  7:    ,[Text]       nvarchar(3)           NULL
  8:    ,[Integer_E1] nvarchar(256)         NULL
  9:    ,[Integer]    int                   NULL
 10:    ,[Date_E1]    nvarchar(256)         NULL
 11:    ,[Date]       datetime              NULL
 12: );

  1: CREATE TABLE [T1].[Table]
  2: (
  3:     [Id]         int IDENTITY(1,1) NOT NULL
  4:    ,[PK_E1]      nvarchar(256)         NULL
  5:    ,[PK]         int                   NULL
  6:    ,[Text_E1]    nvarchar(256)         NULL
  7:    ,[Text]       nvarchar(3)           NULL
  8:    ,[Integer_E1] nvarchar(256)         NULL
  9:    ,[Integer]    int                   NULL
 10:    ,[Date_E1]    nvarchar(256)         NULL
 11:    ,[Date]       datetime              NULL
 12: );

In der Tabelle [T1].[Table] sind alle Spalten bis auf die Spalte [Id] als Nullable deklariert. Damit können sowohl die Eingangswerte aus einer Tabelle [E1].[Table] als auch die konvertierten Ausgangswerte – auch in Anwesenheit von Konvertierungsproblemen – gespeichert werden. Voraussetzung ist jedoch, dass alle Konvertierungsfunktionen im Fehlerfall ein NULL zurückgeben.

Identifikation von Konvertierungs-Fehlern

Schauen wir uns ein Datenbeispiel für die oben genannte Tabelle [T1].[Table] an.

Probleme bei der Konvertierung von Eingangswerten in den Datentyp des Ausgangswertes lassen sich über eine einfache WHERE-Klausel identifizieren. Für Ausgangswerte vom allgemeinen Datentyp Nicht-Text finden die folgenden WHERE-Klauseln die Probleme bei der Typ-Konvertierung — der Eingangswert ist gefüllt, der konvertierte Ausgangswert aber NULL:

  1: WHERE [PK_E1]      IS NOT NULL AND [PK]      IS NULL
  2: WHERE [Integer_E1] IS NOT NULL AND [Integer] IS NULL
  3: WHERE [Date_E1]    IS NOT NULL AND [Date]    IS NULL

  1: WHERE [PK_E1]      IS NOT NULL AND [PK]      IS NULL
  2: WHERE [Integer_E1] IS NOT NULL AND [Integer] IS NULL
  3: WHERE [Date_E1]    IS NOT NULL AND [Date]    IS NULL

Für Ausgangswerte vom allgemeinen Datentyp Text kann auf Ungleichheit abgefragt werden, um zu lange (abgeschnittene) Texte und damit Probleme zu identifizieren:

  1: WHERE [Text_E1] <> [Text]

  1: WHERE [Text_E1] <> [Text]

Warum beide Werte persistiert werden — und was die Alternative kostet. Das Pattern speichert bewusst sowohl den Eingangswert (_E1, Text) als auch den konvertierten Ausgangswert nebeneinander. Rein theoretisch könnte man auf die Ausgangs-Spalte verzichten und nur die Eingangswerte halten — dann werden die Prüfroutinen aber komplexer. Es ist die Frage, wo die Konvertierung stattfindet:

• Konvertierung materialisiert (dieser Ansatz): Das Konvertierungsergebnis wird in die Ausgangs-Spalte geschrieben. Die Prüfung ist danach ein einfacher Spaltenvergleich auf dem Eingangs-/Ausgangs-Paar — und man sieht direkt in der Zeile, in welcher konkreten Spalte der Fehler steckt (im Beispiel oben: Zeile 1 bei Integer, Zeile 2 bei Date, Zeile 3 bei PK).
• Konvertierung nur in der Prüfung: Die Prüfroutine wendet die eigentliche Konvertierungs- bzw. Validierungslogik zur Laufzeit direkt auf die Eingangswerte an und protokolliert oder zählt die Fehler. Das funktioniert — aber der Tabelle sieht man nicht mehr an, wo der Fehler liegt: Man erfährt, wie viele Fehler ein Datensatz enthält, aber nicht durch bloßes Ansehen der Zeile, in welcher Spalte. Genau diese fehlende Sichtbarkeit hat in der Praxis schon für Irritationen gesorgt. Diesen regelbasierten Weg — Prüfregeln generisch über dynamisches SQL anwenden — beschreibt der Artikel Datenqualität mit SQL prüfen ausführlich.

Konvertierung in Abhängigkeit vom Ziel-Datentyp

SQL Server stellt Funktionen für die Konvertierung von Daten in einen Zieldatentyp zur Verfügung. Wendet man sie an, ohne genau zu untersuchen, wie sie arbeiten, erlebt man Überraschungen. Bei genauerer Betrachtung zeigt sich etwa, dass das Ergebnis der Konvertierung einer leeren Zeichenfolge die Zahl 0 ist:

  1: SELECT TRY_CONVERT(int, N'')   -- 0
  2: SELECT TRY_CONVERT(int, N' ')  -- 0

  1: SELECT TRY_CONVERT(int, N'')   -- 0
  2: SELECT TRY_CONVERT(int, N' ')  -- 0

Das kann fachlich korrekt sein. Aus Sicht des Datenbankentwicklers wird jedoch kein Wert geliefert — der Wert ist unbekannt, und demzufolge wäre NULL das korrekte Ergebnis der Konvertierung. Solche Feinheiten gibt es einige, die bei einer gesicherten Typ-Konvertierung zu berücksichtigen sind.

In den folgenden verlinkten Artikeln wird je Datentyp hergeleitet, wie ein Eingangswert gesichert und fachlich korrekt in die Datentypen der Ausgangswerte zu konvertieren sind. Die sichere Typ-Konvertierung wird für die folgenden Datentypen hergeleitet:

In den folgenden Artikeln dieser Serie wird die sichere Typ-Konvertierung in Abhängigkeit vom Datentyp des Ausgangswertes hergeleitet.

Wiederverwendbare Konvertierungs-Funktionen

Das erste Paradigma — NULL statt Exception bei einem nicht konvertierbaren Wert — wiederholt sich pro Zieltyp. Statt es in jedem SELECT auszuformulieren, kapselt man es in eine benutzerdefinierte Funktion fn_try_convert_<typ>. Sie übernimmt zwei Aufgaben, die TRY_CONVERT allein nicht erledigt: Leerstrings und reine Whitespace-Eingaben auf NULL abbilden (statt der 0-Falle von oben) und — bei Fließkommazahlen — die deutsche Dezimalschreibweise normalisieren.

Für die ganzzahligen Typen sieht der Integer-Vertreter so aus (die Schwestern fn_try_convert_bigint, fn_try_convert_smallint und fn_try_convert_tinyint unterscheiden sich nur im Ziel-Typ):

  1: CREATE FUNCTION [dbo].[fn_try_convert_int] (@p_input AS nvarchar(256))
  2: RETURNS int
  3: AS
  4: BEGIN
  5:    DECLARE @normalized AS nvarchar(256);
  6: 
  7:    SET @normalized = LTRIM(RTRIM(@p_input));
  8: 
  9:    -- Leerstring/Whitespace ist ein unbekannter Wert, keine 0
 10:    IF @normalized = N'' RETURN NULL;
 11: 
 12:    RETURN TRY_CONVERT(int, @normalized);
 13: END;

  1: CREATE FUNCTION [dbo].[fn_try_convert_int] (@p_input AS nvarchar(256))
  2: RETURNS int
  3: AS
  4: BEGIN
  5:    DECLARE @normalized AS nvarchar(256);
  6: 
  7:    SET @normalized = LTRIM(RTRIM(@p_input));
  8: 
  9:    -- Leerstring/Whitespace ist ein unbekannter Wert, keine 0
 10:    IF @normalized = N'' RETURN NULL;
 11: 
 12:    RETURN TRY_CONVERT(int, @normalized);
 13: END;

Für die Fließkomma-Typen kommt die Komma-zu-Punkt-Normalisierung hinzu, damit eine deutsche Notation wie 25,5 korrekt konvertiert (fn_try_convert_real ist identisch bis auf den Ziel-Typ):

  1: CREATE FUNCTION [dbo].[fn_try_convert_float] (@p_input AS nvarchar(256))
  2: RETURNS float
  3: AS
  4: BEGIN
  5:    DECLARE @normalized AS nvarchar(256);
  6: 
  7:    -- deutsche Dezimal-Notation: Komma zu Punkt
  8:    SET @normalized = REPLACE(LTRIM(RTRIM(@p_input)), N',', N'.');
  9: 
 10:    IF @normalized = N'' RETURN NULL;
 11: 
 12:    RETURN TRY_CONVERT(float, @normalized);
 13: END;

  1: CREATE FUNCTION [dbo].[fn_try_convert_float] (@p_input AS nvarchar(256))
  2: RETURNS float
  3: AS
  4: BEGIN
  5:    DECLARE @normalized AS nvarchar(256);
  6: 
  7:    -- deutsche Dezimal-Notation: Komma zu Punkt
  8:    SET @normalized = REPLACE(LTRIM(RTRIM(@p_input)), N',', N'.');
  9: 
 10:    IF @normalized = N'' RETURN NULL;
 11: 
 12:    RETURN TRY_CONVERT(float, @normalized);
 13: END;

Mit diesen Funktionen wird aus der Konvertierung im Schema T1 ein einfacher, abbruchsicherer Ausdruck — [dbo].[fn_try_convert_int]([Integer_E1]) liefert den typisierten Wert oder NULL, nie eine Exception. Die datentyp-spezifischen Feinheiten (Wertebereiche, Rundung bei decimal, J/N-Mapping bei bit, Datumsformate) leiten die verlinkten Artikel dieser Serie je Typ her.

Kritische Würdigung der Systematik

Dieser Artikel hat die grundlegende Systematik einer sicheren Typ-Konvertierung aufgezeigt. Ihre Implementierung in einem ETL-Prozess erscheint auf den ersten Blick aufwändig: SELECT-Statements, die Daten aus den Tabellen des Schemas E1 lesen und typisiert in den Tabellen des Schemas T1 speichern, können bei Tabellen mit vielen Spalten komplex werden.

Es bietet sich daher an, diese Aufgabe einmalig über eine generische, metadaten-getriebene Prozedur zu lösen: eine Prozedur, die aus den Tabellenstrukturen des Schemas T1 dynamisch das Konvertierungs-SELECT erzeugt, reduziert den Entwicklungsaufwand pro Tabelle im Wesentlichen auf eine Zeile Code. Genau dieses Muster — konfigurierbares Bad-Data-Aufspüren über dynamisches SQL — beschreibt der Artikel Datenqualität mit SQL prüfen.

Damit ordnet sich dieser Artikel klar ein: Er ist die praktische Regelrichtlinie für die Konvertierungs-Fehlerprüfung. Den architektonischen Rahmen — die Schema-Schichtung E0–L2 — liefert die Architektur eines ETL-Prozesses; die Verallgemeinerung auf beliebige Datenqualitäts-Regeln das eben genannte Framework. Dieser Artikel deckt das Stück dazwischen ab: wie sich Fehler beim Schritt der Typ-Konvertierung konkret aufspüren lassen.

FAQ

Verwandte Artikel

Grundlagen:

• Datenqualität // Grundlagen der Typ-Konvertierung mit T-SQL — CAST, CONVERT, TRY_CAST und TRY_CONVERT im Vergleich.

Sichere Konvertierung je Datentyp:

• TRY_CONVERT für bigint, int, smallint und tinyint
• TRY_CONVERT für decimal und numeric
• TRY_CONVERT für money und smallmoney
• TRY_CONVERT für float und real
• TRY_CONVERT für date, datetime, datetime2 und time
• TRY_CONVERT für bit — Ja/Nein-Werte konvertieren

Im ETL-, Datenqualitäts- und Migrations-Kontext:

• Architektur eines ETL-Prozesses — die Schema-Schichtung E0–L2, in die dieses Pattern den E1→T1-Konvertierungsschritt einordnet.
• Datenqualität in einem ETL-Prozess — der übergeordnete Blick auf Datenqualität im Prozess.
• Datenqualität mit SQL prüfen — generisches Bad-Data-Aufspüren über dynamisches SQL.
• Datenmigration: SQL Server nach PostgreSQL — der Kontext, in dem sichere Typ-Konvertierung besonders zählt.