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