Limitation du nombre d'ensembles de résultats au moyen de la clause TABLESAMPLE
La clause TABLESAMPLE limite le nombre de lignes retournées à partir d'une table dans la clause FROM à un échantillon ou à un pourcentage (PERCENT) de lignes. Exemple :
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 ne peut pas être appliqué à des tables dérivées, à des tables de serveurs liés, ni à des tables dérivées de fonctions table, de fonctions d'ensemble de lignes ou OPENXML. La clause TABLESAMPLE ne peut pas être spécifiée dans la définition d'une vue ou d'une fonction table en ligne.
La syntaxe de la clause TABLESPACE est la suivante :
TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]
Notes
TABLESAMPLE a été introduit dans SQL Server 2005. Lorsque TABLESAMPLE est utilisé sur des bases de données mises à niveau à partir d'une version antérieure, leur niveau de compatibilité doit au moins être défini à 90. Pour définir le niveau de compatibilité de la base de données, consultez ALTER DATABASE (Transact-SQL).
Vous pouvez utiliser TABLESAMPLE pour retourner rapidement un échantillon d'une grande table lorsqu'une des conditions suivantes est remplie :
Il n'est pas obligatoire que l'échantillon soit un échantillon réellement aléatoire au niveau des lignes individuelles.
Des lignes sur certaines pages de la table ne sont pas corrélées avec d'autres lignes sur la même page.
Important
Si vous souhaitez vraiment obtenir un échantillon aléatoire de lignes individuelles, modifiez votre requête de façon à filtrer des lignes de manière aléatoire au lieu d'utiliser TABLESAMPLE. Par exemple, la requête suivante utilise la fonction NEWID pour retourner environ un pour cent des lignes de la table Sales.SalesOrderDetail :
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)
/ CAST (0x7fffffff AS int)
La colonne SalesOrderID est incluse dans l'expression CHECKSUM de sorte que NEWID() retourne une valeur une fois par ligne afin d'obtenir un échantillonnage ligne par ligne. L'expression CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) retourne une valeur aléatoire de type float comprise entre 0 et 1.
Utilisation de l'option SYSTEM
SYSTEM spécifie une méthode d'échantillonnage dépendante d'une implémentation ANSI de SQL. L'option SYSTEM est facultative, mais il s'agit de la seule méthode d'échantillonnage disponible dans SQL Server. Elle est appliquée par défaut.
TABLESAMPLE SYSTEM retourne un pourcentage de lignes approximatif et génère une valeur aléatoire pour chaque page physique de 8 Ko dans la table. En fonction de la valeur aléatoire pour une page et du pourcentage spécifié dans la requête, une page est incluse dans l'échantillon ou exclue. Chaque page incluse retourne toutes les lignes de l'échantillon de résultats. Par exemple, si vous spécifiez TABLESAMPLE SYSTEM 10 PERCENT, SQL Server retourne toutes les lignes présentes sur 10 % environ des pages de données spécifiées de la table. Si les lignes sont distribuées de manière égale sur les pages de la table, et s'il y a un nombre suffisant de pages dans la table, le nombre de lignes retournées devrait s'approcher de la taille de l'échantillon demandée. Toutefois, étant donné que la valeur aléatoire générée pour chaque page est indépendante des valeurs générées pour les autres pages, il est possible que le pourcentage de pages retournées soit inférieur ou supérieur au pourcentage demandé. L'opérateur TOP(n) peut être utilisé pour limiter le nombre de lignes à un plafond spécifié.
Lorsqu'un nombre de lignes est spécifié au lieu d'un pourcentage basé sur le nombre total de lignes dans la table, ce nombre est converti en un pourcentage de lignes et, par conséquent, de pages, qui doivent être retournées. L'opération TABLESAMPLE est ensuite exécutée avec ce pourcentage calculé.
Si la table est composée d'une seule page, soit toutes les lignes de la page sont retournées, soit aucune ligne de la page n'est retournée. Dans ce cas, TABLESAMPLE SYSTEM peut uniquement retourner 100 % ou 0 % des lignes sur une page, quel que soit le nombre de lignes sur la page.
L'utilisation de TABLESAMPLE SYSTEM pour une table spécifique contraint le plan d'exécution à utiliser seulement une analyse de table (une analyse du segment de mémoire ou, le cas échéant, d'un index cluster) sur la table. Bien que le plan montre qu'une analyse de table (Table Scan) a eu lieu, seules les pages incluses dans l'ensemble de résultats sont réellement nécessaires pour lire le fichier de données.
Important
La clause TABLESAMPLE SYSTEM doit être utilisée avec prudence et en toute connaissance de cause, notamment au niveau des implications de l'utilisation de l'échantillonnage. Par exemple, une jointure de deux tables est susceptible de retourner une correspondance pour chaque ligne des deux tables ; cependant, si la clause TABLESAMPLE SYSTEM est spécifiée pour l'une ou l'autre des deux tables, il est probable que certaines lignes provenant de la table non échantillonnée n'aient pas d'homologue dans la table échantillonnée. Ce comportement peut vous conduire à suspecter qu'il existe un problème de cohérence de données dans les tables sous-jacentes, quand les données sont réellement valides. De même, si la clause TABLESAMPLE SYSTEM est spécifiée pour les deux tables jointes, ce faux problème risque même d'être pire.
Utilisation de l'option REPEATABLE
L'option REPEATABLE fait en sorte qu'un échantillon sélectionné soit retourné une nouvelle fois. Lorsque l'option REPEATABLE est spécifiée avec la même valeur repeat_seed, SQL Server retourne le même sous-ensemble de lignes, à condition qu'aucune modification n'ait été apportée à la table. Lorsque l'option REPEATABLE est spécifiée avec une valeur repeat_seed différente, SQL Server retourne généralement un échantillon de lignes différent. Les actions suivantes effectuées sur la table sont considérées comme des modifications : insertion, mise à jour, suppression, recréation d'index, défragmentation d'index, restauration d'une base de données et attachement d'une base de données.
Exemples
A. Sélection d'un pourcentage de lignes
La table Person.Person contient 19 972 lignes. L'instruction suivante retourne environ 10 % des lignes. Le nombre de lignes retournées change à chaque exécution de l'instruction.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT) ;
B. Sélection d'un pourcentage de lignes avec une valeur d'échantillonnage
L'instruction suivante retourne le même ensemble de lignes à chacune de ses exécutions. La valeur d'échantillonnage 205 a été choisie arbitrairement.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT)
REPEATABLE (205) ;
C. Sélection d'un nombre de lignes
L'instruction suivante retourne environ 100 lignes. Le nombre de lignes retournées peut varier de façon significative. Si vous spécifiez un petit nombre, tel que 5, il se peut que vous ne receviez pas de résultats dans l'échantillon.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (100 ROWS) ;