Es war einmal ...
So fangen viele Märchen an und in dieser märchenhaften Geschichte geht es um die Fallstricke beim ORDER BY.

Many thanks to Brad Schulz, for giving the permission to post this translation of his famous SQL Fable.

Das Troll-Rätsel: Ein SQL Märchen

Es war einmal vor langer Zeit, da lebten in einem fernen Land drei T-SQL Programmierer. Frederick Function liebte es einfach T-SQL Funktionen in seinen Abfragen aufzurufen. Er dachte, das würde seinen Code komplizierter aussehen lassen und das bereitete ihm ein gutes Gefühl. Ganz besonders liebte er es mit einem Datum zu arbeiten, weil sein Code dann überall mit jeder Menge DATEADDs und DATEDIFFs durchsetzt sein würde. Osric Ordinal liebte es Abkürzungen in seine Abfragen einzubauen, wann immer er Gelegenheit dazu hatte. Colin Columnname benannte seine Spalten in der SELECT Liste sorgfältig und verwendete diese Spaltennamen auch in der gesamten Abfrage, wo immer es möglich war.

Diese drei jungen Herren versuchten eine schöne Jungfrau zu retten, die im tiefen dunklen Wald als Gefangene gehalten wurde. Die Burg wurde von einem bösen und hässlichen Troll bewacht.
Der Legende nach hatte der Troll drei scheinbar einfache, aber insgeheim schwierige T-SQL Aufgaben…
Wenn irgendeine brave Seele in der Lage wäre, die richtige Antwort auf alle drei T-SQL Aufgaben zu geben, könnte er in die Burg gehen und die Jungfrau erretten. Falls er jedoch nicht erfolgreich bei der Lösung sein sollte, würde er vom Troll aufgefressen werden.
Unsere drei jungen Helden waren mutige und selbstbewusste Männer und so näherten sie sich dem Troll.

“Was wollt ihr?” fragte der Troll die drei Männer, wobei er grünen Schleim beim Sprechen sabberte. “Wir sind gekommen um deine drei T-SQL Herausforderungen zu bestehen und die Jungfrau zu retten.” antwortete jeder der Männer. “Ihr seid Narren!” knurrte der Troll. “Ihr taucht in Gebiete ein, wo bereits hunderte versagt haben.” Keiner der Männer sagte ein Wort… sie blieben standhaft in ihren Absichten. “Hah! Dann sollten wir weitermachen. Ihr kommt alle gerade rechtzeitig… Gerade hatte ich etwas Hunger bekommen. Heh-heh-heh.” Des Trolls Bauch grummelte und seine Augen verengten sich. Ohrenschmalz tropfte aus seinem rechten Ohr. “Holt Eure Laptops raus und macht Euch bereit für die erste Aufgabe!” bellte er. Die Männer waren an Ihren Tastaturen bereit.

Aufgabe Nummer Eins

“Aufgabe Nummer Eins: Schreibt eine Query, welche die Anfangsbuchstaben der Vor- und Nachnamen aus der Contacts-Tabelle in der AdventureWorks Datenbank ausgibt. Benennt diese Spalten FirstInitial und LastInitial. Am Ende sortiert ihr das Ergebnis nach den Initialen der Vor- und Nachnamen. Ihr habt 60 Sekunden Zeit. Los! ”

Für ein paar Sekunden waren die drei mutigen Männer geschockt. Sie konnten nicht glauben, wie einfach diese erste Aufgabe war, aber nach einer kurzen Weile begannen sie wild auf ihren Tastaturen zu tippen. Sie alle schauten auf, lange bevor die 60 Sekunden zu ende waren.

“Lasst uns mal sehen, was ihr da so habt,” sagte der Troll.

Frederick Function demonstrierte seine Query. Er verwendet dieselbe LEFT() Funktion in der ORDER BY Klausel, die er auch in der SELECT Liste verwendet hatte… Er dachte, das sähe wirklich cool aus, wenn er die Funktionen einfach so wiederholen würde:

SELECT FirstInitial=LEFT(FirstName,1)
      ,LastInitial=LEFT(LastName,1)
FROM AdventureWorks.Person.Contact
ORDER BY LEFT(FirstName,1), LEFT(LastName,1);
/* FirstInitial LastInitial
------------ -----------
A             A
A             A
A             A
A             A
...
Z             W
Z             W
Z             Y
Z             Z
(19972 rows)
*/

Osric Ordinals Query profitierte von der Verwendung von Ordinalzahlen im ORDER BY, welche sich auf die Reihenfolge der Spalten in der SELECT Liste bezogen. Er liebte diese Art von Abkürzungen:

SELECT FirstInitial=LEFT(FirstName,1)
      ,LastInitial=LEFT(LastName,1)
FROM AdventureWorks.Person.Contact
ORDER BY 1, 2;
/* FirstInitial LastInitial
------------ -----------
A             A
A             A
A             A
A             A
...
Z             W
Z             W
Z             Y
Z             Z
(19972 rows)
*/

Colin Columnnames Query verwendete die Namen der Spalten, die er in der SELECT Liste eingeführt hatte, auch in der ORDER BY Klausel:

SELECT FirstInitial=LEFT(FirstName,1)
      ,LastInitial=LEFT(LastName,1)
FROM CMU.dbo.Contact
ORDER BY FirstInitial, LastInitial;
/* FirstInitial LastInitial
------------ -----------
A             A
A             A
A             A
A             A
...
Z             W
Z             W
Z             Y
Z             Z
(19972 rows)
*/

“Ihr habt alle die erste Aufgabe erfolgreich gelöst,” knurrte der Troll. “Aber das ist keine große Leistung. Die meisten Menschen schaffen das.”

Der Magen des Trolls rumpelte wieder. Dieses Mal allerdings so laut, dass der Boden bebte. Ein Pickel auf seiner linken Wange platzte spontan und heftig, sein fauliger Inhalt flog weit und verpasste Colin nur um Zentimeter.

Aufgabe Nummer Zwei

“Aufgabe Nummer Zwei: Packt Eure Query aus der ersten Aufgabe in eine Stored Procedure mit dem Namen #GetContactInitials welche einen einzelnen String mit dem Namen @SortOrder als Parameter akzeptiert.
Falls @SortOrder gleich ‘First’ ist, dann sortiert ihr nach dem Initial des Vor- und Nachnamens. Wenn @SortOrder gleich ‘Last’ ist, sortiert ihr nach dem Initial des Nach- und Vornamens.
Es sind keine IF Statements erlaubt… Es darf nur eine einzige Abfrage in der Prozedur geben. Ihr habt 60 Sekunden Zeit. Los! ”

Die Männer begannen zu arbeiten.

Frederick Function wurde sehr schnell fertig und kam mit der folgenden Stored Procedure, die auch ohne Fehler kompiliert wurde. Wieder war er besonders stolz auf seine Prozedur, weil da drin noch mehr Funktionen aufgerufen wurden:

CREATE PROCEDURE #GetContactInitials_F
  @SortOrder VARCHAR(20)
AS
SELECT FirstInitial=LEFT(FirstName,1)
      ,LastInitial=LEFT(LastName,1)
FROM AdventureWorks.Person.Contact
ORDER BY CASE @SortOrder
           WHEN 'First' THEN LEFT(FirstName,1)
           WHEN 'Last' THEN LEFT(LastName,1)
         END
        ,CASE @SortOrder
           WHEN 'First' THEN LEFT(LastName,1)
           WHEN 'Last' THEN LEFT(FirstName,1)
         END;

Osric Ordinal produzierte seine Stored Procedure sehr schnell, wobei er seinen Ansatz mit den Ordinalzahlen wieder im ORDER BY verwendete. Seine Prozedur wurde auch ohne Fehler kompiliert:

CREATE PROCEDURE #GetContactInitials_O
  @SortOrder VARCHAR(20)
AS
SELECT FirstInitial=LEFT(FirstName,1)
      ,LastInitial=LEFT(LastName,1)
FROM AdventureWorks.Person.Contact
ORDER BY CASE @SortOrder
           WHEN 'First' THEN 1
           WHEN 'Last' THEN 2
         END
        ,CASE @SortOrder
           WHEN 'First' THEN 2
           WHEN 'Last' THEN 1
         END;

Colin Columnname hatte erst etwas Schwierigkeiten. Er hatte seine Prozedur wie folgt zusammengebaut, aber sie produzierte Kompilierungsfehler:

CREATE PROCEDURE #GetContactInitials_C
  @SortOrder VARCHAR(20)
AS
SELECT FirstInitial=LEFT(FirstName,1)
      ,LastInitial=LEFT(LastName,1)
FROM AdventureWorks.Person.Contact
ORDER BY CASE @SortOrder
           WHEN 'First' THEN FirstInitial
           WHEN 'Last' THEN LastInitial
         END
        ,CASE @SortOrder
           WHEN 'First' THEN LastInitial
           WHEN 'Last' THEN FirstInitial
         END;

Meldung 207, Ebene 16, Status 1, Prozedur #GetContactInitials_C, Zeile 8
Invalid column name 'FirstInitial'.
Meldung 207, Ebene 16, Status 1, Prozedur #GetContactInitials_C, Zeile 9
Invalid column name 'LastInitial'.
Meldung 207, Ebene 16, Status 1, Prozedur #GetContactInitials_C, Zeile 12
Invalid column name 'LastInitial'.
Meldung 207, Ebene 16, Status 1, Prozedur #GetContactInitials_C, Zeile 13
Invalid column name 'FirstInitial'.

Das war zuerst etwas verwirrend. Warum erhielt er Fehlermeldungen, die besagten, dass ‘FirstInitial’ und ‘LastInitial’ ungültige Spaltennamen wären? Ganz offensichtlich waren sie in der ersten Aufgabe gültig gewesen.
Und das ORDER BY ist eine der letzten Operationen, die in einer Abfrage ausgeführt werden, so dass die Spaltennamen bereits durch die SELECT Liste definiert sind. Es war wirklich verwirrend.

Dann, in einer plötzlichen Erkenntnis, verstand Colin. Weil die ORDER BY Klausel aus CASE Ausdrücken bestand, musste T-SQL diese Ausdrücke für jede einzelne Zeile der Tabelle in der FROM-Klausel auswerten.
In anderen Worten, während des Clustered Index Scan auf der Contacts Tabelle, müsste T-SQL die CASE Ausdrücke berechnen. Und dann, erst nachdem alle Zeilen auf diese Art ausgewertet wurden, könnte am Ende das ORDER BY auf den bereits ausgewerteten CASE-Ausdrücken stattfinden. Auch wenn es richtig ist, dass die eigentliche ORDER BY Sortierung logisch gesehen am Ende der Abfrage stattfindet, müssen doch die Ausdrücke innerhalb der ORDER BY Liste bereits zu Anfang ausgewertet werden.

So hat Colin seine Abfrage schnell umgeschrieben, so dass sie eine derived table (abgeleitete Tabelle) in der FROM Klausel verwendete. Auf diese Weise konnte er die Spaltennamen auch noch im ORDER BY verwenden. Er schaffte es gerade noch das letzte Zeichen von seinem Code zu schreiben, als die 60 Sekunden auch schon vorbei waren:

CREATE PROCEDURE #GetContactInitials_C
  @SortOrder VARCHAR(20)
AS
SELECT FirstInitial
      ,LastInitial
FROM (SELECT FirstInitial=LEFT(FirstName,1)
            ,LastInitial=LEFT(LastName,1)
      FROM AdventureWorks.Person.Contact) InitialValues
ORDER BY CASE @SortOrder
           WHEN 'First' THEN FirstInitial
           WHEN 'Last' THEN LastInitial
         END
        ,CASE @SortOrder
           WHEN 'First' THEN LastInitial
           WHEN 'Last' THEN FirstInitial
         END;

“Jetzt testen wir mal Eure Stored Procedures,” knurrte der Troll.

Frederick Function demonstrierte seine Stored Procedure, und sie produzierte das korrekte Ergebnis:

EXEC #GetContactInitials_F 'First';
/* FirstInitial LastInitial
------------ -----------
A             A
A             A
A             A
A             A
...
Z             W
Z             W
Z             Y
Z             Z
(19972 rows)
*/ EXEC #GetContactInitials_F 'Last';
/* FirstInitial LastInitial
------------ -----------
A             A
A             A
A             A
A             A
...
W             Z
W             Z
W             Z
Z             Z
(19972 rows)
*/

Colin Columnname demonstrierte auch seine Stored Procedure, und auch diese produzierte denselben korrekten Output.

EXEC #GetContactInitials_C 'First';
EXEC #GetContactInitials_C 'Last';

Zum Schluss demonstrierte Osric Ordinal seine Stored Procedure. Aber sie produzierte eine Ausgabe, die nicht erwartet wurde:

EXEC #GetContactInitials_O 'First';
/* FirstInitial LastInitial
------------ -----------
G             A
C             A
K             A
H             A
P             A
...
C             G
I             R
C             H
C             Z
C             H
(19972 rows)
*/ EXEC #GetContactInitials_O 'Last';
/* FirstInitial LastInitial
------------ -----------
G             A
C             A
K             A
H             A
P             A
...
C             G
I             R
C             H
C             Z
C             H
(19972 rows)
*/

“Das verstehe ich nicht,” sagte Osric. “Ich habe meine Original Query in die Stored Procedure eingebaut, und ich habe einen CASE Aus---“

Aber Osric bekam keine Chance seinen Satz zu Ende zu führen, denn sein Kopf war plötzlich verschwunden. Er wurde durch die scharfen Zähne des Trolls pulverisiert. Bevor der Rest von Osrics kopflosem Körper auf dem Boden zusammenfallen konnte, fing der Troll ihn auf und schluckte ihn ebenfalls herunter.

Der Troll gab einen lauten Rülpser von sich. Frederick und Colin versuchten von dem Gestank in seinem Atem nicht in Ohnmacht zu fallen.

Colin schielte rüber auf Osrics Laptop und sah, dass Osrics Query dasselbe CASE Auswertungsproblem hatte, das Colins original Query auch hatte. Osric hatte (inkorrekter Weise) angenommen, dass die Verwendung seiner Ordinalzahlen innerhalb der CASE Ausdrücke honoriert würde. Aber stattdessen wurden sie einfach als simple konstante Integer Zahlen von 1 und 2 ausgewertet, und überhaupt nicht mehr als Referenz auf die Spaltenpositionen. Falls zum Beispiel seine Prozedur mit dem Parameter ‘First’ aufgerufen wurde, hatte jede Zeile dasselbe Ergebnis im CASE Ausdruck. Das erste CASE wurde immer als der konstante Integerwert 1 ausgewertet und der zweite immer zum Integerwert 2. Weil nun also jeder Zeile derselbe Wert für das ORDER BY zugeordnet wurde, kam das Endergebnis auch in der Reihenfolge heraus, in dem die Tabelle gelesen wurde, in der Reihenfolge des Clustered Index.

Aufgabe Nummer Drei

“Aufgabe Nummer Drei: Das ist ähnlich wie Aufgabe Nummer Zwei. Der einzige Unterschied besteht darin, dass die Stored Procedure den Namen #GetDistinctContactInitials bekommt und das Ergebnis sollte die distinkten Werte der Anfangsbuchstaben von Vor- und Nachname liefern. Es gelten die gleichen Regeln bezüglich des @SortOrder Parameters und es darf nur eine einzige Query in der Stored Procedure geben. Ihr habt 60 Sekunden Zeit. Los!”

Colin nahm seine Prozedur aus der Aufgabe Nummer Zwei und benannte sie wie angefordert um. Dann ergänzte er ganz einfach ein DISTINCT bei seiner derived table (und benannte auch der Vollständigkeit halber den Aliasnamen für die derived table um, damit Klarheit herrscht). Sie wurde ohne Fehler kompiliert:

CREATE PROCEDURE #GetDistinctContactInitials_C
  @SortOrder VARCHAR(20)
AS
SELECT FirstInitial
      ,LastInitial
FROM (SELECT
      DISTINCT FirstInitial=LEFT(FirstName,1)
              ,LastInitial=LEFT(LastName,1)
      FROM AdventureWorks.Person.Contact) DistinctValues
ORDER BY CASE @SortOrder
           WHEN 'First'
           THEN FirstInitial
           ELSE LastInitial
         END
        ,CASE @SortOrder
           WHEN 'First'
           THEN LastInitial
           ELSE FirstInitial
         END;

Frederick machte das gleiche. Er benannte seine Prozedur um und ergänzte das DISTINCT bei seiner Query, aber er erhielt den folgenden Fehler, als er versuchte diese zu kompilieren:

CREATE PROCEDURE #GetDistinctContactInitials_F
  @SortOrder VARCHAR(20)
AS
SELECT
DISTINCT FirstInitial=LEFT(FirstName,1)
        ,LastInitial=LEFT(LastName,1)
FROM AdventureWorks.Person.Contact
ORDER BY CASE @SortOrder
           WHEN 'First' THEN LEFT(FirstName,1)
           WHEN 'Last' THEN LEFT(LastName,1)
         END
        ,CASE @SortOrder
           WHEN 'First' THEN LEFT(LastName,1)
           WHEN 'Last' THEN LEFT(FirstName,1)
         END;
Meldung 145, Ebene 15, Status 1, Prozedur #GetDistinctContactInitials_F, Zeile 4 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Was zum Teufel ist das? Alles was Frederick getan hatte, war ein DISTINCT bei seiner Query zu ergänzen. Warum machte ihm das jetzt solche Probleme? Und was bedeutete die Fehlermeldung eigentlich? Die ORDER BY Elemente waren eindeutig in der SELECT Liste, dachte er.

Frederick war nicht so clever wie Colin zu bemerken, dass der Umstand, einen CASE Ausdruck im ORDER BY zu haben, T-SQL dazu bringt, diese Ausdrücke bereits während der Clustered Index Scan Phase auszuwerten.
Das hatte noch funktioniert für die Prozedur von Aufgabe Zwei. Aber durch das hinzufügen von DISTINCT in Aufgabe Drei hat sich alles geändert. Weil das Hinzufügen des DISTINCT Schlüsselwortes das Endergebnis reduziert, darf man im ORDER BY nur Spalten aus dem resultierenden verkleinerten Ergebnis verwenden. Man kann jetzt nicht mehr nach Ausdrücken sortieren, die auf Spalten basieren, die in der Original-Tabelle der FROM-Klausel vorhanden sind.

Frederick wurde zunehmend nervös. Während die letzten wertvollen Sekunden verstrichen, konnte er keinen Weg mehr finden, wie er die Abfrage korrigieren könnte.

“Die Zeit ist um!” grummelte der Troll, “Lasst uns sehen, was ihr habt.” Er schaute auf Fredericks Prozedur und sah, dass sie Kompilierungsfehler hatte.

Bäche von Schweiß sprudelten über Fredericks Gesicht und Körper, wie von Wasserfällen.

“Ahhhh…” sagte der Troll, “Ich danke Dir dafür, dass Du noch diese feine salzige Marinade beisteuerst.” Und ohne ein weiteres Wort verschlang er Frederick und schluckte ihn in einem Stück herunter.

Der Troll wandte sich zu Colin.

“Zeig mir Deine Stored Procedure!” kommandierte der Troll.

Colin machte genau das, und brachte das korrekte Ergebnis:

EXEC #GetDistinctContactInitials_C 'First';
/* FirstInitial LastInitial
------------ -----------
A             A
A             B
A             C
A             D
A             E
...
Z             S
Z             T
Z             W
Z             Y
Z             Z
(544 Rows)
*/ EXEC #GetDistinctContactInitials_C 'Last';


/* FirstInitial LastInitial
------------ -----------
A             A
B             A
C             A
D             A
E             A
...
S             Z
T             Z
V             Z
W             Z
Z             Z
(544 Rows)
*/

“Na endlich! Das wurde aber auch Zeit!” sagte der Troll. “Ich wurde schon ganz krank und müde von dieser ganzen SQL Puzzle Veranstaltung. Glückwunsch… Die Jungfrau gehört Dir. Wir sehen uns… Ich bin mal eben weg, ein Dorf terrorisieren.”
Und er verschwand ohne ein weiteres Wort und stapfte über einen Hügel in der Nähe davon.

Colin raste zur Burg und nahm jeweils drei Stufen auf einmal.

Und da war die Jungfrau. Sie drehte sich zu ihm um, die beiden Enden ihres seidenen Kopftuches flatterten sachte im Wind, der durch das geöffnete Fenster kam.

“Glaube mir” rief er aus, “Du bist absolut wunderschön! Tatsächlich siehst Du in dem weißen Kleid und Kopftuch fast genau so aus wie Yeoman Tonia Barrows in---”

Die Jungfrau unterbrach ihn und vervollständigte seinen Gedanken: “…in „Shore Leave“, der 17. Episode der ersten Staffel von Star Trek.”

Colin, ein ausgewiesener Star Trek Fan, war sprachlos.

“Ich danke Dir!” fuhr die Jungfrau fort, “Das ist freundlich von Dir, dass Du das sagst. Und das war auch eine besonders gute Folge, meinst Du nicht auch? Wie auch immer, Ich muss aber sagen, dass sie nicht mit anderen Episoden aus der ersten Staffel mithalten kann, wie „Devil In The Dark“ oder „The City On The Edge of Forever“, nicht wahr? Aber, Theodore Sturgeon, der das Drehbuch für „Shore Leave“ geschrieben hat, hat dann noch „Amok Time“ geschrieben, was wieder eine klasse Folge der zweiten Staffel war.”

Colin wusste sich nicht zu helfen. “Wow! Was für eine Frau!” rief er aus und nahm sie in seine Arme.

Und sie lebten lange glücklich und zufrieden.

Ende

  01_order_by.sql
  02_order_by.sql
  03_order_by.sql