Partilhar via

sp_tableoption (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

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

Importante

A funcionalidade de texto na linha será removida numa futura versão do SQL Server. Para armazenar dados de grande valor, recomendamos que utilize os tipos de dados varchar(max), nvarchar(max) e varbinary(max ).

Transact-SQL convenções de sintaxe

Sintaxe

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

Arguments

[ @TableNamePattern = ] N'PadrãoNomeDeMesa'

O nome qualificado ou não qualificado de uma tabela de base de dados definida pelo utilizador. @TableNamePattern é nvarchar(776), sem padrão. Se for fornecido um nome de tabela totalmente qualificado, incluindo um nome de base de dados, o nome da base de dados deve ser o nome da base de dados atual. As opções de mesa para várias mesas não podem ser definidas ao mesmo tempo.

[ @OptionName = ] 'NomeDeopção'

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

Valor Description
table lock on bulk load Quando desativado (o padrão), faz com que o processo de carregamento em massa nas tabelas definidas pelo utilizador obtenha bloqueios de linha. Quando ativado, faz com que os processos de carregamento em massa em tabelas definidas pelo utilizador obtenham um bloqueio de atualização em massa.
insert row lock Não há mais suporte.

Esta opção não tem efeito no comportamento de bloqueio do SQL Server e está incluída apenas para compatibilidade de scripts e procedimentos existentes.
text in row Quando OFF ou 0 (desativado, o padrão), não altera o comportamento atual e não há BLOB em linha.

Quando especificado e @OptionValue é ON (ativado) ou um valor inteiro de 24 através 7000de , novas cadeias de texto, texto n ou imagem são armazenadas diretamente na linha de dados. Todos os dados BLOB (binary large object: text, ntext, ou image) existentes são alterados para texto em formato de linha quando o valor BLOB é atualizado. Para obter mais informações, consulte Observações.
large value types out of row 1 = varchar(max), nvarchar(max),varbinary(max), xml e grandes colunas de tipo definido pelo utilizador (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 grandes de UDT são armazenados diretamente na linha de dados, até um limite de 8.000 bytes e desde que o valor possa caber no registo. Se o valor não caber no registo, um ponteiro é armazenado na linha e o restante é guardado fora da linha no espaço de armazenamento LOB. 0 é o valor padrão.

O tipo definido pelo utilizador grande (UDT) aplica-se a: SQL Server 2008 (10.0.x) e posteriores.

Use a TEXTIMAGE_ON opção CRIAR TABELA 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, ON, ou 1, a tabela designada está ativada para o formato de armazenamento vardecimal . Quando FALSE, OFF, ou 0, a tabela não está ativada para o formato de armazenamento vardecimal . O formato de armazenamento vardecimal só pode ser ativado quando a base de dados está ativada para o formato vardecimal usando sp_db_vardecimal_storage_format. No SQL Server 2008 (10.0.x) e posteriores, o formato de armazenamento vardecimal está obsoleto. Usa ROW compressão em vez disso. Para obter mais informações, consulte Compactação de dados. 0 é o valor padrão.

[ @OptionValue = ] 'ValorOption'

Especifica se o @OptionName está ativado (TRUE, ON, ou 1) ou desativado (FALSE, OFF, ou 0). @OptionValue é varchar(12), sem padrão. @OptionValue não distingue minúsculas.

Para a opção texto na linha, os valores válidos das opções são 0, ON, OFF, ou um inteiro de 24 até 7000. Quando @OptionValue é ON, o limite por defeito é de 256 bytes.

Valores de código de retorno

0 (sucesso) ou número de erro (falha).

Observações

sp_tableoption podem ser usados apenas para definir valores de opção para tabelas definidas pelo utilizador. Para mostrar as propriedades da tabela, use OBJECTPROPERTY ou consulta sys.tables.

A opção texto na linha em sp_tableoption pode ser ativada ou desativada apenas em tabelas que contenham colunas de texto. Se a tabela não tiver uma coluna de texto, o SQL Server gera um erro.

Quando a opção texto na linha está ativada, o parâmetro @OptionValue permite aos utilizadores especificar o tamanho máximo a armazenar numa linha para um BLOB. O padrão é 256 bytes, mas os valores podem variar entre 24 e 7000 bytes.

Texto, ntext ou cadeias de imagens são armazenadas na linha de dados se aplicarem as seguintes condições:

  • O texto na linha está ativado.
  • O comprimento da corda é mais curto do que o limite especificado em @OptionValue.
  • Há espaço suficiente disponível na linha de dados.

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

Se um texto, ntext ou cadeia de imagem for maior do que o limite especificado ou o espaço disponível na linha, os ponteiros são armazenados na linha. As condições para armazenar as cadeias BLOB na linha aplicam-se, no entanto: deve haver espaço suficiente na linha de dados para armazenar os ponteiros.

As strings e ponteiros BLOB armazenados na linha de uma tabela são tratados de forma semelhante às strings de comprimento variável. O SQL Server utiliza apenas o número de bytes necessários para armazenar a string ou o apontador.

As cadeias BLOB existentes não são convertidas imediatamente quando o texto na linha é ativado 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 strings de texto, ntext ou imagem já presentes na linha de dados não são convertidas para cumprir o novo limite até serem atualizadas.

Observação

Desabilitar a opção texto na linha ou reduzir o limite dessa opção exigirá a conversão de todos os BLOBs; portanto, o processo pode ser longo, dependendo do número de cadeias BLOB que devem ser convertidas. A mesa é bloqueada durante o processo de conversão.

Uma variável de tabela, incluindo uma função que devolve uma variável de tabela, tem automaticamente a opção texto na linha ativada com um limite padrão em linha de 256. Esta opção não pode ser alterada.

A opção texto na linha suporta as funções TEXTPTR, WRITETEXT, UPDATETEXT e READTEXT. Os utilizadores podem ler partes de um BLOB com a função SUBSTRING(), mas devem lembrar-se que os ponteiros de texto em linha têm limites de duração e número diferentes dos outros ponteiros de texto.

Para mudar uma tabela do formato vardecimal de armazenamento para o formato decimal normal, a base de dados deve estar no modelo de recuperação SIMPLE. Mudar o modelo de recuperação vai quebrar a cadeia de logs para fins de backup, por isso deve criar um backup completo da base de dados depois de remover o formato de armazenamento vardecimal de uma tabela.

Se estiveres a converter uma coluna de tipo de dado LOB existente (texto, ntext ou imagem) para tipos de valor pequeno a médio grande (varchar(max),nvarchar(max) ou varbinary(max)), e a maioria das instruções não faz referência às colunas de tipos de valor grande no teu ambiente, considera mudar large_value_types_out_of_row para 1 obter desempenho ótimo. 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 cordas é alterado à medida que são atualizadas mais tarde. Quaisquer novos valores inseridos numa tabela são armazenados de acordo com a opção de tabela em vigor. Para resultados imediatos, pode fazer uma cópia dos dados e depois repreencher a tabela após alterar a definição de large_value_types_out_of_row ou atualizar cada coluna de tipos de valor pequenos a médios para si própria, de modo a que o armazenamento das strings seja alterado com a opção de tabela em vigor. Considere reconstruir os índices da tabela após a atualização ou repovoamento para condensar a tabela.

Permissions

Para executar sp_tableoption , requer ALTER permissão na mesa.

Examples

A. Armazenar dados XML fora da linha

O exemplo seguinte especifica que os dados xml na HumanResources.JobCandidate tabela devem ser armazenados fora da linha.

USE AdventureWorks2022;
GO

EXECUTE sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

B. Ativar o formato de armazenamento vardecimal numa tabela

O exemplo seguinte 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
EXECUTE sp_db_vardecimal_storage_format 'AdventureWorks2022', 'ON';
GO

USE AdventureWorks2022;
GO

EXECUTE sp_tableoption 'Production.WorkOrderRouting', 'vardecimal storage format', 'ON';

Conteúdo relacionado

  • Last updated on