sp_tableoption (Transact-SQL)

Artigo
03/03/2023
7 minutos para ler

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Define valores de opção para tabelas definidas pelo usuário. sp_tableoption pode ser usado para controlar o comportamento em linha de tabelas com colunas de tipo varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image ou grandes definidas pelo usuário.

Importante

O recurso texto em linha será removido em uma versão futura do SQL Server. Para armazenar dados de valor grande, recomendamos que você use os tipos de dados varchar(max), nvarchar(max) e varbinary(max ).

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Argumentos

[ @TableNamePattern =] 'table'
É o nome qualificado ou não qualificado de uma tabela de banco de dados definida pelo usuário. 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. Não se pode definir opções de tabela para várias tabelas ao mesmo tempo. table é nvarchar(776), sem padrão.

[ @OptionName = ] 'option_name'
É um nome de opção de tabela. option_name é varchar(35), sem nenhum padrão de NULL. option_name pode ser um dos valores a seguir.

Valor	Descrição
table 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. 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.
insert row lock	Não tem mais suporte. Essa opção não tem efeito sobre o comportamento de bloqueio de SQL Server e está incluída apenas para compatibilidade de scripts e procedimentos existentes.
text in row	Quando OFF ou 0 (desabilitado, o padrão), não altera o comportamento atual e não há nenhum BLOB na linha. Quando especificado e @OptionValue é ON (habilitado) ou um valor inteiro de 24 a 7000, novas cadeias de caracteres text, ntext ou image são armazenadas diretamente na linha de dados. Todos os BLOB existentes (objeto binário grande: texto, ntext ou dados de imagem ) serão alterados para texto no formato de linha quando o valor blob for atualizado. Para obter mais informações, consulte Comentários.
large value types out of row	1 = as colunas varchar(max), nvarchar(max), varbinary(max), xml e UDT (tipo grande definido pelo usuário) na tabela são armazenadas fora de linha, com um ponteiro de 16 bytes para a raiz. 0 = varchar(max), nvarchar(max), varbinary(max), xml e valores UDT grandes são armazenados diretamente na linha de dados, até um limite de 8.000 bytes e desde que o valor possa caber no registro. 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. O valor padrão é 0. O UDT (tipo definido pelo usuário) grande aplica-se a: SQL Server 2008 (10.0.x) e posterior. Use a opção TEXTIMAGE_ON de CREATE TABLE para especificar um local para armazenamento de tipos de dados grandes.
formato de armazenamento vardecimal	Aplica-se a: SQL Server 2008 (10.0.x) e posterior. Quando TRUE, ON ou 1, a tabela designada é habilitada para o formato de armazenamento vardecimal. Quando FALSE, OFF ou 0, a tabela designada não é habilitada para o formato de armazenamento vardecimal. O formato de armazenamento Vardecimal só pode ser habilitado quando o banco de dados tiver sido habilitado para o formato de armazenamento vardecimal usando sp_db_vardecimal_storage_format. No SQL Server 2008 (10.0.x) e posterior, o formato de armazenamento vardecimal foi preterido. Em vez disso, use compactação ROW. Para saber mais, veja Data Compression. O valor padrão é 0.

[ @OptionValue =] 'value'
É se o option_name está habilitado (TRUE, ON ou 1) ou desabilitado (FALSE, OFF ou 0). value é varchar(12), sem padrão. value não diferencia maiúsculas de minúsculas.

Para a opção text in row, valores válidos são 0, ON, OFF ou um inteiro de 24 a 7000. Quando o valor é ON, o limite assume como padrão 256 bytes.

Valores do código de retorno

0 (êxito) ou um número de erro (falha)

Comentários

Use sp_tableoption apenas para definir valores de opção para tabelas definidas pelo usuário. Para exibir as propriedades da tabela, use OBJECTPROPERTY ou consulte 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. Se a tabela não tiver uma coluna de texto, SQL Server gerará um erro.

Quando a opção text in row está habilitada, o @OptionValue parâmetro permite que os usuários especifiquem o tamanho máximo a ser armazenado em uma linha para um BLOB. O padrão é 256 bytes, mas valores podem variar de 24 a 7000 bytes.

As cadeias de caracteres text, ntext ou image serão armazenadas na linha de dados se as seguintes condições se aplicarem:

A opção texto em linha está habilitada.
O comprimento da cadeia de caracteres é menor que o limite especificado em @OptionValue
Há bastante espaço disponível na linha de dados.

Quando as cadeias de caracteres BLOB são armazenadas na linha de dados, ler e gravar as cadeias de caracteres text, ntext ou image pode ser tão rápido quanto ler ou gravar caracteres e cadeias de caracteres binárias. SQL Server não precisa acessar páginas separadas para ler ou gravar a cadeia de caracteres BLOB.

Se uma cadeia de caracteres text, ntext ou image for maior que o limite especificado ou o espaço disponível na linha, os ponteiros serão armazenados na linha. 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.

As cadeias e ponteiros BLOB armazenados na linha de uma tabela são tratados de modo semelhante a cadeias de comprimento variável. SQL Server usa apenas o número de bytes necessários para armazenar a cadeia de caracteres ou o ponteiro.

As cadeias BLOB existentes não são convertidas imediatamente, quando text in row é habilitada pela primeira vez. As cadeias só são convertidas quando são atualizadas. Da mesma forma, quando o limite de opção de texto em linha for aumentado, as cadeias de caracteres text, ntext ou image já na linha de dados não serão convertidas para aderir ao novo limite até o momento em que forem atualizadas.

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. A tabela é bloqueada durante o processo de conversão.

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. Essa opção não pode ser alterada.

A opção text in row dá suporte às funções TEXTPTR, WRITETEXT, UPDATETEXT e READTEXT. 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.

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. 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.

Se você estiver convertendo uma coluna de tipo de dados LOB existente (text, ntext ou image) em tipos de valor grande de pequeno a médio porte (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. Quando o valor da opção large_value_types_out_of_row é alterado, os valores varchar(max), nvarchar(max), varbinary(max) e xml existentes não são convertidos imediatamente. O armazenamento da cadeia de caracteres é alterado conforme é atualizado subsequentemente. Qualquer novo valor inserido em uma tabela é armazenado de acordo com a opção da tabela em vigor. Para resultados imediatos, faça uma cópia dos dados e, em seguida, repovoe a tabela depois de alterar a configuração large_value_types_out_of_row ou atualize cada coluna de tipos de valor grande pequeno a médio para si mesma para que o armazenamento das cadeias de caracteres seja alterado com a opção de tabela em vigor. Considere recompilar os índices na tabela após a atualização ou preencher novamente para condensar a tabela.

Permissões

Para executar sp_tableoption é necessário ter permissão ALTER na tabela.

Exemplos

a. Armazenando dados xml fora da linha

O exemplo a seguir especifica que os dados xml na HumanResources.JobCandidate tabela sejam armazenados fora da linha.

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

B. Habilitando o formato de armazenamento vardecimal em uma tabela

O exemplo a seguir modifica a Production.WorkOrderRouting tabela para armazenar o decimal tipo de dados no vardecimal formato de armazenamento.

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ém

sys.tables (Transact-SQL)
OBJECTPROPERTY (Transact-SQL)
Procedimentos armazenados do sistema (Transact-SQL)
Procedimentos armazenados do Mecanismo de Banco de Dados (Transact-SQL)