sp_tableoption (Transact-SQL)sp_tableoption (Transact-SQL)

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Define valores de opção para tabelas definidas pelo usuário.Sets option values for user-defined tables. sp_tableoption pode ser usado para controlar o comportamento na linha de tabelas com varchar (max) , nvarchar (max) , varbinary (max) , XML, Text, ntext, Imageou colunas de tipo definido pelo usuário grande.sp_tableoption can be used to control the in-row behavior of tables with varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, or large user-defined type columns.

Importante

O recurso text in row será removido em uma versão futura do SQL ServerSQL Server.The text in row feature will be removed in a future version of SQL ServerSQL Server. Para armazenar dados de valor grande, recomendamos que você use os tipos de dados varchar (max) , nvarchar (max) e varbinary (max) .To store large value data, we recommend that you use of the varchar(max), nvarchar(max) and varbinary(max) data types.

Ícone de link do tópico Convenções de sintaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

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

ArgumentosArguments

[@TableNamePattern =] 'tabela'[ @TableNamePattern =] 'table'
É o nome qualificado ou não qualificado de uma tabela de banco de dados definida pelo usuário.Is the qualified or nonqualified name of a user-defined database table. Se um nome de tabela totalmente qualificado, incluindo um nome de banco de dados, for fornecido, o nome de banco de dados deve ser o nome do banco de dados atual.If a fully qualified table name, including a database name, is provided, the database name must be the name of the current database. Não se pode definir opções de tabela para várias tabelas ao mesmo tempo.Table options for multiple tables can not be set at the same time. Table é nvarchar (776) , sem padrão.table is nvarchar(776), with no default.

[@OptionName =] 'option_name'[ @OptionName = ] 'option_name'
É um nome de opção de tabela.Is a table option name. option_name é varchar (35) , sem nenhum padrão de NULL.option_name is varchar(35), with no default of NULL. option_name pode ser um dos valores a seguir.option_name can be one of the following values.

ValorValue DescriçãoDescription
table lock on bulk loadtable lock on bulk load Quando desabilitado (o padrão), faz com que o processo de carregamento em massa em tabelas definidas pelo usuário obtenha bloqueio de linhas.When disabled (the default), it causes the bulk load process on user-defined tables to obtain row locks. Quando habilitado, faz com que os processos de carregamento em massa em tabelas definidas pelo usuário obtenham um bloqueio de atualização em massa.When enabled, it causes the bulk load processes on user-defined tables to obtain a bulk update lock.
insert row lockinsert row lock Não tem mais suporte.No longer supported.

Essa opção não tem nenhum efeito no comportamento de bloqueio do SQL ServerSQL Server e só é incluída para a compatibilidade de scripts e procedimentos existentes.This option has no effect on the locking behavior of SQL ServerSQL Server and is included only for compatibility of existing scripts and procedures.
text in rowtext in row Quando OFF ou 0 (desabilitado, o padrão), não altera o comportamento atual e não há nenhum BLOB na linha.When OFF or 0 (disabled, the default), it does not change current behavior, and there is no BLOB in row.

Quando especificado e @OptionValue está ativado (habilitado) ou um valor inteiro de 24 a 7000, as novas cadeias de caracteres Text, ntextou Image são armazenadas diretamente na linha de dados.When specified and @OptionValue is ON (enabled) or an integer value from 24 through 7000, new text, ntext, or image strings are stored directly in the data row. Todo o BLOB existente (objeto binário grande: texto, ntextou dados de imagem ) será alterado para texto no formato de linha quando o valor de blob for atualizado.All existing BLOB (binary large object: text, ntext, or image data) will be changed to text in row format when the BLOB value is updated. Para obter mais informações, consulte Comentários.For more information, see Remarks.
large value types out of rowlarge value types out of row 1 = varchar (max) , nvarchar (max) , varbinary (max) , XML e grandes colunas de tipo definido pelo usuário (UDT) na tabela são armazenados fora da linha, com um ponteiro de 16 bytes para a raiz.1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.

0 = varchar (max) , nvarchar (max) , varbinary (max) , XML e valores de UDT grandes são armazenados diretamente na linha de dados, até um limite de 8000 bytes e desde que o valor possa caber no registro.0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. Se o valor não se ajustar ao registro, um ponteiro será armazenado na linha e o restante será armazenado fora da linha no espaço de armazenamento de LOB.If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. O valor padrão é 0.0 is the default value.

O UDT (tipo definido pelo usuário) grande aplica-se a: SQL Server 2008SQL Server 2008 e posterior.Large user-defined type (UDT) applies to: SQL Server 2008SQL Server 2008 and later.

Use a opção TEXTIMAGE_ON de CREATE TABLE para especificar um local para armazenamento de tipos de dados grandes.Use the TEXTIMAGE_ON option of CREATE TABLE to specify a location for storage of large data types.
formato de armazenamento vardecimalvardecimal storage format Aplica-se a: SQL Server 2008SQL Server 2008 e posterior.Applies to: SQL Server 2008SQL Server 2008 and later.

Quando TRUE, ON ou 1, a tabela designada é habilitada para o formato de armazenamento vardecimal.When TRUE, ON, or 1, the designated table is enabled for vardecimal storage format. Quando FALSE, OFF ou 0, a tabela designada não é habilitada para o formato de armazenamento vardecimal.When FALSE, OFF, or 0, the table is not enabled for vardecimal storage format. O formato de armazenamento vardecimal só poderá ser habilitado quando o banco de dados tiver sido habilitado para o formato de armazenamento vardecimal usando sp_db_vardecimal_storage_format.Vardecimal storage format can be enabled only when the database has been enabled for vardecimal storage format by using sp_db_vardecimal_storage_format. No SQL Server 2008SQL Server 2008 e posterior, o formato de armazenamento vardecimal é preterido.In SQL Server 2008SQL Server 2008 and later, vardecimal storage format is deprecated. Em vez disso, use compactação ROW.Use ROW compression instead. Para saber mais, veja Data Compression.For more information, see Data Compression. O valor padrão é 0.0 is the default value.

[@OptionValue =] 'valor'[ @OptionValue =] 'value'
É se a option_name está habilitada (true, on ou 1) ou se está desabilitada (false, off ou 0).Is whether the option_name is enabled (TRUE, ON, or 1) or disabled (FALSE, OFF, or 0). o valor é varchar (12) , sem padrão.value is varchar(12), with no default. o valor não diferencia maiúsculas de minúsculas.value is case insensitive.

Para a opção text in row, valores válidos são 0, ON, OFF ou um inteiro de 24 a 7000.For the text in row option, valid option values are 0, ON, OFF, or an integer from 24 through 7000. Quando o valor está ativado, o limite padrão é de 256 bytes.When value is ON, the limit defaults to 256 bytes.

Valores do código de retornoReturn Code Values

0 (êxito) ou um número de erro (falha)0 (success) or error number (failure)

RemarksRemarks

Use sp_tableoption apenas para definir valores de opção para tabelas definidas pelo usuário.sp_tableoption can be used only to set option values for user-defined tables. Para exibir as propriedades da tabela, use OBJECTPROPERTY ou consulta sys. Tables.To display table properties, use OBJECTPROPERTY or query sys.tables.

A opção text in row em sp_tableoption só pode ser habilitada ou desabilitada em tabelas que contêm colunas de texto.The text in row option in sp_tableoption can be enabled or disabled only on tables that contain text columns. Se a tabela não tiver uma coluna de texto, o SQL ServerSQL Server gerará um erro.If the table does not have a text column, SQL ServerSQL Server raises an error.

Quando a opção texto na linha está habilitada, o parâmetro @OptionValue permite que os usuários especifiquem o tamanho máximo a ser armazenado em uma linha para um BLOB.When the text in row option is enabled, the @OptionValue parameter allows users to specify the maximum size to be stored in a row for a BLOB. O padrão é 256 bytes, mas valores podem variar de 24 a 7000 bytes.The default is 256 bytes, but values can range from 24 through 7000 bytes.

as cadeias de caracteres Text, ntextou Image são armazenadas na linha de dados se as seguintes condições se aplicarem:text, ntext, or image strings are stored in the data row if the following conditions apply:

  • A opção texto em linha está habilitada.text in row is enabled.

  • O comprimento da cadeia de caracteres é menor que o limite especificado em @OptionValueThe length of the string is shorter than the limit specified in @OptionValue

  • Há bastante espaço disponível na linha de dados.There is enough space available in the data row.

Quando as cadeias de caracteres de BLOB são armazenadas na linha de dados, a leitura e a gravação das cadeias de caracteres Text, ntextou Image podem ser tão rápidas quanto a leitura ou a gravação de cadeias de caractere e binárias.When BLOB strings are stored in the data row, reading and writing the text, ntext, or image strings can be as fast as reading or writing character and binary strings. O SQL ServerSQL Server não precisa acessar páginas separadas para ler ou gravar a cadeia de caracteres BLOB.SQL ServerSQL Server does not have to access separate pages to read or write the BLOB string.

Se uma cadeia de caracteres Text, ntextou Image for maior do que o limite especificado ou o espaço disponível na linha, os ponteiros serão armazenados na linha em vez disso.If a text, ntext, or image string is larger than the specified limit or the available space in the row, pointers are stored in the row instead. As condições para armazenar as cadeias de caracteres BLOB na linha ainda assim se aplicam: deve haver espaço suficiente na linha de dados para conter os ponteiros.The conditions for storing the BLOB strings in the row nonetheless apply: There must be enough space in the data row to hold the pointers.

As cadeias e ponteiros BLOB armazenados na linha de uma tabela são tratados de modo semelhante a cadeias de comprimento variável.BLOB strings and pointers stored in the row of a table are treated similarly to variable-length strings. O SQL ServerSQL Server usa só o número de bytes exigido para armazenar a cadeia de caracteres ou o ponteiro.SQL ServerSQL Server uses only the number of bytes required to store the string or the pointer.

As cadeias BLOB existentes não são convertidas imediatamente, quando text in row é habilitada pela primeira vez.Existing BLOB strings are not converted immediately when text in row is first enabled. As cadeias só são convertidas quando são atualizadas.The strings are converted only when they are updated. Da mesma forma, quando o limite da opção texto em linha for aumentado, as cadeias de caracteres Text, ntextou Image já existentes na linha de dados não serão convertidas para aderir ao novo limite até a hora em que são atualizadas.Likewise, when the text in row option limit is increased, the text, ntext, or image strings already in the data row will not be converted to adhere to the new limit until the time they are updated.

Observação

Desabilitar a opção text in row ou reduzir o limite da opção exigirá a conversão de todas as BLOBs; assim, o processo pode ser longo, dependendo do número de cadeias BLOB que devem ser convertidas.Disabling the text in row option or reducing the limit of the option will require the conversion of all BLOBs; therefore, the process can be long, depending on the number of BLOB strings that must be converted. A tabela é bloqueada durante o processo de conversão.The table is locked during the conversion process.

Uma variável de tabela, incluindo uma função que retorna uma variável de tabela, tem a opção text in row automaticamente habilitada com um limite padrão embutido de 256.A table variable, including a function that returns a table variable, automatically has the text in row option enabled with a default inline limit of 256. Essa opção não pode ser alterada.This option cannot be changed.

A opção texto na linha dá suporte às funções TEXTPTR, WRITETEXT, UPDATETEXT e READTEXT.The text in row option supports the TEXTPTR, WRITETEXT, UPDATETEXT, and READTEXT functions. Os usuários podem ler partes de um BLOB com a função SUBSTRING(), mas devem se lembrar de que os ponteiros de texto na linha têm limites de número e duração diferentes de outros ponteiros de texto.Users can read parts of a BLOB with the SUBSTRING() function, but must remember that in-row text pointers have different duration and number limits from other text pointers.

Para alterar uma tabela de formato de armazenamento vardecimal de volta ao formato de armazenamento decimal normal, o banco de dados deve estar em modo de recuperação SIMPLE.To change a table from vardecimal storage format back to the normal decimal storage format, the database must be in SIMPLE recovery mode. A alteração do modo de recuperação interromperá a cadeia de logs para efeito de backup, portanto, é necessário criar um backup de banco de dados completo depois de remover o formato de armazenamento vardecimal de uma tabela.Changing the recovery mode will break the log chain for backup purposes, therefore you should create a full database backup after removing the vardecimal storage format from a table.

Se você estiver convertendo uma coluna de tipo de dados LOB (text, ntext ou image) existente em tipos de valor grande pequenos para médio (varchar (max), nvarchar (max) ou varbinary (max)) e a maioria das instruções não referenciar as colunas de tipo de valor grande em seu ambiente, considere alterar large_value_types_out_of_row para 1 para obter o desempenho ideal.If you are converting an existing LOB data type column (text, ntext, or image) to small-to-medium large value types (varchar(max), nvarchar(max), or varbinary(max)), and most statements do not reference the large value type columns in your environment, consider changing large_value_types_out_of_row to 1 to gain optimal performance. Quando o valor da opção de large_value_types_out_of_row é alterado, os valores varchar (max), nvarchar (max), varbinary (max) e XML existentes não são convertidos imediatamente.When the large_value_types_out_of_row option value is changed, existing varchar(max), nvarchar(max), varbinary(max), and xml values are not immediately converted. O armazenamento da cadeia de caracteres é alterado conforme é atualizado subsequentemente.The storage of the strings is changed as they are subsequently updated. Qualquer novo valor inserido em uma tabela é armazenado de acordo com a opção da tabela em vigor.Any new values inserted into a table are stored according to the table option in effect. Para resultados imediatos, faça uma cópia dos dados e, em seguida, repopular a tabela depois de alterar a configuração de large_value_types_out_of_row ou atualizar cada coluna de tipos de valor grande de pequeno para médio para si mesma, para que o armazenamento das cadeias de caracteres seja alterado com a opção de tabela em vigor.For immediate results, either make a copy of the data and then repopulate the table after changing the large_value_types_out_of_row setting or update each small-to-medium large value types column to itself so that the storage of the strings is changed with the table option in effect. Considere recompilar os índices na tabela após a atualização ou preencher novamente para condensar a tabela.Consider rebuilding the indexes on the table after the update or repopulation to condense the table.

PermissõesPermissions

Para executar sp_tableoption é necessário ter permissão ALTER na tabela.To execute sp_tableoption requires ALTER permission on the table.

ExemplosExamples

A.A. Armazenando dados xml fora da linhaStoring xml data out of the row

O exemplo a seguir especifica que os dados XML na tabela HumanResources.JobCandidate sejam armazenados fora da linha.The following example specifies that the xml data in the HumanResources.JobCandidate table be stored out of row.

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

b.B. Habilitando o formato de armazenamento vardecimal em uma tabelaEnabling vardecimal storage format on a table

O exemplo a seguir modifica a tabela Production.WorkOrderRouting para armazenar o tipo de dados decimal no formato de armazenamento vardecimal.The following example modifies the Production.WorkOrderRouting table to store the decimal data type in the 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';  

Consulte tambémSee Also

sys.tables (Transact-SQL) sys.tables (Transact-SQL)
OBJECTPROPERTY (Transact-SQL) OBJECTPROPERTY (Transact-SQL)
Procedimentos armazenados do sistema (Transact-SQL) System Stored Procedures (Transact-SQL)
Mecanismo de Banco de Dados procedimentos (armazenados TRANSACT-SQL)Database Engine Stored Procedures (Transact-SQL)