sp_tableoption (Transact-SQL)

Articolo
09/12/2014

Imposta i valori delle opzioni per le tabelle definite dall'utente. È possibile usare sp_tableoption per controllare il comportamento interno alle righe delle tabelle con colonne di tipo varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image o colonne con un tipo dati di grandi dimensioni definito dall'utente.

Importante
La funzionalità text in row verrà rimossa nelle versioni future di SQL Server. Per l'archiviazione di valori di grandi dimensioni, è consigliabile usare i tipi di dati varchar(max), nvarchar(max) e varbinary(max).

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

sp_tableoption [ @TableNamePattern = ] 'table' 
     , [ @OptionName = ] 'option_name' 
     ,[ @OptionValue =] 'value'

Argomenti

[ @TableNamePattern =] 'table'
Nome completo o non qualificato di una tabella di database definita dall'utente. Nel caso di un nome completo, ovvero contenente il nome del database, il nome del database deve corrispondere a quello del database corrente. Non è possibile configurare le opzioni tabella per più tabelle contemporaneamente. table è di tipo nvarchar(776) e non prevede alcun valore predefinito.

[ @OptionName =] 'option_name'
Nome di un'opzione di tabella. option_name è di tipo varchar(35) con nessun valore predefinito di tipo NULL. I possibili valori di option_name vengono indicati di seguito.

Valore	Descrizione
table lock on bulk load	Quando questa opzione è disabilitata (impostazione predefinita), durante il processo di caricamento bulk nelle tabelle definite dall'utente vengono acquisiti blocchi di riga. Quando è abilitata, viene acquisito un blocco di tipo aggiornamenti bulk.
insert row lock	Non più supportata. La strategia di blocco di SQL Server prevede il blocco di riga, con possibile promozione a blocco di pagina o di tabella. Questa opzione non influisce sulla funzionalità di blocco di SQL Server ed è disponibile solo per compatibilità con script e procedure esistenti.
text in row	Quando questa opzione è disabilitata, ovvero impostata su OFF o 0 (impostazione predefinita), il funzionamento corrente rimane invariato e la riga non contiene valori BLOB. Se si specifica questa opzione e il valore di @OptionValue è ON (abilitata) oppure un valore intero compreso tra 24 e 7000, le nuove stringhe text, ntext o image vengono archiviate direttamente nella riga di dati. Tutti i dati esistenti di tipo BLOB (oggetto binario di grandi dimensioni), text, ntext o image, verranno convertiti nel formato text in row durante l'aggiornamento del valore BLOB. Per altre informazioni, vedere la sezione Osservazioni.
large value types out of row	1 = 1 = I dati delle colonne varchar(max), nvarchar(max), varbinary(max), xml e delle colonne con un tipo dati di grandi dimensioni definito dall'utente nella tabella vengono archiviati esternamente alla riga, con un puntatore di 16 byte all'elemento radice. 0 = 0 = I valori varchar(max), nvarchar(max), varbinary(max), xml e i valori elevati definiti dall'utente vengono archiviati direttamente nella riga di dati, con un limite massimo di 8000 byte e a condizione che le dimensioni del record siano sufficienti per contenere il valore. Se le dimensioni del record non sono sufficienti per il valore, all'interno della riga viene archiviato un puntatore e i dati restanti vengono archiviati all'esterno della riga nello spazio di archiviazione LOB. Il valore predefinito è 0.
formato di archiviazione vardecimal	Se TRUE, ON o 1, la tabella designata è abilitata per il formato di archiviazione vardecimal. Se FALSE, OFF o 0, il formato di archiviazione vardecimal non è abilitato per la tabella. È possibile abilitare il formato di archiviazione vardecimal solo se tale formato è stato abilitato anche per il database con sp_db_vardecimal_storage_format. In SQL Server 2008 e versioni successive, il formato di archiviazione vardecimal è deprecato ed è necessario usare il tipo di compressione ROW. Per altre informazioni, vedere Compressione dei dati. Il valore predefinito è 0.

[ @OptionValue =] 'value'
Indica se l'opzione option_name è abilitata (TRUE, ON o 1) oppure disabilitata (FALSE, OFF o 0). value è di tipo varchar(12) e non prevede alcun valore predefinito. value non fa distinzione tra maiuscole e minuscole.

Per l'opzione text in row, i valori validi sono 0, ON, OFF o un numero intero compreso tra 24 e 7000. Quando value è ON, per impostazione predefinita il limite è di 256 byte.

Valori di codice restituiti

0 (esito positivo) o numero di errore (esito negativo)

Osservazioni

La stored procedure sp_tableoption può essere usata solo per impostare i valori delle opzioni per le tabelle definite dall'utente. Per visualizzare le proprietà della tabella, usare OBJECTPROPERTY.

In sp_tableoption è possibile abilitare o disabilitare l'opzione text in row solo in tabelle contenenti colonne di testo. Nel caso di tabelle prive di colonne di questo tipo, SQL Server genera un errore.

Quando l'opzione text in row è abilitata, il parametro @OptionValue consente di specificare le dimensioni massime per l'archiviazione di dati BLOB (oggetto binario di grandi dimensioni) all'interno delle righe. I possibili valori sono compresi tra 24 e 7000 byte. Il valore predefinito è 256 byte.

Le stringhe di tipo text, ntext o image vengono archiviate nella riga di dati solo in presenza delle condizioni seguenti:

L'opzione text in row è abilitata.
La lunghezza della stringa è inferiore al valore limite specificato in @OptionValue.
Nella riga di dati lo spazio disponibile è sufficiente.

Quando si archiviano stringhe BLOB nella riga di dati, le operazioni di lettura e scrittura delle stringhe di tipo text, ntext o image vengono eseguite con la stessa velocità delle operazioni di lettura e scrittura di stringhe di caratteri e binarie. SQL Server non accede a pagine separate per la lettura o scrittura di stringhe BLOB.

Se le dimensioni di una stringa di tipo text, ntext o image sono maggiori del limite specificato o dello spazio disponibile nella riga, vengono archiviati solo i puntatori. Le condizioni per l'archiviazione delle stringhe BLOB nella riga devono comunque essere soddisfatte, ovvero lo spazio disponibile nella riga di dati deve essere sufficiente per includervi i puntatori.

Le stringhe e i puntatori BLOB archiviati nella riga di una tabella vengono gestiti in modo analogo alle stringhe a lunghezza variabile, ovvero SQL Server utilizza solo il numero di byte necessari per l'archiviazione della stringa o del puntatore.

Quando si abilita l'opzione text in row per la prima volta, le stringhe BLOB esistenti non vengono convertite immediatamente, ma solo in fase di aggiornamento. Analogamente, quando si aumenta il limite dell'opzione text in row, le stringhe text, ntext o image già incluse nella riga di dati vengono convertite per la conformità al nuovo limite solo in occasione del successivo aggiornamento.

[!NOTA]

Per disabilitare l'opzione text in row o ridurne il valore limite, è necessario convertire tutti i valori BLOB. L'operazione può pertanto richiedere tempi lunghi, a seconda del numero di stringhe BLOB da convertire. Durante il processo di conversione la tabella viene bloccata.

Per una variabile di tabella, così come per una funzione che restituisce una variabile di tabella, l'opzione text in row viene abilitata automaticamente con il valore predefinito 256 per il parametro inline limit. Questa opzione non può essere modificata.

L'opzione text in row supporta le funzioni TEXTPTR, WRITETEXT, UPDATETEXT e READTEXT. Gli utenti possono leggere parti di un valore BLOB tramite la funzione SUBSTRING(). È importante sottolineare, tuttavia, che i limiti massimi relativi a durata e numero per i puntatori di testo all'interno di righe sono diversi da quelli degli altri puntatori di testo.

Per modificare di nuovo il formato di archiviazione di una tabella da vardecimal a decimal, è necessario che per il database sia impostata la modalità di recupero SIMPLE. La modifica della modalità di recupero causa l'interruzione della catena di log per il backup. Dopo la rimozione del formato di archiviazione vardecimal da una tabella è pertanto necessario creare un backup completo del database.

Se è necessario convertire una colonna esistente con tipo di dati BLOB (text, ntext o image) in tipi di valori di grandi dimensioni da piccoli a medi (varchar(max), nvarchar(max) o varbinary(max)) e nell'ambiente in uso la maggior parte delle istruzioni non fa riferimento alle colonne con tipi di valori di grandi dimensioni, provare a impostare large_value_types_out_of_row su 1 per ottenere prestazioni ottimali. Quando viene modificato il valore dell'opzione large_value_types_out_of_row, i valori 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 risultati immediati, creare una copia dei dati e quindi ripopolare la tabella dopo la modifica dell'impostazione di large_value_types_out_of_row o aggiornare ciascuna colonna con tipi di valori di grandi dimensioni da piccoli a medi con se stessa in modo tale che l'archiviazione delle stringhe venga modificata con l'opzione di tabella attiva. Provare a ricompilare gli indici nella tabella dopo l'aggiornamento o il ripopolamento per ridurre la tabella.

Autorizzazioni

Per l'esecuzione di sp_tableoption è richiesta l'autorizzazione ALTER per la tabella.

Esempi

A.Archiviazione di dati xml all'esterno delle righe

Nell'esempio seguente viene impostata l'archiviazione all'esterno delle righe per i dati xml nella tabella HumanResources.JobCandidate.

USE AdventureWorks2012;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

B.Abilitazione del formato di archiviazione vardecimal in una tabella

Nell'esempio seguente viene modificata la tabella Production.WorkOrderRouting per attivare l'archiviazione del tipo di dati decimal nel vardecimal storage format.

USE master;
GO
-- The database must be enabled for vardecimal storage format
-- before a table can be enabled for vardecimal storage format
EXEC sp_db_vardecimal_storage_format 'AdventureWorks2012', 'ON';
GO
USE AdventureWorks2012;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';