Kann ich mich auf die Reihenfolge der Ausgabe verlassen, wenn ich row_number() verwende?

Question

Ich glaube die Antwort ist nein. Und suche nach einem Gegenbeispiel, um zu zeigen, dass die Reihenfolge der Ausgabe nicht garantiert ist, wenn keine ORDER BY-Klausel vorhanden ist.

Betrachten Sie:

create table #order (orderId int primary key clustered
    , customerId int not null -- references customer(customerId)
    , orderDateTIme datetime not null)

insert into #order values (1, 100, '2009-01-01')
insert into #order values (2, 101, '2009-01-02')
insert into #order values (3, 102, '2009-01-03')
insert into #order values (4, 103, '2009-01-04')
insert into #order values (5, 100, '2009-01-05')
insert into #order values (6, 101, '2009-01-06')
insert into #order values (7, 101, '2009-01-07')
insert into #order values (8, 103, '2009-01-08')
insert into #order values (9, 105, '2009-01-09')
insert into #order values (10, 100, '2009-01-10')
insert into #order values (11, 101, '2009-01-11')
insert into #order values (12, 102, '2009-01-12')
insert into #order values (13, 103, '2009-01-13')
insert into #order values (14, 100, '2009-01-14')
insert into #order values (15, 100, '2009-01-15')
insert into #order values (16, 101, '2009-01-16')
insert into #order values (17, 102, '2009-01-17')
insert into #order values (18, 101, '2009-01-18')
insert into #order values (19, 100, '2009-01-19')
insert into #order values (20, 101, '2009-01-20')

select * from #order
-- Ergebnisse in PK-Reihenfolge aufgrund des gruppierten Primärschlüssels

select orderId, CustomerId, orderDateTime
    , row_number() over (partition by customerId order by orderDateTime) RN
from #order

Auf MS SQL Server 2005 hat die Ausgabeordnung zwei Eigenschaften:

1. Die Reihen für jeden customerId sind im Ergebnis zusammenhängend.

2. Row_number() ist fortlaufend innerhalb jedes customerId.

Mein Verständnis ist, dass diese beiden Eigenschaften nicht garantiert sind, wenn keine explizite ORDER BY-Klausel vorhanden ist. Ich suche nach einem Beispiel, in dem diese Eigenschaften nicht zutreffen, das nicht durch eine ORDER BY-Klausel erzwungen wird, sondern einfach ein Ergebnis davon ist, wie MS SQL Server zufällig arbeitet. Entwickeln Sie bei Bedarf gerne Ihre eigene Tabellendefinition, Indizes, usw. in Ihrem Beispiel.

Oder falls ich falsch liege, dürfen Sie mir einen Verweis auf eine Quelle geben, die zeigt, dass diese Reihenfolgen garantiert sind, auch ohne eine explizite ORDER BY-Klausel.

Answer 1

Wenn Sie ein geordnetes Ergebnisset möchten, fügen Sie Ihrer SELECT-Anweisung eine ORDER BY-Klausel hinzu. Punkt. Alles andere ist situativ und kann je nach der aktuellen SQL-Build, die Sie testen, der Laune des Optimierers an diesem Tag und der Phase des Mars-Transits in den Fischen funktionieren oder auch nicht.

Ein triviales Beispiel, das Ihre Annahme widerspricht:

select orderId, CustomerId, orderDateTime
    , row_number() over (partition by customerId order by orderDateTime) RN
    , row_number() over (partition by orderDateTime order by customerId) AntiRN
from #order

Answer 2

Ich habe Schwierigkeiten, die Relevanz hier zu finden; wenn Sie eine explizite Sortierung wünschen, wäre der empfohlene Weg, eine ORDER BY-Klausel in der Abfrage zu verwenden.

Ich würde niemals auf die Standard-Sortierung einer Tabelle vertrauen, wenn ich eine Abfrage erstelle, auf die die Reihenfolge der Ergebnisse angewiesen ist. Jedes moderne RDBMS wird in der Lage sein, eine Order by auf der Grundlage von Indizes und Ähnlichem zu optimieren, daher ist es nichts, worüber man sich Gedanken machen müsste.

In Bezug auf row_number, obwohl es eine Nebenwirkung ist, dass wenn keine ORDER BY-Klausel existiert, die Ausgabe nach dem ROW_NUMBER-Wert sortiert ist, können Sie sich nicht auf dieses Verhalten verlassen, da es nicht garantiert ist.

Noch einmal, der einzige Weg, die Reihenfolge der Ausgabe zu garantieren, ist mit einer ORDER BY-Klausel.

Answer 3

Wie erwähnt, können Sie sich ohne ORDER BY nicht auf die Reihenfolge der Zeilen verlassen.

Sie können sich jedoch auf den ROW_NUMBER() -Wert der Funktion value verlassen.

SELECT
    principal_id, name, 
    ROW_NUMBER() OVER (ORDER BY principal_id DESC) AS DemoRank
FROM
    msdb.sys.database_principals
ORDER BY
    name

Wenn Sie die Daten in Ihrem Client nach DemoRank verarbeiten, sind Sie auch ohne die ORDER BY-Klausel in Ordnung

Wenn Sie sich auf die Anordnung des Recordsets (ordinaler Index) verlassen, dann nicht.

Das obige Beispiel gibt die erste Zeile (Index = 0) als '##MS_PolicyEventProcessingLogin##' aus, aber durch Verwendung des DemoRank-Werts erhalten Sie "db_denydatawriter"

Im Grunde genommen verwenden Sie ORDER BY.

Answer 4

Wenn Sie es geordnet haben möchten, müssen Sie ORDER BY verwenden.

schaue dir einfach den Ausführungsplan an mit

SET SHOWPLAN_ALL ON

wenn in der Spalte StmtText kein "order by" vorhanden ist, erhalten Sie die Dinge einfach so, wie sie nach getaner Arbeit sortiert sind. Manchmal haben Sie Glück, manchmal nicht, wie die Daten gespeichert/geladen/gefiltert/verbunden usw. sind und wie sie zurückgegeben werden.