⚡ 7 SQL-Tricks für MS SQL Server

Deklarativ, mächtig, unerwartet – fortgeschrittene T-SQL Techniken, die jede Datenbank-Arbeit revolutionieren.
inspiriert von Lukas Eders berühmter Vortragsreihe, umgesetzt für Microsoft T-SQL.

🎯 MS SQL Server · T-SQL

Alles ist eine Tabelle, rekursive CTEs, Fensterfunktionen, Lückenanalyse und mehr – diese sieben Techniken zeigen, wie weit man mit reinem deklarativem SQL gehen kann. Fokus liegt auf MS SQL Server 2017+ (kompatibel mit neueren Versionen). Keine imperative Schleifenlogik, dafür elegante, lesbare Abfragen.

01 📋 Alles ist eine Tabelle

Jedes SELECT-Statement liefert eine Ergebnismenge (Tabelle). Auch Konstanten, VALUES-Konstrukt oder Unterabfragen sind Tabellen – das vereinheitlicht die gesamte SQL-Logik.

-- Werte als Tabelle erzeugen (MS SQL Server)
SELECT *
FROM (VALUES (1), (2), (3)) AS t(a);

-- INSERT als "INSERT ... SELECT ..." (Tabellen einfügen)
CREATE TABLE #temp (zahl INT);
INSERT INTO #temp (zahl)
SELECT v.a
FROM (VALUES (10),(20),(30)) v(a);

SELECT * FROM #temp;
DROP TABLE #temp;

🔍 In MS SQL kann jede Abfrage als „Tabelle“ in einer FROM-Klausel verwendet werden. Das macht Unterabfragen, CTEs und VALUES-Konstrukte extrem flexibel.

02 🔄 Datengenerierung mit rekursivem CTE

Mit Common Table Expressions (WITH RECURSIVE – SQL Standard) lassen sich Hierarchien, Sequenzen oder sogar die Mandelbrot-Menge generieren.

-- Zahlen von 1 bis 10 rekursiv erzeugen (MS SQL Server)
WITH Rekursion AS (
  SELECT 1 AS Zahl -- Anker / Seed
  UNION ALL
  SELECT Zahl + 1
  FROM Rekursion
  WHERE Zahl < 10
)
SELECT * FROM Rekursion
OPTION (MAXRECURSION 100);

🧠 Die obere Abfrage ist ein rekursiver CTE – jede Iteration addiert 1. Perfekt für Kalendertabellen, Pfad-Aggregationen oder Zerlegung von Zeichenketten in T-SQL.

03 📊 Laufende Summe & Fensterfunktionen

Mit SUM() OVER() kannst du kumulative Summen, gleitende Durchschnitte oder Rangfolgen ohne Selbst-Joins berechnen – hochperformant.

-- Laufende Summe (running total) pro Abteilung
SELECT 
  Abteilung,
  Mitarbeiter,
  Gehalt,
  SUM(Gehalt) OVER (PARTITION BY Abteilung ORDER BY Mitarbeiter 
            ROWS UNBOUNDED PRECEDING) AS Laufende_Summe
FROM Angestellte;

📈 Fensterfunktionen (OVER-Klausel) sind in MS SQL Server vollständig implementiert. Sie ersetzen teure Korrelationen und ermöglichen komplexe Business Analytics in einem einzigen Durchlauf.

04 📅 Längste, lückenlose Abfolge (Gaps & Islands)

Ermitteln der maximalen Anzahl aufeinanderfolgender Tage / IDs – z.B. für "Login-Streaks" oder Verfügbarkeitszeiträume.

-- Beispiel: Login-Daten eines Users – längste Serie von aufeinanderfolgenden Tagen
WITH LoginDaten AS (
  SELECT DISTINCT CAST(LoginZeit AS DATE) AS LoginDatum
  FROM BenutzerLogins
  WHERE BenutzerID = 123
),
Gruppiert AS (
  SELECT LoginDatum,
      ROW_NUMBER() OVER (ORDER BY LoginDatum) AS rn
  FROM LoginDaten
),
Gruppen AS (
  SELECT LoginDatum,
      DATEADD(day, -rn, LoginDatum) AS Gruppe
  FROM Gruppiert
)
SELECT 
  MIN(LoginDatum) AS Start,
  MAX(LoginDatum) AS Ende,
  COUNT(*) AS Tage
FROM Gruppen
GROUP BY Gruppe
ORDER BY Tage DESC;

⭐ Der Trick: Zeilennummer abziehen, um zusammenhängende Tage zu gruppieren. Bekannt als "Tabibitosan-Methode". Funktioniert mit jedem Datentyp, der eine natürliche Ordnung hat.

05 📏 Länge von Abfolgen: LEAD() & LAG()

Mit den Fensterfunktionen LEAD und LAG kann man Änderungen eines Werts erkennen und zusammenhängende Blöcke zählen.

-- Gruppieren von aufeinanderfolgenden Zeilen mit gleichem Vorzeichen (positive/negative Beträge)
WITH DatenMitRN AS (
  SELECT ID, Betrag,
      SIGN(Betrag) AS Vorzeichen,
      ROW_NUMBER() OVER (ORDER BY ID) AS rn
  FROM Transaktionen
),
Grenzen AS (
  SELECT *,
      CASE WHEN LAG(Vorzeichen) OVER (ORDER BY ID) != Vorzeichen 
        THEN rn END AS StartBlock,
      CASE WHEN LEAD(Vorzeichen) OVER (ORDER BY ID) != Vorzeichen 
        THEN rn END AS EndBlock
  FROM DatenMitRN
),
Bloecke AS (
  SELECT ID, Betrag, Vorzeichen,
      MAX(StartBlock) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) AS BlockStart,
      MIN(EndBlock) OVER (ORDER BY ID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS BlockEnd
  FROM Grenzen
)
SELECT *, (BlockEnd - BlockStart + 1) AS BlockLaenge
FROM Bloecke;

✍️ LAG prüft den Vorgänger, LEAD den Nachfolger. Zusammen mit IGNORE NULLS (oder geschicktem Coalesce) lassen sich beliebige Folgen von Datensätzen segmentieren – nützlich für Aktienkursbewegungen oder Bestandsänderungen.

06 🧩 Teilsummenproblem (Subset Sum) mit CTE

Klassisches NP-Problem – aber mit rekursivem SQL und dynamischer Programmierung möglich. Für kleinere Datenmengen enorm praktisch.

-- Finde alle möglichen Summen aus einer Liste von Zahlen (2^n Kombinationen)
WITH Items AS (
  SELECT 1 AS ID, 7120 AS Wert UNION ALL
  SELECT 2, 8150 UNION ALL SELECT 3, 8255 UNION ALL SELECT 4, 9051
),
RekSum AS (
  SELECT Wert, CAST(Wert AS VARCHAR(MAX)) AS Pfad, ID
  FROM Items
  UNION ALL
  SELECT r.Wert + i.Wert, r.Pfad + ' + ' + CAST(i.Wert AS VARCHAR), i.ID
  FROM RekSum r
  JOIN Items i ON r.ID < i.ID
)
SELECT Wert, Pfad 
FROM RekSum
ORDER BY Wert;

⚙️ Die Abfrage baut kombinatorische Summen rekursiv auf. In MS SQL Server kann man damit Budget-Analysen oder "beste Passung" für Materialbedarfsplanung durchführen.

07 ✂️ Gekapptes Running Total (nie unter 0)

Laufende Summe, die niemals negativ wird – z.B. für Lagerbestände oder Kontostand mit Untergrenze 0.

-- T-SQL Simulation eines "Running Total with Floor" mittels Fensterfunktion & Korrektur
WITH Trans AS (
  SELECT Datum, Betrag,
      SUM(Betrag) OVER (ORDER BY Datum ROWS UNBOUNDED PRECEDING) AS StandardTotal
  FROM KontoBewegungen
)
SELECT Datum, Betrag,
    CASE 
      WHEN StandardTotal < 0 THEN 0 
      ELSE StandardTotal 
    END AS TotalGekappt
FROM Trans;

-- Bessere Variante für echten "Floor(0)" rekursiv (tatsächliches Abschneiden)
-- (Hier als rekursiver CTE mit IIF LIMIT 0)
WITH Bestand AS (
  SELECT Datum, Betrag, 
      IIF(Betrag < 0, 0, Betrag) AS BestandNeu,
      1 AS Reihenfolge
  FROM KontoBewegungen
  WHERE Reihenfolge = 1

  UNION ALL

  SELECT b2.Datum, b2.Betrag,
      IIF(b.BestandNeu + b2.Betrag < 0, 0, b.BestandNeu + b2.Betrag),
      b.Reihenfolge + 1
  FROM Bestand b
  JOIN KontoBewegungen b2 ON b2.Reihenfolge = b.Reihenfolge + 1
)
SELECT * FROM Bestand
OPTION (MAXRECURSION 0);

📉 Die erste Option zeigt eine einfache Kappung, die zweite rekursive Variante bildet das echte „nicht-negativ“-Verhalten ab (wie in Oracle mit MODEL). Für MS SQL Server kann man Window-Aggregate mit rekursiver Logik mischen, um das exakte Verhalten zu erhalten.

⚡ 7 SQL-Tricks für MS SQL Server

Impressum-Hinweis zur verantwortlichen Stelle