Dati all'interno di righe
I tipi per valori di grandi dimensioni da piccoli a medi (varchar(max), nvarchar(max), varbinary(max) e xml) e i tipi di dati LOB (text, ntext, e image) possono essere archiviati in una riga di dati. Questo comportamento è controllato tramite l'utilizzo di due opzioni nella stored procedure di sistema sp_tableoption, l'opzione large value types out of row per i tipi per valori di grandi dimensioni e l'opzione text in row per i tipi LOB. Queste opzioni sono particolarmente indicate per le tabelle in cui i valori dei dati di questi tipi vengono generalmente letti o scritti un'unità e la maggioranza delle istruzioni che fanno riferimento alla tabella fanno riferimento a questo tipo di dati. In base alle caratteristiche di utilizzo o relative al carico di lavoro, l'archiviazione dei dati all'interno di righe può non essere indicata.
.gif "Nota importante") Importante |
|---|
La stored procedure text in row verrà rimossa a partire da una delle prossime versioni di SQL Server. Evitare pertanto di utilizzarla in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni che attualmente utilizzano text in row. È consigliabile archiviare i dati di grandi dimensioni utilizzando i tipi di dati varchar(max), nvarchar(max) oppure varbinary(max). Per controllare il comportamento all'interno e all'esterno di righe di questi tipi di dati, utilizzare l'opzione large value types out of row. |
A meno che l'opzione text in row sia impostata su ON oppure su un limite specifico all'interno di righe, le stringhe text, ntext oppure image sono stringhe binarie o di caratteri di grandi dimensioni (fino a 2 GB) archiviate all'esterno di una riga di dati. La riga di dati include solo un puntatore di testo a 16 byte che fa riferimento al nodo radice di un albero composto da puntatori interni. Questi puntatori eseguono il mapping delle pagine in cui sono archiviati i frammenti della stringa. Per ulteriori informazioni sull'archiviazione delle stringhe text, ntext oppure image, vedere Utilizzo dei dati di tipo text e image.
È possibile impostare un'opzione text in row per le tabelle che includono colonne di tipi di dati LOB. È inoltre possibile specificare un limite per l'opzione text in row, da 24 a 7.000 byte.
Allo stesso modo, a meno che l'opzione large value types out of row non sia impostata su ON, le colonne varchar(max), nvarchar(max), varbinary(max) e xml vengono archiviate, se possibile, nella riga di dati. In questo caso, Motore di database di SQL Server tenta di adattare il valore specifico, se è possibile. In caso contrario lo sposta all'esterno delle righe. Se large value types out of row è impostata su ON, i valori vengono archiviati all'esterno di righe e nel record viene archiviato solo un puntatore di testo a 16 byte.
[!NOTA]
Il massimo spazio di archiviazione all'interno di righe per tipi di dati per valori di grandi dimensioni è impostato su 8.000 byte quando l'opzione large value types out of row è OFF. Diversamente dall'opzione text in row, non è possibile specificare il limite all'interno della riga per le colonne nella tabella.
Quando una tabella è configurata per archiviare tipi per valori di grandi dimensioni o tipi di dati LOB direttamente nella riga di dati, i valori effettivi delle colonne saranno all'interno di righe se si verifica una delle condizioni seguenti:
La lunghezza della stringa è inferiore al valore limite specificato per le colonne text, ntext e image.
Nella riga di dati è disponibile spazio sufficiente per includere la stringa.
Quando un tipo per valori di grandi dimensioni o un valore di colonne di tipo LOB viene archiviato nella riga di dati, non è necessario che Motore di database abbia accesso a una pagina o a un set di pagine separato per leggere o scrivere la stringa di caratteri o binaria. Le operazioni di lettura e scrittura di stringhe all'interno di righe risultano pertanto veloci quanto le operazioni di lettura e scrittura di stringhe di tipo varchar, nvarchar o varbinary. Analogamente, quando i valori vengono archiviati all'esterno di righe, in Motore di database si verifica un'operazione di lettura o scrittura di pagina aggiuntiva.
Per tipi di dati LOB, se la lunghezza della stringa è maggiore del limite impostato per l'opzione text in row o dello spazio disponibile nella riga, il set di puntatori altrimenti archiviato nel nodo radice dell'albero dei puntatori viene archiviato nella riga. I puntatori vengono archiviati nella riga se si verifica una delle condizioni seguenti:
Lo spazio necessario per l'archiviazione dei puntatori è inferiore al limite impostato per l'opzione text in row.
Nella riga di dati è disponibile spazio sufficiente per includere i puntatori.
Quando i puntatori vengono spostati dal nodo radice alla riga, non è necessario che Motore di database utilizzi un nodo principale. Questo può determinare l'eliminazione di un accesso di pagina durante la lettura o la scrittura della stringa e consente di ottimizzare le prestazioni.
Quando i nodi radice sono utilizzati, vengono archiviati come uno dei frammenti di stringa in una pagina LOB e possono includere fino a cinque puntatori interni. Motore di database richiede 72 byte di spazio sulla riga per l'archiviazione di cinque puntatori in una stringa all'interno di righe. Se lo spazio sulla riga non è sufficiente per la memorizzazione dei puntatori quando l'opzione text in row è ON oppure l'opzione large value types out of row è OFF, Motore di database potrebbe dover allocare una pagina 8 KB per memorizzare i puntatori. Se la lunghezza dei dati del valore è superiore a 40.200 byte, sono necessari più di cinque puntatori all'interno di righe. Solo 24 byte vengono archiviati nella riga principale e una pagina di dati aggiuntiva viene allocata nello spazio di archiviazione LOB.
Se nella riga vengono archiviate stringhe di grandi dimensioni, la modalità di archiviazione utilizzata è analoga a quella delle stringhe di lunghezza variabile. Motore di database ordina le colonne in ordine decrescente per dimensioni ed esegue il push dei valori all'esterno di righe finché le colonne restanti non si adattano alla pagina di dati (8K).
Attivazione e disattivazione dell'opzione large value types out of row
È possibile attivare l'opzione large value types out of row per una tabella utilizzando sp_tableoption nel modo seguente:
sp_tableoption N'MyTable', 'large value types out of row', 'ON'
Se viene specificato OFF, il limite all'interno della riga per le colonne varchar(max)nvarchar(max), varbinary(max) e xml viene impostato su 8.000 byte. Solo un puntatore radice a 16 byte viene archiviato all'interno di righe e il valore viene archiviato nello spazio di archiviazione LOB. È consigliabile impostare l'opzione su ON per le tabelle in cui la maggioranza delle istruzioni non fa riferimento alle colonne di tipi per valori di grandi dimensioni. L'archiviazione di queste colonne all'esterno di righe implica che più righe possono essere incluse in una pagina, riducendo pertanto il numero di operazioni I/O necessarie per analizzare la tabella.
Con il valore dell'opzione impostato su OFF, molte stringhe potrebbero venire archiviate direttamente nella riga, riducendo potenzialmente il numero di righe di dati incluse in ogni pagina. Se la maggioranza delle istruzioni che fanno riferimento alla tabella non accede alle colonne varchar(max), nvarchar(max), varbinary(max) e xml, riducendo il numero di righe in una pagina è possibile aumentare le pagine che sarà necessario leggere per l'elaborazione delle query. Riducendo le righe per pagina è possibile aumentare il numero di pagine che potranno essere analizzate nel caso Query Optimizer non individui alcun indice utilizzabile.
È inoltre possibile utilizzare sp_tableoption per disattivare l'opzione out-of-row:
sp_tableoption N'MyTable', 'large value types out of row', 'OFF'
Quando il valore dell'opzione large value types out of row si modifica, i valori dell colonne esistenti varchar(max), nvarchar(max), varbinary(max) e xml non vengono convertiti immediatamente. L'archiviazione delle stringhe viene modificata nel corso del successivo aggiornamento. Qualsiasi nuovo valore inserito in una tabella viene archiviato in base all'opzione di tabella attiva.
Per analizzare il valore dell'opzione large value types out of row per una tabella specifica, eseguire una query sulla colonna large_value_types_out_of_row della vista del catalogo sys.tables. Il valore della colonna è 0 se l'opzione large value types out of row non è attivata per la tabella e 1 se i tipi per valori di grandi dimensioni sono archiviati all'esterno delle righe.
Attivazione e disattivazione dell'opzione text in row
È possibile attivare l'opzione text in row per una tabella utilizzando sp_tableoption nel modo seguente:
sp_tableoption N'MyTable', 'text in row', 'ON'
Facoltativamente, è possibile specificare una lunghezza massima compresa tra 24 e 7.000 byte per una stringa text, ntext e image che sia possibile archiviare in una riga di dati:
sp_tableoption N'MyTable', 'text in row', '1000'
Se si specifica ON anzichè un valore specifico, per impostazione predefinita il limite massimo viene impostato su 256 byte. Questo valore predefinito comporta la quasi totalità dei vantaggi in termini di prestazioni ottenibili dall'utilizzo dell'opzione text in row. Sebbene in genere non sia consigliabile un valore minore di 72, è altrettanto importante non impostare un valore troppo elevato. Questo si applica in particolare alle tabelle in cui la maggioranza delle istruzioni non fa riferimento alle colonne text, ntext e image, oppure in cui sono presenti più colonne text, ntext e image.
Se si imposta un limite elevato per text in row e molte stringhe vengono archiviate direttamente nella riga, è possibile ridurre notevolmente il numero di righe di dati incluse in ogni pagina. Se la maggioranza delle istruzioni che fanno riferimento alla tabella non accede alle colonne text, ntext o image, riducendo il numero di righe in una pagina è possibile aumentare le pagine che sarà necessario leggere per l'elaborazione delle query. Riducendo le righe per pagina è possibile aumentare le dimensioni degli indici e le pagine che potranno essere analizzate nel caso Query Optimizer non individui alcun indice utilizzabile. Il valore predefinito (256) impostato per il limite dell'opzione text in row è infatti sufficientemente elevato da garantire l'archiviazione nelle righe di stringhe di piccole dimensioni e di puntatori di testo radice, ma non tanto elevato da ridurre il numero di righe di ogni pagina fino a influire sulle prestazioni.
L'opzione text in row è automaticamente impostata su 256 per variabili con tipo di dati tabella e per tabelle restituite da funzioni definite dall'utente che restituiscono una tabella. Questa impostazione non può essere modificata.
È inoltre possibile utilizzare sp_tableoption per disattivare l'opzione, specificando il valore di opzione OFF o 0:
sp_tableoption N'MyTable', 'text in row', 'OFF'
Per analizzare il valore dell'opzione text in row per una tabella specifica, eseguire una query sulla colonna text_in_row_limit della vista del catalogo sys.tables. La colonna è 0 se nella tabella text in row non è attiva, mentre è maggiore di 0 se è stato impostato un limite all'interno di righe.
Effetti dell'utilizzo dell'opzione text in row
L'opzione text in row produce gli effetti seguenti:
Dopo aver attivato l'opzione text in row, è possibile utilizzare le istruzioni TEXTPTR, READTEXT, UPDATETEXT oppure WRITETEXT per la lettura o la modifica delle parti di eventuali valori text, ntext oppure image archiviati nella tabella. Nelle istruzioni SELECT è possibile leggera l'intera stringa text, ntext o image, oppure utilizzare la funzione SUBSTRING per leggere parti della stringa. Tutte le istruzioni INSERT o UPDATE che fanno riferimento alla tabella devono specificare stringhe complete e non possono modificare solo una parte di una stringa text, ntext o image.
Quando l'opzione text in row viene attivata per la prima volta, le stringhe text, ntext o image esistenti non vengono immediatamente convertite in stringhe all'interno di righe. La conversione avviene solo se le stringhe vengono successivamente aggiornate. Eventuali stringhe text, ntext o image inserite dopo l'attivazione dell'opzione text in row vengono inserite come stringhe all'interno di righe.
La disattivazione dell'opzione text in row può essere un'operazione registrata di esecuzione impegnativa. La tabella è bloccata e tutte le stringhe all'interno di righe text, ntext e image sono convertite in stringhe regolari text, ntext e image. La durata dell'esecuzione del comando e la quantità di dati modificati dipendono dal numero di stringhe text, ntext e image da convertire da stringhe all'interno di righe in stringhe regolari.
L'opzione text in row non ha effetto sul funzionamento di SQL Server Native client Provider OLE DB o del driver SQL Server Native client ODBC, fatta eccezione per una velocizzazione dell'accesso ai dati text, ntext e image.
Le funzioni per i valori text e image di DB-Library, ad esempio dbreadtext e dbwritetext, non possono essere utilizzate su una tabella dopo l'attivazione dell'opzione text in row.
Vedere anche