Articolo
09/28/2010

sp_tableoption (Transact-SQL)

Imposta i valori delle opzioni per le tabelle definite dall'utente. È possibile utilizzare sp_tableoption per controllare il comportamento all'interno di 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 utilizzare 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 di tabella per più tabelle contemporaneamente. table è 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 sono indicati di seguito.

Valore	Descrizione
table lock on bulk load	Quando questa opzione è disattivata (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	Opzione non supportata in SQL Server 2008. La strategia di blocco di SQL Server prevede il blocco di riga, con possibile innalzamento di livello 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 è disattivata, 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 integer compreso nell'intervallo da 24 a 7000, le nuove stringhe text, ntext o image vengono archiviate direttamente nella riga di dati. Tutti i valori esistenti di tipo BLOB (Binary Large Object: dati text, ntext o image) verranno convertiti nel formato text in row quando si aggiorna il valore BLOB. Per ulteriori informazioni, vedere la sezione Osservazioni.
large value types out of row	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 = 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.
vardecimal storage format	Se TRUE, ON o 1, per la tabella designata è abilitato 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. Per informazioni sul formato di archiviazione vardecimal, vedere Archiviazione di dati decimal come lunghezza variabile. Per questa opzione è richiesto il SQL Server 2005 Service Pack 2. Il formato di archiviazione Vardecimal è disponibile solo nelle edizioni SQL Server Enterprise, Developer ed Evaluation. In SQL Server 2008 e versioni successive tutti i database utente sono abilitati per il formato di archiviazione vardecimal. In SQL Server 2008 e versioni successive il formato di archiviazione vardecimal è obsoleto ed è necessario utilizzare il tipo di compressione ROW. Per ulteriori informazioni, vedere Creazione di tabelle e di indici compressi. Il valore predefinito è 0.

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

Per l'opzione text in row, i valori dell'opzione 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 restituiti

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

Osservazioni

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

In sp_tableoption è possibile abilitare o disattivare 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 (Binary Large Object) 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 disattivare 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 ulteriori informazioni, vedere Gestione di dati di tipo ntext, text e image.

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.

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 AdventureWorks;
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 vardecimalstorage 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 'AdventureWorks', 'ON';
GO
USE AdventureWorks;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';