Zeilennummern mit nicht deterministischer Reihenfolge

Die Fensterfunktion ROW_NUMBER bietet zahlreiche praktische Anwendungen, die weit über die offensichtlichen Anforderungen an die Rangfolge hinausgehen. Wenn Sie Zeilennummern berechnen, müssen Sie diese meistens anhand einer bestimmten Reihenfolge berechnen und die gewünschte Bestellspezifikation in der Fensterreihenfolge der Funktion angeben. Es gibt jedoch Fälle, in denen Sie Zeilennummern in keiner bestimmten Reihenfolge berechnen müssen. mit anderen Worten, basierend auf nichtdeterministischer Ordnung. Dies kann sich über das gesamte Abfrageergebnis oder innerhalb von Partitionen erstrecken. Beispiele hierfür sind das Zuweisen eindeutiger Werte zu Ergebniszeilen, das Deduplizieren von Daten und das Zurückgeben einer beliebigen Zeile pro Gruppe.

Beachten Sie, dass die Zuweisung von Zeilennummern basierend auf einer nicht deterministischen Reihenfolge anders ist als die Zuweisung basierend auf einer zufälligen Reihenfolge. Bei ersteren ist es Ihnen egal, in welcher Reihenfolge sie zugewiesen werden und ob wiederholte Ausführungen der Abfrage denselben Zeilen dieselben Zeilennummern zuweisen oder nicht. Bei letzteren erwarten Sie, dass wiederholte Ausführungen ständig ändern, welche Zeilen welchen Zeilennummern zugewiesen werden. In diesem Artikel werden verschiedene Techniken zum Berechnen von Zeilennummern mit nicht deterministischer Reihenfolge erläutert. Die Hoffnung ist, eine Technik zu finden, die sowohl zuverlässig als auch optimal ist.

Besonderer Dank geht an Paul White für den Tipp zum konstanten Falten, für die Laufzeitkonstantechnik und dafür, dass er immer eine gute Informationsquelle ist!

Wenn es auf Ordnung ankommt

Ich beginne mit Fällen, in denen die Reihenfolge der Zeilennummern eine Rolle spielt.

In meinen Beispielen verwende ich eine Tabelle mit dem Namen T1. Verwenden Sie den folgenden Code, um diese Tabelle zu erstellen und mit Beispieldaten zu füllen:

Betrachten Sie die folgende Abfrage (wir nennen sie Abfrage 1):

SELECT id, grp, datacol, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY datacol) AS n FROM dbo.T1;

Hier möchten Sie, dass Zeilennummern innerhalb jeder Gruppe zugewiesen werden, die durch die Spalte grp identifiziert wird, geordnet nach der Spaltendatenrolle. Als ich diese Abfrage auf meinem System ausführte, erhielt ich die folgende Ausgabe:

id grp datacol n--- ---- -------- ---5 A 40 12 A 50 211 A 50 37 B 10 13 B 20 2

Zeilennummern werden hier in einer teilweise deterministischen und teilweise nicht deterministischen Reihenfolge zugewiesen. Damit meine ich, dass Sie die Gewissheit haben, dass innerhalb derselben Partition eine Zeile mit einem höheren Datenpolwert einen höheren Zeilennummernwert erhält. Da Datacol innerhalb der grp-Partition nicht eindeutig ist, ist die Reihenfolge der Zuweisung von Zeilennummern zwischen Zeilen mit denselben grp- und datacol-Werten nicht deterministisch. Dies ist bei den Zeilen mit den ID-Werten 2 und 11 der Fall. Beide haben den GRP-Wert A und den Datacol-Wert 50. Als ich diese Abfrage zum ersten Mal auf meinem System ausführte, erhielt die Zeile mit der ID 2 die Zeilennummer 2 und die Zeile mit der ID 11 hat die Zeilennummer 3. Es ist egal, wie wahrscheinlich dies in der Praxis in SQL Server ist. Wenn ich die Abfrage erneut ausführe, könnte theoretisch der Zeile mit der ID 2 die Zeilennummer 3 und der Zeile mit der ID 11 die Zeilennummer 2 zugewiesen werden.

Wenn Sie Zeilennummern basierend zuweisen müssen In einer vollständig deterministischen Reihenfolge, in der wiederholbare Ergebnisse über die Ausführung der Abfrage hinweg garantiert werden, solange sich die zugrunde liegenden Daten nicht ändern, muss die Kombination der Elemente in den Klauseln zur Fensterpartitionierung und -reihenfolge eindeutig sein. Dies könnte in unserem Fall erreicht werden, indem die Spalten-ID als Tiebreaker zur Fensterordnungsklausel hinzugefügt wird. Die OVER-Klausel wäre dann:

OVER (PARTITION BY grp ORDER BY datacol, id)

Bei der Berechnung von Zeilennummern auf der Grundlage einer aussagekräftigen Ordnungsspezifikation wie in Abfrage 1 muss SQL Server auf jeden Fall die Daten verarbeiten Zeilen, geordnet nach der Kombination von Fensterpartitionierungs- und Ordnungselementen. Dies kann erreicht werden, indem entweder die vorbestellten Daten aus einem Index abgerufen oder die Daten sortiert werden. Derzeit gibt es keinen Index für T1, der die Berechnung von ROW_NUMBER in Abfrage 1 unterstützt. Daher muss SQL Server die Daten sortieren. Dies ist in dem in Abbildung 1 gezeigten Plan für Abfrage 1 zu sehen.

Abbildung 1: Plan für Abfrage 1 ohne unterstützenden Index

Beachten Sie, dass der Plan die Daten aus dem Clustered-Index mit der Eigenschaft Ordered: False durchsucht. Dies bedeutet, dass der Scan die nach dem Indexschlüssel geordneten Zeilen nicht zurückgeben muss. Dies ist der Fall, da der Clustered-Index hier nur verwendet wird, weil er die Abfrage abdeckt und nicht aufgrund seiner Schlüsselreihenfolge. Der Plan wendet dann eine Sortierung an, was zu zusätzlichen Kosten, N Log N-Skalierung und verzögerter Antwortzeit führt. Der Segmentoperator erzeugt ein Flag, das angibt, ob die Zeile die erste in der Partition ist oder nicht. Schließlich weist der Sequenzprojektoperator in jeder Partition Zeilennummern zu, die mit 1 beginnen.

Wenn Sie die Notwendigkeit einer Sortierung vermeiden möchten, können Sie einen Deckungsindex mit einer Schlüsselliste erstellen, die auf den Partitionierungs- und Ordnungselementen basiert, und einer Einschlussliste, die auf den Deckungselementen basiert.Ich stelle mir diesen Index gerne als POC-Index vor (zum Partitionieren, Ordnen und Abdecken). Hier ist die Definition des POC, der unsere Abfrage unterstützt:

CREATE INDEX idx_grp_data_i_id ON dbo.T1(grp, datacol) INCLUDE(id);

Führen Sie Abfrage 1 erneut aus:

SELECT id, grp, datacol, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY datacol) AS n FROM dbo.T1;

Der Plan für diese Ausführung ist in Abbildung 2 dargestellt.

Abbildung 2: Plan für Abfrage 1 mit einem POC-Index

Beachten Sie, dass der Plan diesmal den POC-Index mit einer Ordered: True-Eigenschaft durchsucht. Dies bedeutet, dass der Scan garantiert, dass die Zeilen in der Reihenfolge der Indexschlüssel zurückgegeben werden. Da die Daten wie für die Fensterfunktion erforderlich vorbestellt aus dem Index abgerufen werden, ist keine explizite Sortierung erforderlich. Die Skalierung dieses Plans ist linear und die Antwortzeit ist gut.

Wenn die Reihenfolge keine Rolle spielt

Es wird etwas schwierig, wenn Sie Zeilennummern mit einer völlig nicht deterministischen Zuweisung zuweisen müssen In einem solchen Fall sollten Sie natürlich die Funktion ROW_NUMBER verwenden, ohne eine Fensterordnungsklausel anzugeben. Überprüfen Sie zunächst, ob der SQL-Standard dies zulässt. Hier ist der relevante Teil des Standards, der die Syntaxregeln für Fenster definiert Funktionen:

Beachten Sie, dass in Punkt 6 die Funktionen < ntile function >, Lead- oder Lag-Funktion >, < Rangfunktionstyp > oder ROW_NUMBER und dann Punkt 6a besagt, dass für die Funktionen < ntile function > < Lead- oder Lag-Funktion >, RANK oder DENSE_RANK Die Fensterordnungsklausel muss b Das Geschenk. Es gibt keine explizite Sprache, die angibt, ob ROW_NUMBER eine Fensterreihenfolgeklausel erfordert oder nicht, aber die Erwähnung der Funktion in Punkt 6 und ihre Auslassung in 6a könnte bedeuten, dass die Klausel für diese Funktion optional ist. Es ist ziemlich offensichtlich, warum Funktionen wie RANK und DENSE_RANK eine Fensterreihenfolgeklausel erfordern würden, da diese Funktionen auf die Behandlung von Bindungen spezialisiert sind und Bindungen nur dann vorhanden sind, wenn eine Bestellspezifikation vorliegt. Sie können jedoch durchaus sehen, wie die Funktion ROW_NUMBER von einer optionalen Fensterreihenfolge-Klausel profitieren kann.

Probieren Sie es also aus und versuchen Sie, Zeilennummern ohne Fensterreihenfolge in SQL Server zu berechnen:

SELECT id, grp, datacol, ROW_NUMBER() OVER() AS n FROM dbo.T1;

Dieser Versuch führt zu folgendem Fehler:

Wenn Sie die SQL Server-Dokumentation der ROW_NUMBER-Funktion überprüfen, finden Sie den folgenden Text:

Offensichtlich ist die Fensterreihenfolgeklausel für die ROW_NUMBER-Funktion in SQL Server obligatorisch Das ist übrigens auch bei Oracle der Fall.

Ich muss sagen, dass ich nicht sicher bin, ob ich die Gründe verstehe ng hinter dieser Anforderung. Denken Sie daran, dass Sie Zeilennummern basierend auf einer teilweise nicht deterministischen Reihenfolge definieren können, wie in Abfrage 1. Warum also nicht den gesamten Nichtdeterminismus zulassen? Vielleicht gibt es einen Grund, über den ich nicht nachdenke. Wenn Ihnen ein solcher Grund einfällt, teilen Sie ihn bitte mit.

Auf jeden Fall könnten Sie argumentieren, dass Sie, wenn Sie sich nicht für die Bestellung interessieren, da die Klausel für die Fensterbestellung obligatorisch ist, einen beliebigen angeben können bestellen. Das Problem bei diesem Ansatz besteht darin, dass bei einer Bestellung nach einer Spalte aus den abgefragten Tabellen eine unnötige Leistungseinbußen auftreten können. Wenn kein unterstützender Index vorhanden ist, zahlen Sie für die explizite Sortierung. Wenn ein unterstützender Index vorhanden ist, beschränken Sie die Speicher-Engine auf eine Indexreihenfolge-Scan-Strategie (gemäß der Index-verknüpften Liste). Sie lassen keine größere Flexibilität zu, als dies normalerweise der Fall ist, wenn die Reihenfolge bei der Auswahl zwischen einem Indexreihenfolge-Scan und einem Zuordnungsreihenfolge-Scan (basierend auf IAM-Seiten) keine Rolle spielt.

Eine Idee, die es wert ist, ausprobiert zu werden ist es, eine Konstante wie 1 in der Fensterreihenfolgeklausel anzugeben. Wenn dies unterstützt wird, hoffen Sie, dass das Optimierungsprogramm intelligent genug ist, um zu erkennen, dass alle Zeilen den gleichen Wert haben. Daher besteht keine echte Relevanz für die Reihenfolge und daher ist es nicht erforderlich, eine Sortierung oder einen Indexreihenfolge-Scan zu erzwingen. Hier ist eine Abfrage, die diesen Ansatz versucht:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1) AS n FROM dbo.T1;

Leider unterstützt SQL Server diese Lösung nicht. Der folgende Fehler wird generiert:

Anscheinend geht SQL Server davon aus, dass eine Ganzzahlkonstante in der Fensterreihenfolgeklausel eine Ordnungsposition eines Elements in der SELECT-Liste darstellt, beispielsweise wenn Sie eine Ganzzahl in der Präsentationsreihenfolge angeben BY-Klausel. Wenn dies der Fall ist, sollten Sie auch versuchen, eine nicht ganzzahlige Konstante anzugeben, z. B.:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY "No Order") AS n FROM dbo.T1;

Es stellt sich heraus, dass diese Lösung ebenfalls nicht unterstützt wird. SQL Server generiert den folgenden Fehler:

Anscheinend unterstützt die Fensterreihenfolgeklausel keine Konstante.

Bisher haben wir Folgendes über die Relevanz der Fensterreihenfolge der ROW_NUMBER-Funktion in SQL Server erfahren:

1. ORDER BY ist erforderlich.
2. Kann nicht nach einer Ganzzahlkonstante sortieren, da SQL Server denkt, dass Sie versuchen, eine Ordnungsposition in SELECT anzugeben.
3. Kann nicht nach sortieren jede Art von Konstante.

Die Schlussfolgerung ist, dass Sie nach Ausdrücken ordnen sollen, die keine Konstanten sind. Natürlich können Sie nach einer Spaltenliste aus den abgefragten Tabellen sortieren. Wir sind jedoch auf der Suche nach einer effizienten Lösung, bei der der Optimierer erkennen kann, dass keine Ordnungsrelevanz besteht.

Konstante Faltung

Die Schlussfolgerung lautet bisher, dass Sie keine Konstanten in verwenden können das Die Reihenfolge der Fensterreihenfolge von ROW_NUMBER, aber was ist mit Ausdrücken, die auf Konstanten basieren, wie in der folgenden Abfrage:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1+0) AS n FROM dbo.T1;

Dieser Versuch fällt jedoch einem als Konstante bekannten Prozess zum Opfer Falten, was sich normalerweise positiv auf die Leistung von Abfragen auswirkt. Die Idee hinter dieser Technik besteht darin, die Abfrageleistung zu verbessern, indem ein Ausdruck basierend auf Konstanten in einem frühen Stadium der Abfrageverarbeitung auf ihre Ergebniskonstanten gefaltet wird. Details dazu, welche Arten von Ausdrücken konstant gefaltet werden können, finden Sie hier. Unser Ausdruck 1 + 0 wird auf 1 gefaltet, was zu demselben Fehler führt, den Sie bei der direkten Angabe der Konstante 1 erhalten haben:

Sie würden eine ähnliche Situation erleben, wenn Sie versuchen, zwei Zeichenfolgenliterale wie folgt zu verketten:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY "No" + " Order") AS n FROM dbo.T1;

Sie erhalten den gleichen Fehler, den Sie erhalten haben, als Sie das Literal „No Order“ direkt angegeben haben:

Bizarro-Welt – Fehler, die Fehler verhindern

Das Leben ist voller Überraschungen…

Eine Sache, die ein ständiges Falten verhindert, ist, wenn der Ausdruck normalerweise zu einem Fehler führen würde. Beispielsweise kann der Ausdruck 2147483646 + 1 konstant gefaltet werden, da er zu einem gültigen INT-typisierten Wert führt. Folglich schlägt ein Versuch fehl, die folgende Abfrage auszuführen:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 2147483646+1) AS n FROM dbo.T1;

Der Ausdruck 2147483647 + 1 kann jedoch nicht konstant gefaltet werden, da ein solcher Versuch zu einem INT-Überlauffehler geführt hätte. Die Auswirkungen auf die Bestellung sind sehr interessant. Versuchen Sie die folgende Abfrage (wir nennen diese eine Abfrage 2):

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 2147483647+1) AS n FROM dbo.T1;

Seltsamerweise wird diese Abfrage erfolgreich ausgeführt! Was passiert, ist, dass SQL Server einerseits keine konstante Faltung anwendet und die Reihenfolge daher auf einem Ausdruck basiert, der keine einzelne Konstante ist. Auf der anderen Seite stellt der Optimierer fest, dass der Ordnungswert für alle Zeilen gleich ist, sodass der Ordnungsausdruck insgesamt ignoriert wird. Dies wird bestätigt, wenn der Plan für diese Abfrage untersucht wird (siehe Abbildung 3).

Abbildung 3: Plan für Abfrage 2

Beobachten dass der Plan einen Abdeckungsindex mit einer Ordered: False-Eigenschaft scannt. Dies war genau unser Leistungsziel.

In ähnlicher Weise beinhaltet die folgende Abfrage einen erfolgreichen konstanten Faltversuch und schlägt daher fehl:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1/1) AS n FROM dbo.T1;

Die folgende Abfrage beinhaltet einen fehlgeschlagenen konstanten Faltversuch und ist daher erfolgreich, indem der zuvor in Abbildung 3 gezeigte Plan generiert wird:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1/0) AS n FROM dbo.T1;

Folgendes Die Abfrage beinhaltet einen erfolgreichen konstanten Faltversuch (das VARCHAR-Literal „1“ wird implizit in INT 1 konvertiert und dann wird 1 + 1 zu 2 gefaltet) und schlägt daher fehl:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1+"1") AS n FROM dbo.T1;

Die folgende Abfrage umfasst a Der konstante Faltversuch ist fehlgeschlagen („A“ kann nicht in INT konvertiert werden) und ist daher erfolgreich, da der zuvor in Abbildung 3 gezeigte Plan generiert wird:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY 1+"A") AS n FROM dbo.T1;

Um ehrlich zu sein, obwohl diese bizarre Technik unser ursprüngliches Leistungsziel erreicht, kann ich nicht sagen, dass ich sie für sicher halte und mich daher nicht so wohl darauf verlasse.

Laufzeitkonstanten basierend auf Funktionen

Wenn Sie die Suche nach einer guten Lösung für die Berechnung von Zeilennummern mit nicht deterministischer Reihenfolge fortsetzen, gibt es einige Techniken, die sicherer erscheinen als die letzte skurrile Lösung: Verwenden von Laufzeitkonstanten basierend auf Funktionen, Verwenden einer Unterabfrage basierend auf einer Konstante, Verwenden von a Alias-Spalte basierend auf einer Konstanten und unter Verwendung einer Variablen.

Wie ich in T-SQL-Fehlern, Fallstricken und Best Practices – Determinismus – erläutere, werden die meisten Funktionen in T-SQL nur einmal pro Referenz in der Abfrage ausgewertet – nicht einmal pro Zeile. Dies ist selbst bei den meisten nichtdeterministischen Funktionen wie GETDATE und RAND der Fall. Es gibt nur sehr wenige Ausnahmen von dieser Regel, wie die Funktionen NEWID und CRYPT_GEN_RANDOM, die einmal pro Zeile ausgewertet werden. Die meisten Funktionen, wie GETDATE, @@ SPID und viele andere, werden zu Beginn der Abfrage einmal ausgewertet, und ihre Werte werden dann als Laufzeitkonstanten betrachtet. Ein Verweis auf solche Funktionen wird nicht konstant gefaltet. Diese Eigenschaften machen eine Laufzeitkonstante, die auf einer Funktion basiert, zu einer guten Wahl als Fensterordnungselement, und tatsächlich scheint es, dass T-SQL dies unterstützt. Gleichzeitig erkennt der Optimierer, dass in der Praxis keine Bestellrelevanz besteht, wodurch unnötige Leistungseinbußen vermieden werden.

Hier ein Beispiel mit der Funktion GETDATE:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY GETDATE()) AS n FROM dbo.T1;

Diese Abfrage erhält den gleichen Plan wie in Abbildung 3.

Hier ist ein weiteres Beispiel mit der Funktion @@ SPID (Rückgabe der aktuellen Sitzungs-ID):

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY @@SPID) AS n FROM dbo.T1;

Was ist mit der Funktion PI? Versuchen Sie die folgende Abfrage:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY PI()) AS n FROM dbo.T1;

Dieser Fehler schlägt mit folgendem Fehler fehl:

Funktionen wie GETDATE und @@ SPID werden einmal pro Ausführung des Plans neu bewertet, sodass sie nicht abgerufen werden können konstant gefaltet. PI stellt immer die gleiche Konstante dar und wird daher konstant gefaltet.

Wie bereits erwähnt, werden nur sehr wenige Funktionen einmal pro Zeile ausgewertet, z. B. NEWID und CRYPT_GEN_RANDOM. Dies macht sie zu einer schlechten Wahl als Fensterordnungselement, wenn Sie eine nicht deterministische Reihenfolge benötigen – nicht zu verwechseln mit zufälliger Reihenfolge. Warum eine unnötige Sortierstrafe zahlen?

Hier ist ein Beispiel mit der NEWID-Funktion:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY NEWID()) AS n FROM dbo.T1;

Der Plan für diese Abfrage ist in Abbildung 4 dargestellt und bestätigt, dass SQL Server explizit hinzugefügt wurde Sortieren basierend auf dem Ergebnis der Funktion.

Abbildung 4: Plan für Abfrage 3

Wenn Sie möchten, dass die Zeilennummern zugewiesen werden in zufälliger Reihenfolge, das ist auf jeden Fall die Technik, die Sie verwenden möchten. Sie müssen sich nur bewusst sein, dass die Sortierkosten anfallen.

Verwenden einer Unterabfrage

Sie können auch eine auf einer Konstanten basierende Unterabfrage als Fensterordnungsausdruck verwenden (z. B. ORDER BY) (SELECT „No Order“)). Auch bei dieser Lösung erkennt das Optimierungsprogramm von SQL Server, dass keine Bestellrelevanz besteht, und legt daher keine unnötige Sortierung fest oder beschränkt die Auswahlmöglichkeiten der Speicher-Engine auf diejenigen, die die Reihenfolge garantieren müssen. Versuchen Sie, die folgende Abfrage als Beispiel auszuführen:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY (SELECT "No Order")) AS n FROM dbo.T1;

Sie erhalten denselben Plan wie in Abbildung 3 dargestellt.

Einer der großen Vorteile Bei dieser Technik können Sie Ihre persönliche Note hinzufügen.Vielleicht magst du NULLs wirklich:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM dbo.T1;

Vielleicht magst du eine bestimmte Zahl wirklich:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY (SELECT 42)) AS n FROM dbo.T1;

Vielleicht möchten Sie jemandem eine Nachricht senden:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY (SELECT "Lilach, will you marry me?")) AS n FROM dbo.T1;

Sie verstehen es.

Machbar, aber umständlich

Es gibt einige Techniken, die funktionieren, aber etwas umständlich sind. Eine besteht darin, einen Spaltenalias für einen Ausdruck basierend auf einer Konstante zu definieren und diesen Spaltenalias dann als Fensterordnungselement zu verwenden. Sie können dies entweder mit einem Tabellenausdruck oder mit dem Operator CROSS APPLY und einem Tabellenwertkonstruktor tun. Hier ist ein Beispiel für Letzteres:

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY ) AS n FROM dbo.T1 CROSS APPLY ( VALUES("No Order") ) AS A();

Sie erhalten denselben Plan wie in Abbildung 3 dargestellt.

Eine weitere Option ist die Verwendung einer Variablen als Fensterordnungselement:

DECLARE @ImABitUglyToo AS INT = NULL; SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY @ImABitUglyToo) AS n FROM dbo.T1;

Diese Abfrage erhält auch den in Abbildung 3 gezeigten Plan.

Was ist, wenn ich meine eigene UDF verwende? ?

Sie könnten denken, dass die Verwendung Ihrer eigenen UDF, die eine Konstante zurückgibt, eine gute Wahl als Fensterordnungselement sein könnte, wenn Sie eine nicht deterministische Reihenfolge wünschen, dies ist jedoch nicht der Fall. Betrachten Sie die folgende UDF-Definition als Beispiel:

DROP FUNCTION IF EXISTS dbo.YouWillRegretThis;GO CREATE FUNCTION dbo.YouWillRegretThis() RETURNS INTASBEGIN RETURN NULLEND;GO

Versuchen Sie, die UDF wie folgt als Klausel für die Fensterreihenfolge zu verwenden (wir nennen diese eine Abfrage 4):

SELECT id, grp, datacol, ROW_NUMBER() OVER(ORDER BY dbo.YouWillRegretThis()) AS n FROM dbo.T1;

Vor SQL Server 2019 (oder paralleler Kompatibilitätsstufe < 150) werden benutzerdefinierte Funktionen pro Zeile ausgewertet . Selbst wenn sie eine Konstante zurückgeben, werden sie nicht inline gesetzt. Folglich können Sie einerseits eine solche UDF als Fensterordnungselement verwenden, andererseits führt dies zu einer Sortierstrafe. Dies wird bestätigt, indem der Plan für diese Abfrage untersucht wird (siehe Abbildung 5).

Abbildung 5: Plan für Abfrage 4

Ab SQL Server 2019 werden unter der Kompatibilitätsstufe > = 150 solche benutzerdefinierten Funktionen eingefügt, was meistens eine großartige Sache ist, aber in unserem Fall zu einem Fehler führt:

Verwenden Sie also eine UDF basierend auf Eine Konstante als Fensterordnungselement erzwingt je nach verwendeter SQL Server-Version und Datenbankkompatibilitätsstufe entweder eine Sortierung oder einen Fehler. Kurz gesagt, tun Sie dies nicht.

Partitionierte Zeilennummern mit nicht deterministischer Reihenfolge

Ein häufiger Anwendungsfall für partitionierte Zeilennummern, die auf nicht deterministischer Reihenfolge basieren, ist die Rückgabe einer Zeile pro Gruppe. Angesichts der Tatsache, dass in diesem Szenario per Definition ein Partitionierungselement vorhanden ist, würden Sie denken, dass eine sichere Technik in einem solchen Fall darin besteht, das Fensterpartitionierungselement auch als Fensterordnungselement zu verwenden. Als ersten Schritt berechnen Sie die Zeilennummern wie folgt:

SELECT id, grp, datacol, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY grp) AS n FROM dbo.T1;

Der Plan für diese Abfrage ist in Abbildung 6 dargestellt.

Abbildung 6: Plan für Abfrage 5

Der Grund dafür, dass unser unterstützender Index mit einer Ordered: True-Eigenschaft gescannt wird, liegt darin, dass SQL Server die Zeilen jeder Partition als a verarbeiten muss Einheit. Dies ist vor dem Filtern der Fall. Wenn Sie nur eine Zeile pro Partition filtern, stehen Ihnen sowohl auftragsbasierte als auch hashbasierte Algorithmen als Optionen zur Verfügung.

Der zweite Schritt besteht darin, die Abfrage mit der Zeilennummernberechnung in einem Tabellenausdruck und in zu platzieren Die äußere Abfrage filtert die Zeile mit der Zeilennummer 1 in jeder Partition wie folgt:

Theoretisch soll diese Technik sicher sein, aber Paul White hat einen Fehler gefunden, der zeigt, dass Sie mit dieser Methode Attribute abrufen können Unterschiedliche Quellzeilen in der zurückgegebenen Ergebniszeile pro Partition. Die Verwendung einer Laufzeitkonstante basierend auf einer Funktion oder einer Unterabfrage basierend auf einer Konstante als Ordnungselement scheint auch in diesem Szenario sicher zu sein. Verwenden Sie stattdessen eine Lösung wie die folgende:

Niemand Ich werde diesen Weg ohne meine Erlaubnis gehen.

Der Versuch, Zeilennummern basierend auf einer nicht deterministischen Reihenfolge zu berechnen, ist ein häufiges Bedürfnis. Es wäre schön gewesen, wenn T-SQL die Fensterreihenfolgeklausel für die Funktion ROW_NUMBER einfach optional gemacht hätte, aber nicht. Wenn nicht, wäre es schön gewesen, wenn zumindest eine Konstante als Ordnungselement verwendet werden könnte, aber dies ist auch keine unterstützte Option.Wenn Sie jedoch nett fragen, in Form einer Unterabfrage, die auf einer Konstante basiert, oder einer Laufzeitkonstante, die auf einer Funktion basiert, lässt SQL Server dies zu. Dies sind die beiden Optionen, mit denen ich mich am wohlsten fühle. Ich fühle mich mit den skurrilen fehlerhaften Ausdrücken, die zu funktionieren scheinen, nicht wirklich wohl, daher kann ich diese Option nicht empfehlen.