sp_tableoption (Transact-SQL)

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Imposta i valori delle opzioni per le tabelle definite dall'utente. sp_tableoptionpuò essere usato per controllare il comportamento in riga delle tabelle con colonne di tipo varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image o di grandi dimensioni definite dall'utente.

Importante

La funzionalità testo nella riga verrà rimossa in una versione futura di SQL Server. Per archiviare dati con valori di grandi dimensioni, è consigliabile usare i tipi di dati varchar(max),nvarchar(max) e varbinary(max).

Convenzioni relative alla sintassi Transact-SQL

Sintassi

sp_tableoption
    [ @TableNamePattern = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Argomenti

[ @TableNamePattern = ] N'TableNamePattern'

Nome qualificato o non qualificato di una tabella di database definita dall'utente. @TableNamePattern è nvarchar(776), senza impostazione predefinita. Nel caso di un nome qualificato di tabella, ovvero contenente un nome di database, il nome del database deve corrispondere a quello del database corrente. Le opzioni di tabella per più tabelle non possono essere impostate contemporaneamente.

[ @OptionName = ] 'OptionName'

Nome dell'opzione di tabella. @OptionName è varchar(35)e può essere uno dei valori seguenti.

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. Se abilitata, viene acquisito un blocco di tipo aggiornamento bulk.
insert row lock Non più supportata.

Questa opzione non ha alcun effetto sul comportamento di blocco di SQL Server ed è inclusa solo per la compatibilità di script e procedure esistenti.
text in row Quando OFF o 0 (disabilitato, impostazione predefinita), non modifica il comportamento corrente e non è presente ALCUN BLOB nella riga.

Se specificato e @OptionValue è ON (abilitato) o un valore intero da 24 tramite 7000, le nuove stringhe di testo, ntext o immagine vengono archiviate direttamente nella riga di dati. Tutti i dati BLOB esistenti (oggetto binario di grandi dimensioni: testo, ntext o immagine) vengono modificati in testo in formato riga quando il valore BLOB viene aggiornato. Per altre informazioni, vedere la sezione Note.
large value types out of row 1 = colonne varchar(max), nvarchar(max), varbinary(max), xml e large user-defined type (UDT) nella tabella vengono archiviate all'esterno della riga, con un puntatore a 16 byte alla radice.

0 = valori varchar(max), nvarchar(max), varbinary(max), xml e UDT di grandi dimensioni vengono archiviati direttamente nella riga di dati, fino a un limite di 8.000 byte e purché il valore possa rientrare nel record. Se il valore non rientra nel record, un puntatore viene archiviato in riga e il resto viene archiviato all'esterno della riga nello spazio di archiviazione LINEB. Il valore predefinito è 0.

Il tipo definito dall'utente (UDT) di grandi dimensioni si applica a: SQL Server 2008 (10.0.x) e versioni successive.

Usare l'opzione TEXTIMAGE_ON CREATE TABLE per specificare un percorso per l'archiviazione di tipi di dati di grandi dimensioni.
formato di archiviazione vardecimal Si applica a: SQL Server 2008 (10.0.x) e versioni successive.

Quando TRUE, ONo 1, la tabella designata è abilitata per il formato di archiviazione vardecimal . Quando FALSE, OFFo 0, la tabella non è abilitata per il formato di archiviazione vardecimal . Il formato di archiviazione vardecimal può essere abilitato solo quando il database è abilitato per il formato di archiviazione vardecimal tramite sp_db_vardecimal_storage_format. In SQL Server 2008 (10.0.x) e versioni successive il formato di archiviazione vardecimal è deprecato. Usare ROW invece la compressione. Per ulteriori informazioni, vedi Compressione dei dati. Il valore predefinito è 0.

[ @OptionValue = ] 'OptionValue'

Specifica se la @OptionName è abilitata (TRUE, ONo 1) o disabilitata (FALSE, OFFo 0). @OptionValue è varchar(12), senza impostazione predefinita. @OptionValue non fa distinzione tra maiuscole e minuscole.

Per l'opzione text in row, i valori di opzione validi sono 0, ON, OFFo un numero intero compreso tra 24 e 7000. Quando @OptionValue è , il limite predefinito è ON256 byte.

Valori del codice restituito

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

Osservazioni:

sp_tableoption può essere usato solo per impostare i valori delle opzioni per le tabelle definite dall'utente. Per visualizzare le proprietà della tabella, usare OBJECTPROPERTY o eseguire una query sys.tables.

L'opzione text in row in sp_tableoption può essere abilitata o disabilitata solo nelle tabelle che contengono colonne di testo. Se la tabella non contiene una colonna di testo, SQL Server genera un errore.

Quando l'opzione text in row è abilitata, il parametro @OptionValue consente agli utenti di specificare le dimensioni massime da archiviare in una riga per un BLOB. I possibili valori sono compresi tra 24 e 7000 byte. Il valore predefinito è 256 byte.

le stringhe text, ntext o image vengono archiviate nella riga di dati se si applicano le condizioni seguenti:

  • Il testo nella riga è abilitato.
  • La lunghezza della stringa è più breve del limite specificato in @OptionValue.
  • Nella riga di dati è disponibile spazio sufficiente.

Quando le stringhe BLOB vengono archiviate nella riga di dati, la lettura e la scrittura di stringhe di testo, ntext o image possono essere veloci quanto la lettura o la scrittura di stringhe di caratteri e binari. SQL Server non deve accedere a pagine separate per leggere o scrivere la stringa BLOB.

Se una stringa di tipo text, ntext o image è maggiore del limite specificato o dello spazio disponibile nella riga, i puntatori vengono archiviati nella riga. 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, SQL Server usa solo il numero di byte necessari per archiviare la stringa o il puntatore.

Le stringhe BLOB esistenti non vengono convertite immediatamente quando il testo nella riga è abilitato per la prima volta. ma solo in fase di aggiornamento. Analogamente, quando il limite di opzioni di testo nella riga viene aumentato, le stringhe text, ntext o image già presenti nella riga di dati non vengono convertite in modo da rispettare il nuovo limite fino al momento in cui vengono aggiornate.

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 una tabella dal formato di archiviazione vardecimal al formato di archiviazione decimale normale, il database deve trovarsi nel modello di recupero SIMPLE. La modifica del modello di recupero interromperà la catena di log a scopo di backup, pertanto è necessario creare un backup completo del database dopo aver rimosso il formato di archiviazione vardecimal da una tabella.

Se si converte una colonna di tipo di dati LOB esistente (text, ntext o image) in tipi di valori di grandi dimensioni (varchar(max), nvarchar(max)o varbinary(max)) e la maggior parte delle istruzioni non fa riferimento alle colonne di tipo valore di grandi dimensioni nell'ambiente, valutare la possibilità di modificare large_value_types_out_of_row per 1 ottenere prestazioni ottimali. Quando il valore dell'opzione large_value_types_out_of_row viene modificato, i valori varchar(max), nvarchar(max), varbinary(max)e xml non vengono convertiti immediatamente. L'archiviazione delle stringhe viene modificata man mano che vengono aggiornate in un secondo momento. Qualsiasi nuovo valore inserito in una tabella viene archiviato in base all'opzione di tabella attiva. Per ottenere risultati immediati, creare una copia dei dati e quindi ripopolare la tabella dopo aver modificato l'impostazione large_value_types_out_of_row o aggiornare ogni colonna di tipi di valore di piccole e medie dimensioni in modo che l'archiviazione delle stringhe venga modificata con l'opzione tabella in vigore. Provare a ricompilare gli indici nella tabella dopo l'aggiornamento o il ripopolamento per ridurre la tabella.

Autorizzazioni

Per eseguire sp_tableoption è necessaria ALTER l'autorizzazione per la tabella.

Esempi

R. Archiviare i dati XML dalla riga

Nell'esempio seguente viene specificato che i dati xml nella tabella devono essere archiviati all'esterno HumanResources.JobCandidate della riga.

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

B. Abilitare il formato di archiviazione vardecimal in una tabella

Nell'esempio seguente la Production.WorkOrderRouting tabella viene modificata per archiviare il tipo di dati decimal nel formato di archiviazione 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 'AdventureWorks2022', 'ON';
GO
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting',
   'vardecimal storage format', 'ON';

Contenuto correlato