sp_tableoption (Transact-SQL)

Artigo
09/12/2014

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

Importante
O recurso text in row será removido em uma versão futura do SQL Server. Para armazenar dados de valor grande, recomendamos usar tipos de dados varchar(max), nvarchar(max) e varbinary(max).

Ícone de vínculo de tópico Convenções de sintaxe 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, inclusive um nome de banco de dados, for fornecido, o nome do banco de dados deverá 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 o padrão de NULL. option_name pode ter 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. A estratégia de bloqueio do SQL Server é bloqueio de linha com possível promoção para bloqueio de página ou de tabela. Esta opção não tem nenhum efeito no comportamento de bloqueio do SQL Server e só é incluída para a 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 for ON (habilitado) ou um valor inteiro de 24 a 7000, novas cadeias de caracteres text, ntext ou image serão armazenadas diretamente na linha de dados. Todos os BLOBs existentes (objeto binário grande: dados text, ntext ou image) serão alterados para o formato text in row quando o valor do BLOB for 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 tipos definidos pelo usuário (UDT) 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 grandes valores de UDT são armazenados diretamente na linha de dados até um limite de 8000 bytes e contanto que o valor possa se ajustar 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. 0 é o valor padrão.
formato de armazenamento vardecimal	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. Para habilitar o formato de armazenamento vardecimal pode ser habilitado apenas quando o banco de dados tiver sido habilitado para esse formato por meio de sp_db_vardecimal_storage_format. No SQL Server 2008 e em versões posteriores, o formato de armazenamento vardecimal é substituído. Em vez disso, use compactação ROW. Para obter mais informações, consulte Compactação de dados. 0 é o valor padrão.

[ @OptionValue =] 'value'
Indica se 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 e minúsculas.

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

Valores de código de retorno

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

Comentários

sp_tableoption pode ser usado apenas para definir valores de opções de tabelas definidas pelo usuário. Para exibir propriedades de tabela, use OBJECTPROPERTY.

A opção text in row em sp_tableoption pode ser habilitada ou desabilitada apenas em tabelas que contêm colunas. Se a tabela não tiver uma coluna de texto, o SQL Server gerará um erro.

Quando a opção text in row está habilitada, o parâmetro @OptionValue permite que os usuários especifiquem o tamanho máximo a ser armazenado em uma linha de 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 armazenados na linha de dados se as seguintes condições se aplicarem:

text in row estiver habilitado.
O comprimento da cadeia de caracteres é mais curto que o limite especificado em @OptionValue
Há bastante espaço disponível na linha de dados.

Quando cadeias de caracteres BLOB são armazenadas na linha de dados, a leitura e a gravação das cadeias de caracteres text, ntext ou image podem ser tão rápidas quanto a leitura ou gravação de cadeias binárias ou de caracteres. O 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, no lugar delas serão armazenados ponteiros linha. As condições para armazenar as cadeias de caracteres BLOB na linha ainda assim se aplicam: É preciso ter espaço disponível 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 só o número de bytes exigido para armazenar a cadeia de caracteres ou o ponteiro.

As cadeias de caracteres BLOB existentes não são convertidas imediatamente quando text in row é habilitado pela primeira vez. As cadeias só são convertidas quando são atualizadas. Da mesma forma, quando o limite da opção text in row é aumentado, as cadeias de caracteres text, ntext ou image já existentes 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 todos os BLOBs; portanto, o processo pode ser longo, dependendo do número de cadeias de caracteres BLOB que devem ser convertidas. A tabela é bloqueada durante o processo de conversão.

Uma variável de tabela, inclusive 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 READTEX. 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. Alterar o modo de recuperação quebrará a cadeia de logs para efeito de backup, portanto, é preciso 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) para tipos de valores de pequenos para médios e grandes (varchar(max), nvarchar(max) ou varbinary(max)) e a maioria das instruções não se referir às colunas de tipo de valor grande em seu ambiente, mude large_value_types_out_of_row para 1 para obter o desempenho ideal. Quando você muda o valor da opção large_value_types_out_of_row, os valores varchar(max), nvarchar(max), varbinary(max) e xml não são convertidos imediatamente. O armazenamento das cadeias de caracteres é alterado de acordo com as atualizações subsequentes. Quaisquer valores novos inseridos em uma tabela são armazenados de acordo com a opção de tabela em efeito. Para obter resultados imediatos, faça uma cópia dos dados e preencha novamente a tabela depois de mudar a configuração large_value_types_out_of_row definindo ou atualizando cada coluna de tipos de valores de pequenos para médios e grandes, para que o armazenamento das cadeias de caracteres seja alterado para a opção de tabela ativa. Considere recriar os índices na tabela após a atualização ou o preenchimento, 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 tabela HumanResources.JobCandidate 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 tabela Production.WorkOrderRouting para armazenar o tipo de dados decimal no storage format 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 'AdventureWorks2012', 'ON';
GO
USE AdventureWorks2012;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';