sp_tableoption (Transact-SQL)

Articolo
12/15/2008

Data aggiornamento: 12 dicembre 2006

Imposta i valori delle opzioni per le tabelle definite dall'utente. È possibile utilizzare sp_tableoption per controllare la gestione dei dati all'interno di righe nelle tabelle con colonne di tipo varchar(max), nvarchar(max), varbinary(max), xml, text, ntext o image.

Importante:
La funzionalità text in row verrà rimossa a partire da una delle prossime versioni 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_pattern è 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) e il valore predefinito è NULL. I possibili valori di option_name sono i seguenti.

Valore	Descrizione
table lock on bulk load	Quando questa opzione è disattivata (impostazione predefinita), durante il processo di caricamento di massa nelle tabelle definite dall'utente vengono acquisiti blocchi di riga. Quando è attivata, viene acquisito un blocco di tipo aggiornamenti di massa.
insert row lock	Opzione non supportata in SQL Server 2005. 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 (attivata) oppure un valore integer compreso nell'intervallo da 24 a 7000, le nuove stringhe di tipo text, ntext o image vengono archiviate direttamente nella riga di dati. Tutti i valori esistenti di tipo BLOB (Binary Large Object: dati di tipo 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 di tipo varchar(max), nvarchar(max), varbinary(max) e xml nella tabella vengono archiviati esternamente alla riga, con un puntatore di 16 byte all'elemento principale. 0 = I valori di tipo varchar(max), nvarchar(max), varbinary(max) e xml vengono archiviati direttamente nella riga di dati, con un limite 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.
vardecimal storage format	Se TRUE, ON o 1, per la tabella designata è attivato il formato di archiviazione vardecimal. Se FALSE, OFF o 0, il formato di archiviazione vardecimal non è attivato per la tabella. È possibile attivare il formato di archiviazione vardecimal solo se tale formato è stato attivato 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 SQL Server 2005 Service Pack 2. Il formato di archiviazione vardecimal è disponibile solo nelle edizioni Enterprise, Developer ed Evaluation di SQL Server 2005.

[ @OptionValue = ] 'value'
Indica se l'opzione option_name è attivata (true, on o 1) oppure disattivata (false, off o 0). value è di tipo varchar(12) e non prevede alcun valore predefinito. Per value non viene applicata alcuna distinzione tra maiuscole e minuscole.

I valori validi per l'opzione text in row sono 0, on, off oppure un valore integer compreso nell'intervallo da 24 a 7000. Se value è impostato su on, il limite predefinito è di 256 byte.

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 attivare 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 è attivata, 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 è attivata.
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. Anche in questo caso sono valide le condizioni previste per l'archiviazione di stringhe BLOB all'interno delle righe, ovvero lo spazio disponibile nella riga di dati deve essere sufficiente per l'archiviazione dei 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 attiva l'opzione text in row per la prima volta, le stringhe BLOB esistenti non vengono convertite immediatamente, ma solo in fase di aggiornamento. In modo analogo, quando si aumenta il valore limite dell'opzione text in row, le stringhe di tipo 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 table, così come per una funzione che restituisce una variabile table, l'opzione text in row viene attivata automaticamente con il valore predefinito 256 per il parametro inline limit. Questa opzione non è modificabile.

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 il formato di archiviazione di una tabella da vardecimal a decimal, è necessario che per il database sia impostato il modello di recupero SIMPLE. La modifica del modello 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.

Valori restituiti

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

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. Attivazione 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 in formato vardecimal.

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';

Vedere anche

Cronologia modifiche

Versione	Cronologia
12 dicembre 2006	Nuovo contenuto: Aggiunta dell'opzione vardecimal storage format disponibile in SQL Server 2005 Service Pack 2.