Limitazione dei set di risultati utilizzando TABLESAMPLE
La clausola TABLESAMPLE limita il numero di righe restituite da una tabella nella clausola FROM a un numero di esempio o PERCENT di righe. Ad esempio:
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. */.
Non è possibile applicare TABLESAMPLE a tabelle derivate, tabelle da server collegati e tabelle derivate da funzioni con valori di tabella, funzioni per i set di righe oppure OPENXML. Non è possibile specificare TABLESAMPLE nella definizione di una vista o in una funzione inline con valori di tabella.
La sintassi della clausola TABLESPACE è la seguente:
TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]
Nota
TABLESAMPLE è stato introdotto in SQL Server 2005. Quando si utilizza TABLESAMPLE nei database aggiornati da una versione precedente , il livello di compatibilità del database deve essere impostato almeno su 90. Per impostare il livello di compatibilità del database, vedere ALTER DATABASE (Transact-SQL).
È possibile utilizzare TABLESAMPLE per restituire rapidamente un esempio di dati da una tabella di grandi dimensioni, se una delle condizioni seguenti è true:
L'esempio non deve essere realmente casuale a livello di singole righe.
Le righe di pagine singole della tabella non sono correlate ad altre righe della stessa pagina.
.gif "Nota importante") Importante |
|---|
Se si desidera ottenere effettivamente un esempio causale di righe singole, modificare la query in modo da filtrare casualmente le righe, invece di utilizzare TABLESAMPLE. Ad esempio, nella query seguente viene utilizzata la funzione NEWID per restituire circa l'uno percento delle righe della tabella Sales.SalesOrderDetail: SELECT * FROM Sales.SalesOrderDetail WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) La colonna SalesOrderID è inclusa nell'espressione CHECKSUM. In questo modo NEWID() restituisce una riga alla volta ed esegue pertanto un campionamento riga per riga. L'espressione CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) restituisce un valore float casuale compreso tra 0 e 1. |
Utilizzo dell'opzione SYSTEM
SYSTEM specifica un metodo di campionamento dipendente dall'implementazione ANSI SQL. La specificazione di SYSTEM è facoltativa, ma si tratta dell'unico metodo di campionamento disponibile in SQL Server, applicato per impostazione predefinita.
TABLESAMPLE SYSTEM restituisce una percentuale approssimativa di righe e genera un valore casuale per ogni pagina fisica di 8 KB della tabella. In base al valore casuale per una pagina e alla percentuale specificata nella query, una pagina viene inclusa o esclusa nell'esempio. Ogni pagina inclusa restituisce tutte le righe nel set di risultati di esempio. Ad esempio, se si specifica TABLESAMPLE SYSTEM 10 PERCENT, SQL Server restituisce tutte le righe approssimativamente sul 10 per cento delle pagine di dati della tabella specificata. Se le righe sono distribuite in modo uniforme sulle pagine della tabella, e se è presente un numero sufficiente di pagine nella tabella, il numero di righe restituite dovrebbe avvicinarsi alle dimensioni di esempio richieste. Tuttavia, poiché il valore casuale generato per ogni pagina è indipendente dai valori generati per ogni altra pagina, è possibile che venga restituita una percentuale maggiore o minore di pagine rispetto alla richiesta. L'operatore TOP(n) può essere utilizzato per limitare il numero di righe per un dato valore massimo.
Quando viene specificato un numero di righe, anzichè una percentuale basata sul numero totale di righe nella tabella, il numero viene convertito in una percentuale delle righe, e pertanto delle pagine, che dovrebbero venire restituite. TABLESAMPLE viene quindi eseguita con la percentuale così calcolata.
Se la tabella è costituita da una singola pagina, vengono restituite tutte le righe sulla pagina oppure nessuna. In questo caso TABLESAMPLE SYSTEM è in grado di restituire solo il 100 percento o lo 0 percento delle righe di una pagina, indipendentemente dal numero di righe presenti nella pagina.
L'utilizzo di TABLESAMPLE SYSTEM per una tabella specifica limita il piano di esecuzione all'utilizzo di un'analisi di tabella (un'analisi dell'heap o dell'indice cluster, se disponibile) sulla tabella. Sebbene il piano mostri che è stata eseguita un'analisi di tabella, solo per le pagine include nel set di risultati è effettivamente necessaria la lettura dal file di dati.
| Importante |
|---|
La clausola TABLESAMPLE SYSTEM dovrebbe essere utilizzata con attenzione e conoscendo alcune delle implicazioni dell'utilizzo del campionamento. Ad esempio, un join di due tabelle restituirà probabilmente una corrispondenza per ogni riga in entrambe le tabelle. Tuttavia, se TABLESAMPLE SYSTEM è specificata per una delle due tabelle, per alcune righe restituite dalla tabella non campionata probabilmente non sarà disponibile una riga corrispondente nella tabella campionata, il che potrebbe far pensare a un potenziale problema di consistenza dei dati nelle tabelle sottostanti, mentre in realtà i dati sono validi. Analogamente, se TABLESAMPLE SYSTEM è specificata per entrambe le tabelle unite in join, il problema percepito sarà probabilmente anche più grave. |
Utilizzo dell'opzione REPEATABLE
L'opzione REPEATABLE determina l'ulteriore restituzione di un esempio selezionato. Quando REPEATABLE è specificata con lo stesso valore repeat_seed, SQL Server restituisce lo stesso subset di righe finché la tabella non viene modificata. Quando REPEATABLE è specificata con un diverso valore repeat_seed, SQL Server restituirà in genere un diverso esempio delle righe nella tabella. Sono considerate modifiche le operazioni seguenti eseguite sulla tabella: inserimento, aggiornamento, eliminazione, ricompilazione dell'indice, deframmentazione dell'indice, ripristino del database e collegamento del database.
Esempi
A. Selezione di una percentuale di righe
La tabella Person.Person contiene 19.972 righe. L'istruzione seguente restituisce circa il 10 percento delle righe. Il numero di righe restituite cambia in genere ogni volta che l'istruzione viene eseguita.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT) ;
B. Selezione di una percentuale di righe con un valore di inizializzazione
L'istruzione seguente restituisce lo stesso set di righe ogni volta che viene eseguito. Il valore di inizializzazione di 205 è stato scelto in modo arbitrario.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT)
REPEATABLE (205) ;
C. Selezione di un numero di righe
L'istruzione seguente restituisce circa 100 righe. Il numero effettivo di righe restituite può variare in modo significativo. Se si specifica un numero basso, ad esempio 5, è possibile che l'esempio non restituisca alcun risultato.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (100 ROWS) ;