Beschränken von Resultsets mit TABLESAMPLE
Mit der TABLESAMPLE-Klausel wird die Anzahl der Zeilen eingeschränkt, die aus einer Tabelle in der FROM-Klausel wiedergegeben werden, und zwar entweder auf eine bestimmte Anzahl von Stichproben oder auf einen bestimmten Prozentsatz der Zeilen (mit PERCENT). Beispiel:
TABLESAMPLE (10 PERCENT) /*Return a sample 10 percent of the rows of the result set. */
TABLESAMPLE (15 ROWS) /* Return a sample of 15 rows from the result set. */.
TABLESAMPLE kann nicht auf abgeleitete Tabellen, Tabellen auf Verbindungsservern und Tabellen angewendet werden, die aus Tabellenwertfunktionen, Rowsetfunktionen oder OPENXML abgeleitet werden. TABLESAMPLE kann nicht in der Definition einer Sicht oder einer Inlinefunktion mit Tabellenrückgabe angegeben werden.
Die TABLESPACE-Klausel besitzt folgende Syntax:
TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]
.gif "Hinweis") Hinweis |
|---|
TABLESAMPLE wurde in SQL Server 2005 eingeführt. Wenn Sie TABLESAMPLE für Datenbanken verwenden, die von einer früheren Version aktualisiert werden, muss der Kompatibilitätsgrad der Datenbank auf 90 festgelegt sein. Informationen zum Festlegen des Datenbank-Kompatibilitätsgrads finden Sie unter ALTER DATABASE (Transact-SQL). |
Mit TABLESAMPLE kann schnell eine Stichprobe aus einer großen Tabelle zurückgegeben werden, wenn eine der folgenden Bedingungen zutrifft:
Die Stichprobe muss keine echte Zufallsstichprobe auf der Ebene einzelner Zeilen sein.
Die Zeilen auf den einzelnen Seiten der Tabelle sind nicht von anderen Zeilen auf derselben Seite abhängig.
.gif "Wichtiger Hinweis") Wichtig |
|---|
Wenn Sie wirklich eine zufällige Stichprobe von einzelnen Zeilen wünschen, sollten Sie nicht TABLESAMPLE verwenden, sondern die Abfrage so ändern, dass Zeilen nach dem Zufallsprinzip ausgefiltert werden. Die folgende Abfrage gibt z. B. mithilfe der NEWID-Funktion etwa ein Prozent der Zeilen der Sales.SalesOrderDetail-Tabelle zurück: SELECT * FROM Sales.SalesOrderDetail WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) Die SalesOrderID-Spalte ist im CHECKSUM-Ausdruck enthalten, sodass NEWID() einmal pro Zeile ausgewertet wird, um eine Stichprobenentnahme auf Zeilenbasis zu ermöglichen. Der Ausdruck CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) wird zu einem zufälligen float-Wert zwischen 0 und 1 ausgewertet. |
Verwenden der Option SYSTEM
SYSTEM gibt eine von der ANSI SQL-Implementierung abhängige Stichprobenmethode an. Die Angabe von SYSTEM ist optional, diese Option ist jedoch die einzige in SQL Server verfügbare Stichprobenmethode und wird standardmäßig angewendet.
TABLESAMPLE SYSTEM gibt einen ungefähren Prozentsatz von Zeilen zurück und generiert einen Zufallswert für jede physische 8-KB-Seite in der Tabelle. Ausgehend vom Zufallswert für eine Seite und dem in der Abfrage angegebenen Prozentwert wird eine Seite entweder in die Stichprobe einbezogen oder nicht. Für jede Seite, die einbezogen wird, werden alle Zeilen im Stichprobenresultset zurückgegeben. Wenn z. B. TABLESAMPLE SYSTEM 10 PERCENT angegeben wird, gibt SQL Server alle Zeilen von ungefähr 10 % der angegebenen Datenseiten der Tabelle zurück. Wenn die Zeilen gleichmäßig über die Seiten der Tabelle verteilt sind und es eine ausreichend große Anzahl von Seiten in der Tabelle gibt, sollte die Anzahl der zurückgegebenen Zeilen ungefähr der angeforderten Stichprobengröße entsprechen. Da jedoch der für jede Seite generierte Zufallswert unabhängig von den Werten ist, die für andere Seiten generiert werden, kann es sein, dass ein größerer oder kleinerer Prozentsatz der Seiten als angefordert zurückgegeben wird. Mit dem TOP(n)-Operator können Sie die Anzahl der Zeilen auf einen angegebenen Maximalwert beschränken.
Wenn kein Prozentsatz der Gesamtanzahl von Zeilen in der Tabelle angegeben wird, sondern eine bestimmte Anzahl von Zeilen, dann wird diese Anzahl in einen Prozentsatz von Zeilen – und damit von Seiten – konvertiert, die zurückgegeben werden sollen. Der TABLESAMPLE-Vorgang wird dann mit diesem berechneten Prozentsatz ausgeführt.
Wenn die Tabelle aus einer einzelnen Seite besteht, werden entweder alle Zeilen der Seite oder keine Zeilen der Seite zurückgegeben. In diesem Fall kann TABLESAMPLE SYSTEM unabhängig von der Anzahl der Zeilen auf der Seite nur 100 % oder 0 % der Zeilen zurückgeben.
Mithilfe von TABLESAMPLE SYSTEM kann für eine bestimmte Tabelle der Ausführungsplan für einen Tabellenscan (ein Scan des Heaps oder des gruppierten Indexes, sofern vorhanden) dieser Tabelle eingeschränkt werden. Obwohl der Plan angibt, dass ein Tabellenscan ausgeführt wird, müssen tatsächlich nur die Seiten aus der Datendatei gelesen werden, die im Resultset enthalten sind.
| Wichtig |
|---|
Die TABLESAMPLE SYSTEM-Klausel sollte mit Vorsicht verwendet werden und nur, wenn darüber Klarheit besteht, welche Konsequenzen sich aus der Verwendung von Stichproben ergeben können. So wird z. B. bei einem Join von zwei Tabellen wahrscheinlich für jede Zeile in beiden Tabellen eine Übereinstimmung zurückgegeben. Wenn allerdings TABLESAMPLE SYSTEM für eine der beiden Tabellen angegeben wird, gibt es für einige Zeilen aus der Tabelle, aus der keine Stichproben entnommen werden, wahrscheinlich keine übereinstimmende Zeile in der Tabelle, aus der Stichproben entnommen werden. Dieses Verhalten könnte dazu führen, dass Sie ein Datenkonsistenzproblem in den zugrunde liegenden Tabellen vermuten, obwohl die Daten eigentlich gültig sind. Wenn TABLESAMPLE SYSTEM für beide der verknüpften Tabellen angegeben wird, ist das dann wahrgenommene Problem möglicherweise noch größer. |
Verwenden der Option REPEATABLE
Die Option REPEATABLE bewirkt, dass eine ausgewählte Stichprobe erneut zurückgegeben wird. Wenn REPEATABLE mit dem gleichen repeat_seed-Wert angegeben wird, gibt SQL Server dieselbe Teilmenge von Zeilen zurück, solange keine Änderungen an der Tabelle vorgenommen wurden. Wird REPEATABLE mit einem anderen repeat_seed-Wert angegeben, gibt SQL Server normalerweise eine andere Stichprobe der Zeilen in der Tabelle zurück. Die folgenden an der Tabelle vorgenommenen Aktionen gelten als Änderungen: Einfügen, Aktualisieren, Löschen, Neuerstellen eines Indexes, Defragmentieren eines Indexes, Wiederherstellen einer Datenbank und Anfügen einer Datenbank.
Beispiele
A. Auswählen eines Prozentwerts von Zeilen
Die Person.Person-Tabelle enthält 19.972 Zeilen. Die folgende Anweisung gibt etwa 10 Prozent der Zeilen zurück. Die Anzahl der zurückgegebenen Zeilen ändert sich normalerweise immer dann, wenn die Anweisung ausgeführt wird.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT) ;
B. Auswählen eines Prozentwerts von Zeilen mit einem Ausgangswert
Die folgende Anweisung gibt bei jedem Ausführungsvorgang die gleiche Anzahl von Zeilen zurück. Der Ausgangswert von 205 wurde beliebig ausgewählt.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT)
REPEATABLE (205) ;
C. Auswählen einer Anzahl von Zeilen
Die folgende Anweisung gibt ungefähr 100 Zeilen zurück. Die tatsächliche Anzahl der zurückgegebenen Zeilen kann erheblich davon abweichen. Wenn Sie eine kleine Anzahl (z. B. 5) angeben, werden möglicherweise keine Ergebnisse im Beispiel ausgegeben.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (100 ROWS) ;