SQL Rätsel (MS SQL Server 2005)

[Matrix316]

Aaaalso, eine Fragen an die wirklichen Experten.

Nehmen wir mal an, ich habe eine Tabelle tabellex

ID
1
2
3
4
5
6
7

Warum kriege ich folgende Ergebnisse und warum:

select min(id) from (select top 3 id from tabellex ) x
Ergebnis 1 -> Ist eigentlich logisch und erwartet.

select min(id),max(id) from (select top 3 id from tabellex ) x
Ergebnis 1,3 -> Ist eigentlich auch logisch und erwartet.

select max(id) from (select top 3 id from tabellex ) x
Ergebnis 7 -> Kann eigentlich nicht sein, oder? Warum nicht 3?

PS.: select top 3 id from tabellex
Ergebnis
1
2
3

[Ectoplasma]

Eine Tabelle ist eine Menge und hat keine! Ordnung. Die Reihenfolge ist also zufällig. Deine Ergebnisse sind alle zufällig. Deine Statements funktionieren nur dann, wenn du eine "order by id" Klausel anfügst. Wenn du auf die ID-Spalte einen Index legst, hast du je nach Index-Typ, meist eine impliziete Sortierung über den Index. Aber darauf würde ich mich nicht verlassen.

[Exxtreme]

Eine Tabelle ist eine Menge und hat keine! Ordnung. Die Reihenfolge ist also zufällig. Deine Ergebnisse sind alle zufällig. Deine Statements funktionieren nur dann, wenn du eine "order by id" Klausel anfügst. Wenn du auf die ID-Spalte einen Index legst, hast du je nach Index-Typ, meist eine impliziete Sortierung über den Index. Aber darauf würde ich mich nicht verlassen.
So und nicht anders! :)

Ohne eine explizite Sortierung mit der order by-Klausel ist die Reihenfolge nicht definiert.

[Matrix316]

Ja aber die äußere Abfrage weiß doch garnicht welche Tabelle hinter X steckt. Und die Menge der Abfrage für X ist eigentlich immer die gleiche (1,2,3), zumindest wird die immer angezeigt, wenn ich das innere select ausführe.

[Ectoplasma]

Nochmal, das Ergebnis ist rein zufällig richtig. Wenn es einmal 1, 2, 3 lautet, dann wird es wahrscheinlich auch noch beim nächsten Mal 1, 2, 3 lauten. Aber mach einfach mal ein paar deletes und inserts auf dieser Tabelle. Dann kann das Ergebnis durchaus anders sein. In SQL sind alle Tabellen eine Menge und Mengen haben keine Ordnung. Das muss man verinnerlicht haben.

[Exxtreme]

Ja aber die äußere Abfrage weiß doch garnicht welche Tabelle hinter X steckt. Und die Menge der Abfrage für X ist eigentlich immer die gleiche (1,2,3), zumindest wird die immer angezeigt, wenn ich das innere select ausführe.
Das was SELECT TOP zurückgibt ist nicht definiert. SELECT TOP definiert nur die Anzahl der Datensätze und nicht deren Reihenfolge. Ohne ORDER BY kommt halt irgendwas raus.

Und warum SELECT TOP 3 mal 1,2,3 rausgibt und mal nicht kann am Optimizer liegen. In deiner verschachtelten Abfrage nimmt er einen anderen Ausführungspfad und schon kommt was anderes raus solange du die Reihenfolge nicht mit ORDER BY definierst.

[Matrix316]

Das was SELECT TOP zurückgibt ist nicht definiert. SELECT TOP definiert nur die Anzahl der Datensätze und nicht deren Reihenfolge. Ohne ORDER BY kommt halt irgendwas raus.

Und warum SELECT TOP 3 mal 1,2,3 rausgibt und mal nicht kann am Optimizer liegen. In deiner verschachtelten Abfrage nimmt er einen anderen Ausführungspfad und schon kommt was anderes raus solange du die Reihenfolge nicht mit ORDER BY definierst.
Das komische ist ja, dass select top 3 immer 1,2,3 ergibt. und select min(id),max(id) von x gibt auch immer das gleiche. Nur max ist was anderes als man erwarten würde.

[samm]

Das komische ist ja, dass select top 3 immer 1,2,3 ergibt. und select min(id),max(id) von x gibt auch immer das gleiche. Nur max ist was anderes als man erwarten würde.Dann erwartest du etwas Falsches: Ohne Ordnung kannst du gar nichts erwarten, das Ergebnis 7 ist für die dritte Abfrage ebenso korrekt wie 3, 4 ,5 oder 6.

Dass die ersten Queries "immer" die gesagten Zahlen zurückliefern, kann an irgendwelchen physischen Ordnungen auf der Platte oder vorhergegangenen Abfragen liegen, sodass halt grad diese Zahlen erwischt werden.

[PatkIllA]

Das komische ist ja, dass select top 3 immer 1,2,3 ergibt. und select min(id),max(id) von x gibt auch immer das gleiche. Nur max ist was anderes als man erwarten würde.
Du darfst das nicht einzeln sehen. Man kann sich im Management Studio den Execution Plan angucken, der bei der Abfrage genutzt wird. Und auch der kann sich bei der gleichen Abfrage auf den gleichen Daten ändern.

[Ectoplasma]

Das hier:

select top 3 id from x

und das hier:

select max(id) from (select top 3 id from x)

führt zu anders aufgebauten Ausführungsplänen. Wie die anderen ja auch bereits geschrieben haben.
Konsistent ist nur dieses Statement:

select top 3 id from x order by id bzw.
select max(id) from (select top 3 id from x order by id)

[xxxgamerxxx]

Hallo zusammen,

hier wurde ja schon mehrfach richtig drauf hingewiesen, dass die Sortierung bei TOP essentiell ist.

Nun habe ich mal das ganze trotzdem getestet und Matrix Ergebnis ist sogar logisch.

Ich habe folgende Abfragen:

select min(id) from (select top 3 id from table)
select max(id) from (select top 3 id from table)

Das liefert mir im Management Studio dieselben grafischen Ausführungspläne mit denselben IO Statistiken!

Nun habe ich mal den SQL Server Profiler attached und dort das Ereignis "Showplan Text Unencoded". Oder im Management Studio einfach:
set showplan_text on;

Im grafischen Plan steht beim Table Scan nur Sortiert = True. Aber im Text Plan steht auch die Richtung:

Clustered Index Scan(Object:..., ORDERED FORWARD)
Clustered Index Scan(Object:..., ORDERED BACKWARD)

D.h. bei der min() Aggregat Function macht er OHNE Sortierung einen Index Scan von vorne nach hinten, weil er vermutlich davon ausgeht, dass die kleinste Id am Anfang stehen muss, wenn die Spalte ein (non)clusteren Index ist und nimmt da gleich die ersten drei Ergebnisse. Und bei max() macht er genau das Gegenteil, weil da der höchste Wert am Ende stehen muss. Also nimmt er da die letzten drei.

Wenn die Id kein (non)clusteren Index ist, sieht das viel leicht wieder anders aus.

[samm]

Wenn die Id kein (non)clusteren Index ist, sieht das viel leicht wieder anders aus.Ja, sieht je nach Index-Art anders aus, clustered sind physisch geordnet. Trotzdem müssen Queries darauf nicht zwingend die vom TS intendierten oder die von ihm beobachteten Daten zurückgeben, weil das rein eine Sache des Optimierers des DBMS ist, dass das wie im von dir geposteten Ausführplan gemacht wird. Was ich sagen will: es ist nicht durch die Query vorgegebenes Verhalten.

[xxxgamerxxx]

Ne, durch die Query ist das natürlich nicht vorgegeben/spezifiziert. Wollte nur mal zeigen, was der Optimierer hier in dem einfachen Beispiel macht. Hier in dem Bsp. ist es halt logisch und wahrscheinlich sogar auch deterministisch für bestimmte SQL Server Versionen und Abfragemuster (wenn auch nicht offiziell dokumentiert). Aber natürlich ist es nicht deterministisch für die SQL Sprache selbst.

[samm]

Wollte nur mal zeigen, was der Optimierer hier in dem einfachen Beispiel macht. Hier in dem Bsp. ist es halt logisch und wahrscheinlich sogar auch deterministisch für bestimmte SQL Server Versionen und Abfragemuster (wenn auch nicht offiziell dokumentiert).Ah, ja, solche Blicke in die inneren Abläufe sind immer mal spannend :)