Partilhar via

ALTER TABLE index_option (Transact-SQL)

  • Artigo

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

Especifica um conjunto de opções que podem ser aplicadas a um índice que faz parte de uma definição de restrição criada com ALTER TABLE.

Para obter uma descrição completa das opções de índice, consulte CREATE INDEX.

Convenções de sintaxe de Transact-SQL

Sintaxe

{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | ONLINE = { ON | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [ MINUTES ]
}

<range> ::=
<partition_number_expression> TO <partition_number_expression>

<single_partition_rebuild__option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                           ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

PAD_INDEX = { ON | OFF }

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores

Especifica o preenchimento do índice. O padrão é OFF.

  • ATIVADO

    O percentual de espaço livre especificada por FILLFACTOR é aplicado às páginas de nível intermediário do índice.

  • OFF ou fillfactor não está especificado

    As páginas de nível de intermediário são preenchidas até próximo de sua capacidade, deixando espaço suficiente para pelo menos uma linha do tamanho máximo que o índice pode ter, dado o conjunto de chaves em páginas intermediárias.

FILLFACTOR = fillfactor

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores

Especifica uma porcentagem que indica quanto Mecanismo de Banco de Dados deve preencher o nível folha de cada página de índice durante a criação ou alteração do índice. O valor especificado deve ser um valor inteiro entre 1 e 100. O padrão é 0.

Observação

Os valores 0 e 100 do fator de preenchimento são idênticos em todos os aspectos.

IGNORE_DUP_KEY = { ON | OFF }

Especifica o tipo de resposta quando uma operação de inserção tenta inserir valores da chave duplicados em um índice exclusivo. A opção IGNORE_DUP_KEY aplica-se apenas a operações de inserção depois que o índice é criado ou recompilado. A opção não tem nenhum efeito ao executar CREATE INDEX, ALTER INDEX ou UPDATE. O padrão é OFF.

  • ATIVADO

    Uma mensagem de aviso será exibida quando valores de chave duplicados são inseridos em um índice exclusivo. Somente as linhas que violarem a restrição de exclusividade falharão.

  • OFF

    Uma mensagem de erro ocorre quando valores de chave duplicados são inseridos em um índice exclusivo. Toda a INSERT operação é revertida.

IGNORE_DUP_KEY não pode ser definido como ON para índices criados em um modo de exibição, índices não exclusivos, índices XML, índices espaciais e índices filtrados.

Para exibir IGNORE_DUP_KEY, use sys.indexes.

Na sintaxe compatível com versões anteriores, WITH IGNORE_DUP_KEY é equivalente a WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF }

Desative ou habilite a opção de atualização automática de estatísticas, AUTO_STATISTICS_UPDATE, para as estatísticas relacionadas aos índices especificados. O padrão é OFF.

  • ATIVADO

    As atualizações automáticas de estatísticas são desativadas depois que o índice é recriado.

  • OFF

    As atualizações automáticas de estatísticas são habilitadas depois que o índice é recriado.

Para restaurar a atualização automática de estatísticas, defina o STATISTICS_NORECOMPUTE como OFFou execute UPDATE STATISTICS sem a NORECOMPUTE cláusula.

Aviso

Se você desabilitar a atualização automática de estatísticas, isso poderá impedir que o Otimizador de Consultas escolha planos de execução ideais para consultas que envolvam a tabela. Você deve usar essa opção com moderação e somente por um administrador de banco de dados qualificado.

Essa configuração não impede uma atualização automática com verificação completa das estatísticas relacionadas ao índice, durante a operação de reconstrução.

ALLOW_ROW_LOCKS = { ON | OFF }

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores

Especifica se bloqueios de linha são permitidos. O padrão é ON.

  • ATIVADO

    Bloqueios de linha são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de linha são usados.

  • OFF

    Bloqueios de linha não são usados.

ALLOW_PAGE_LOCKS = { ON | OFF }

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores

Especifica se bloqueios de página são permitidos. O padrão é ON.

  • ATIVADO

    Bloqueios de página são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de página são usados.

  • OFF

    Bloqueios de página não serão usados.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Aplica-se a: SQL Server 2019 (15.x) e versões posteriores

Especifica se a contenção de inserção de última página será ou não otimizada. O padrão é OFF. Para obter mais informações, consulte a seção Chaves sequenciais do CREATE INDEX artigo.

SORT_IN_TEMPDB = { ON | OFF }

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores

Especifica se os resultados de classificação devem ser armazenados no tempdb. O padrão é OFF.

  • ATIVADO

    Os resultados de classificação intermediários usados para criar o índice são armazenados no tempdb. Isso pode reduzir o tempo necessário para criar um índice se tempdb estiver em um conjunto de discos diferente do banco de dados do usuário. Entretanto, isso aumenta o espaço em disco usado durante a criação do índice.

  • OFF

    Os resultados intermediários de classificação são armazenados no mesmo banco de dados que o índice.

ONLINE = { ON | OFF }

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores

Especifica se as tabelas subjacentes e os índices associados estão disponíveis para consultas e modificação de dados durante a operação de índice. O padrão é OFF. REBUILD pode ser realizada como uma ONLINE operação.

Observação

Não é possível criar índices não clusterizados exclusivos online. Isso inclui índices criados devido a uma UNIQUE restrição OR PRIMARY KEY .

  • ATIVADO

    Bloqueios de tabela de longa duração não são mantidos durante a operação do índice. Durante a fase principal da operação de índice, apenas um bloqueio IS (Tentativa Compartilhada) é mantido na tabela de origem. Ele permite o prosseguimento de consultas ou atualizações feitas na tabela e nos índices subjacentes. No início da operação, um bloqueio Compartilhado (S) é mantido no objeto de origem por um curto período de tempo. No final da operação, por um curto período de tempo, um bloqueio S (compartilhado) é adquirido na origem se um índice não clusterizado estiver sendo criado; ou um bloqueio Sch-M (Modificação de Esquema) é adquirido quando um índice clusterizado é criado ou descartado online e quando um índice clusterizado ou não clusterizado está sendo reconstruído. Embora os bloqueios de índice online sejam bloqueios de metadados curtos, especialmente o bloqueio Sch-M deve esperar que todas as transações de bloqueio sejam concluídas nessa tabela. Durante o tempo de espera, o bloqueio Sch-M bloqueia todas as transações restantes que esperam atrás desse bloqueio ao acessar a mesma tabela. ONLINE não pode ser definido como ON quando um índice está sendo criado em uma tabela temporária local.

    Observação

    A recompilação de índice online pode definir as opções low_priority_lock_wait descritas posteriormente nesta seção. low_priority_lock_wait gerencia prioridade de bloqueio S e Sch-M durante a recompilação de índice online.

  • OFF

    Os bloqueios de tabela são aplicados durante a operação de índice. Isso evita o acesso de todos os usuários à tabela subjacente enquanto durar a operação. Uma operação de índice offline que cria, recria ou cancela um índice clusterizado ou recria ou cancela um índice não clusterizado, adquire um bloqueio de esquema de modificação (Sch-M) na tabela. Isso evita o acesso de todos os usuários à tabela subjacente enquanto durar a operação. Uma operação de índice offline que cria um índice não clusterizado adquire um bloqueio Compartilhado (S) na tabela. Isso impede atualizações na tabela subjacente, mas permite operações de leitura, como SELECT instruções.

Para obter mais informações, consulte Como funcionam as operações de índice online.

Observação

As operações de índice online não estão disponíveis em todas as edições de Microsoft SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, confira Edições e recursos com suporte no SQL Server 2022.

RESUMABLE = { ON | OFF}

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores

Especifica se uma operação ALTER TABLE ADD CONSTRAINT é retomável. A adição da operação de restrição de tabela é retomável quando ON. A adição da operação de restrição de tabela é retomável quando OFF. O padrão é OFF. Quando a opção RESUMABLE estiver definida como ON, a opção ONLINE = ON será exigida.

MAX_DURATION Quando usado com RESUMABLE = ON (requer ONLINE = ON) indica o tempo (um valor inteiro especificado em minutos) que uma operação de restrição de adição online retomável é executada antes de ser pausada. Se não for especificada, a operação continuará até a conclusão. MAXDOP é suportado com RESUMABLE = ON também.

Para obter mais informações sobre como habilitar e usar operações retomáveis ALTER TABLE ADD CONSTRAINT, confira Adição retomável de restrições de tabela.

MAXDOP = max_degree_of_parallelism

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores

Substitui a opção de configuração max degree of parallelism durante a operação do índice. Para obter mais informações, consulte Configurar o grau máximo de paralelismo (opção de configuração do servidor). Use MAXDOP para limitar o número de processadores usados em uma execução de plano paralelo. O máximo é de 64 processadores.

max_degree_of_parallelism pode ser:

  • 1: Suprime a geração de planos paralelos.
  • >1: Restringe o número máximo de processadores usados em uma operação de índice paralelo ao número especificado.
  • 0 (padrão): usa o número real de processadores ou menos com base na carga de trabalho atual do sistema.

Para obter mais informações, consulte Configurar operações de índice paralelo.

Observação

As operações de índice paralelas não estão disponíveis em todas as edições do Microsoft SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, confira Edições e recursos com suporte no SQL Server 2022.

DATA_COMPRESSION

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores

Especifica a opção de compactação de dados para a tabela, o número de partição ou o intervalo de partições especificado. As opções são as descritas a seguir:

  • Nenhuma

    A tabela ou as partições especificadas não são compactadas. Aplica-se somente a tabelas rowstore; não se aplica a tabelas columnstore.

  • ROW

    A tabela ou as partições especificadas são compactadas usando a compactação de linha. Aplica-se somente a tabelas rowstore; não se aplica a tabelas columnstore.

  • PAGE

    A tabela ou as partições especificadas são compactadas usando a compactação de página. Aplica-se somente a tabelas rowstore; não se aplica a tabelas columnstore.

  • COLUMNSTORE

    Aplica-se a: SQL Server 2014 (12.x) e versões posteriores

    Aplica-se somente a tabelas columnstore. COLUMNSTORE especifica para descompactar uma partição que foi compactada com a COLUMNSTORE_ARCHIVE opção. Quando os dados são restaurados, o índice continua a ser compactado COLUMNSTORE com a compactação columnstore usada para todas as tabelas columnstore.

  • COLUMNSTORE_ARCHIVE

    Aplica-se a: SQL Server 2014 (12.x) e versões posteriores

    Aplica-se a tabelas columnstore, que são armazenadas com um índice columnstore clusterizado. COLUMNSTORE_ARCHIVE Além disso, compacta a partição especificada para um tamanho menor. Isso pode ser usado para fins de arquivamento, ou em outras situações que exijam menos armazenamento e possam dispensar mais tempo para armazenamento e recuperação

Para obter mais informações sobre compactação, consulte Compactação de dados.

XML_COMPRESSION

Aplicável a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

Especifica a opção de compactação XML para qualquer coluna de tipo de dados xml na tabela. As opções são as descritas a seguir:

  • ATIVADO

    As colunas que usam o tipo de dados xml são compactadas.

  • OFF

    As colunas que usam o tipo de dados xml não são compactadas.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores

Especifica as partições às quais as configurações DATA_COMPRESSION e XML_COMPRESSION se aplicam. Se a tabela não estiver particionada, o argumento gerará ON PARTITIONS um erro. Se a ON PARTITIONS cláusula não for fornecida, a DATA_COMPRESSION opção ou XML_COMPRESSION se aplicará a todas as partições de uma tabela particionada.

<partition_number_expression> pode ser especificado das seguintes maneiras:

  • Forneça o número uma partição, por exemplo: ON PARTITIONS (2).
  • Forneça os números de várias partições individuais separados por vírgulas, por exemplo: ON PARTITIONS (1, 5).
  • Forneça os intervalos e as partições individuais, por exemplo: ON PARTITIONS (2, 4, 6 TO 8).

<range> pode ser especificado como números de partição separados pela palavra TO, por exemplo: ON PARTITIONS (6 TO 8).

Para definir tipos diferentes de compactação de dados para partições diferentes, especifique a opção DATA_COMPRESSION mais de uma vez, por exemplo:

--For rowstore tables
REBUILD WITH
(
  DATA_COMPRESSION = NONE ON PARTITIONS (1),
  DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
  DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)

--For columnstore tables
REBUILD WITH
(
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)
)

<single_partition_rebuild__option>

Na maioria das vezes, a reconstrução de um índice também reconstruirá todas as partições de um índice particionado. As opções a seguir, quando aplicadas a uma única partição, não recriarão todas as partições.

  • SORT_IN_TEMPDB
  • MAXDOP
  • DATA_COMPRESSION
  • XML_COMPRESSION

low_priority_lock_wait

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores

Uma SWITCH reconstrução de índice ou online é concluída assim que não houver operações de bloqueio para essa tabela. WAIT_AT_LOW_PRIORITY indica que, se a operação de reconstrução do SWITCH índice ou online não puder ser concluída imediatamente, ela aguardará. A operação mantém os bloqueios de baixa prioridade, permitindo que outras operações que mantêm bloqueios que estão em conflito com a instrução DDL continuem. Omitir a opção WAIT AT LOW PRIORITY é equivalente a WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = tempo [ MINUTOS ]

O tempo de espera (um valor inteiro especificado em minutos) que o bloqueio de reconstrução de SWITCH índice online ou que deve ser adquirido, aguarda ao executar o comando DDL. A SWITCH operação de reconstrução de índice ou online tenta ser concluída imediatamente. Se a operação for bloqueada por MAX_DURATION enquanto, uma das ABORT_AFTER_WAIT ações será executada. MAX_DURATION O tempo é sempre em minutos, e a palavra MINUTES pode ser omitida.

ABORT_AFTER_WAIT = { NENHUM | EU | BLOQUEADORES }

  • NONE

    Continua a operação de reconstrução de SWITCH índice online ou sem alterar a prioridade de bloqueio (usando prioridade regular).

  • SELF

    Sai da operação DDL de reconstrução de índice on-line que está sendo executada SWITCH no momento sem executar nenhuma ação.

  • BLOCKERS

    Elimina todas as transações de usuário que bloqueiam atualmente a operação DDL de reconstrução de SWITCH índice on-line ou on-line para que a operação possa continuar.

    BLOCKERS requer a ALTER ANY CONNECTION permissão.

Conteúdo relacionado