sp_tableoption (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

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

Importante

O recurso de texto em linha será removido em uma versão futura do SQL Server. Para armazenar dados de grande valor, 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 = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Argumentos

@TableNamePattern [ = ] N'TableNamePattern'

O nome qualificado ou não qualificado de uma tabela de banco de dados definida pelo usuário. @TableNamePattern é nvarchar(776), sem padrão. 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. As opções de tabela para várias tabelas não podem ser definidas ao mesmo tempo.

[ @OptionName = ] 'Nome_do_opção'

Um nome de opção de tabela. @OptionName é varchar(35) e pode ser um dos seguintes valores.

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 do SQL Server e é incluída apenas para compatibilidade de scripts e procedimentos existentes.
text in row Quando OFF ou 0 (desabilitado, o padrão), ele não altera o comportamento atual e não há BLOB na linha.

Quando especificado e @OptionValue estiver ON (habilitado) ou um valor inteiro de até , novas cadeias de caracteres de texto, 7000ntext ou imagem serão armazenadas diretamente na linha de 24 dados. Todos os dados BLOB (objeto binário grande: texto, ntext ou imagem) existentes são alterados para texto em formato de linha quando o valor BLOB é atualizado. Para obter mais informações, consulte Comentários.
large 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 armazenadas fora da 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 couber no registro, um ponteiro será armazenado na linha e o restante será armazenado fora da linha no espaço de armazenamento do LOB. O valor padrão é 0.

O tipo definido pelo usuário grande (UDT) aplica-se a: SQL Server 2008 (10.0.x) e posterior.

Use a TEXTIMAGE_ON opção de CREATE TABLE para especificar um local para armazenamento de grandes tipos de dados.
formato de armazenamento vardecimal Aplica-se a: SQL Server 2008 (10.0.x) e posterior.

Quando TRUE, ou 1, ONa tabela designada estiver habilitada para o formato de armazenamento vardecimal. Quando FALSE, ou 0, OFFa tabela não está habilitada para o formato de armazenamento vardecimal. O formato de armazenamento vardecimal pode ser habilitado somente quando o banco de dados está 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 ROW a compactação. Para obter mais informações, consulte Compactação de dados. O valor padrão é 0.

[ @OptionValue = ] 'OptionValue'

Especifica se o @OptionName está habilitado (, ou ) ou desabilitado (FALSETRUE, OFFONou 01). @OptionValue é varchar(12), sem padrão. @OptionValue diferencia maiúsculas de minúsculas.

Para a opção de texto na linha, os valores de opção válidos são 0, , OFFONou um inteiro de 24 até 7000. Quando @OptionValue é , o limite padrão é ONde 256 bytes.

Valores do código de retorno

0 (êxito) ou número do erro (falha).

Comentários

sp_tableoption pode ser usado 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 de texto na linha em sp_tableoption pode ser habilitada ou desabilitada somente em tabelas que contenham colunas de texto. Se a tabela não tiver uma coluna de texto, o SQL Server gerará um erro.

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. O padrão é 256 bytes, mas valores podem variar de 24 a 7000 bytes.

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

  • O texto na linha está habilitado.
  • O comprimento da cadeia de caracteres é menor do que o limite especificado em @OptionValue.
  • Há espaço suficiente disponível na linha de dados.

Quando as cadeias de caracteres BLOB são armazenadas na linha de dados, a leitura e a gravação das cadeias de caracteres de texto, ntext ou imagem podem ser tão rápidas quanto ler ou gravar cadeias de caracteres e binárias. O SQL Server não precisa acessar páginas separadas para ler ou gravar a cadeia de caracteres BLOB.

Se uma cadeia de caracteres de texto, ntext ou imagem for maior do 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. O SQL Server usa apenas o número de bytes necessários para armazenar a cadeia de caracteres ou o ponteiro.

As cadeias de caracteres BLOB existentes não são convertidas imediatamente quando o texto na linha é habilitado pela primeira vez. As cadeias só são convertidas quando são atualizadas. Da mesma forma, quando o limite de opção de texto na linha é aumentado, as cadeias de caracteres de texto, ntext ou imagem que já estão na linha de dados não são convertidas para aderir ao novo limite até o momento em que são 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. Esta opção não pode ser alterada.

A opção texto na linha oferece 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 do formato de armazenamento vardecimal de volta para o formato de armazenamento decimal normal, o banco de dados deve estar no modelo de recuperação SIMPLE. Alterar o modo de recuperação quebrará a cadeia de logs para fins de backup, portanto, você deve criar um backup completo do banco de dados depois de remover o formato de armazenamento vardecimal de uma tabela.

Se você estiver convertendo uma coluna de tipo de dados LOB existente (texto, ntext ou imagem) em tipos de valor pequeno a médio grande (varchar(max), nvarchar(max) ou varbinary(max)) e a maioria das instruções não fizer referência às colunas de tipo de valor grande em seu ambiente, considere alterar large_value_types_out_of_row para 1 obter o desempenho ideal. Quando o valor da opção large_value_types_out_of_row é alterado, os valores existentes varchar(max), nvarchar(max), varbinary(max) e xml não são convertidos imediatamente. O armazenamento das cadeias de caracteres é alterado à medida que são atualizadas posteriormente. Qualquer novo valor inserido em uma tabela é armazenado de acordo com a opção da tabela em vigor. Para obter resultados imediatos, faça uma cópia dos dados e, em seguida, preencha novamente a tabela depois de alterar a configuração de large_value_types_out_of_row ou atualize cada coluna de tipos de valor pequeno a médio grande 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 requer ALTER permissão na tabela.

Exemplos

R. Armazenar dados XML fora da linha

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

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

B. Habilitar o formato de armazenamento vardecimal em uma tabela

O exemplo a seguir modifica a Production.WorkOrderRouting tabela para armazenar o tipo de dados decimal no formato de armazenamento 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';

Conteúdo relacionado