ALTER INDEX (Transact-SQL)

Modifica uma tabela ou índice de exibição existente (relacional ou XML) desabilitando, recriando ou reorganizando o índice, ou definindo opções no índice.

Convenções de sintaxe Transact-SQL

Sintaxe

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [PARTITION = ALL]
          [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
  | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
    table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } }
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

Argumentos

• index_name
  É o nome do índice. Os nomes de índice devem ser exclusivos em uma tabela ou exibição, mas não precisam ser exclusivos no banco de dados. Os nomes de índice devem seguir as regras de identificadores.

• ALL
  Especifica todos os índices associados à tabela ou exibição, independentemente do tipo de índice. Especificar ALL fará com que a instrução falhe se um ou mais índices estiverem em um grupo de arquivos offline ou somente leitura, ou se a operação especificada não for permitida em um ou mais tipos de índice. A tabela a seguir lista as operações de índice e os tipos de índice não permitidos.

  A especificação de ALL com esta operação	Falhará se a tabela tiver um ou mais
  REBUILD WITH ONLINE = ON	Índice XML Índice espacial
  REBUILD PARTITION = partition_number	Índice não particionado, índice XML, índice espacial ou índice desabilitado
  REORGANIZE	Índices com ALLOW_PAGE_LOCKS definido como OFF
  REORGANIZE PARTITION = partition_number	Índice não particionado, índice XML, índice espacial ou índice desabilitado
  IGNORE_DUP_KEY = ON	Índice espacial Índice XML
  ONLINE = ON	Índice espacial Índice XML

  Cuidado
  Para obter informações mais detalhadas sobre as operações de índice que podem ser executadas online, consulte Diretrizes para operações de índice online.

  Se ALL for especificado com PARTITION = partition_number, todos os índices deverão ser alinhados. Isso significa que eles serão particionados com base nas funções de partições equivalentes. Usar ALL com PARTITION faz com que todas as partições de índice com o mesmo partition_number sejam recriadas ou reorganizadas. Para obter mais informações sobre índices particionados, consulte Tabelas e índices particionados.

• database_name
  É o nome do banco de dados.

• schema_name
  É o nome do esquema ao qual a tabela ou exibição pertence.

• table_or_view_name
  É o nome da tabela ou exibição associada ao índice. Para exibir um relatório dos índices em um objeto, use a exibição do catálogo sys.indexes.

• REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
  Especifica que o índice será recriado usando as mesmas colunas, tipo de índice, atributo de exclusividade e ordem de classificação. Essa cláusula é equivalente a DBCC DBREINDEX. REBUILD habilita um índice desabilitado. A recriação de um índice clusterizado não recriará os índices não clusterizados associados, a menos que a palavra-chave ALL seja especificada. Se as opções de índice não forem especificadas, os valores de opção de índice existentes armazenados em sys.indexes serão aplicados. Para qualquer opção de índice cujo valor não seja armazenado em sys.indexes, o padrão indicado na definição de argumento da opção será aplicado.

  Ao recriar um índice XML ou um índice espacial, as opções ONLINE = ON e IGNORE_DUP_KEY = ON não são válidas.

  Se ALL for especificado e a tabela subjacente for um heap, a operação de recriação não terá efeito na tabela. Quaisquer índices não clusterizados associados à tabela serão recriados.

  A operação de recriação poderá ser registrada minimamente se o modelo de recuperação de banco de dados for definido como bulk-logged ou simples.

  Observação
  Ao recriar um índice XML primário, a tabela de usuário subjacente não estará disponível durante a operação de índice.

• PARTITION
  Especifica que somente uma partição de um índice será recriada ou reorganizada. PARTITION não poderá ser especificado se index_name não for um índice particionado.

  PARTITION = ALL recria todas as partições.

  Cuidado
  É possível criar e reconstruir índices não alinhados em uma tabela com mais de 1.000 partições, mas não há suporte para isso. Fazer isso pode provocar degradação do desempenho ou consumo excessivo de memória durante essas operações. É recomendável usar índices alinhados apenas quando o número de partições for maior que 1.000.

• partition_number
  É o número de partição de um índice particionado que será reconstruído ou reorganizado. partition_number é uma expressão constante que pode fazer referência a variáveis. Elas incluem variáveis de tipo definido pelo usuário ou funções e funções definidas pelo usuário, mas não é possível referenciar uma instrução Transact-SQL. partition_number deve existir ou a instrução falhará.

• WITH (<single_partition_rebuild_index_option>)
  SORT_IN_TEMPDB, MAXDOP e DATA_COMPRESSION são as opções que podem ser especificadas ao recriar uma única partição (PARTITION = n). Índices XML não podem ser especificados em uma única operação de recriação de partição.

  A operação de recriar um índice particionado não pode ser executada online. A tabela inteira é bloqueada durante a operação.

• DISABLE
  Marca o índice como desabilitado e indisponível para uso pelo Mecanismo de Banco de Dados. Qualquer índice pode ser desabilitado. A definição de um índice desabilitado permanece no catálogo do sistema sem nenhum dado de índice subjacente. Desabilitar um índice clusterizado evita que o usuário acesse os dados da tabela subjacente. Para habilitar um índice, use ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING. Para obter mais informações, consulte Desabilitar índices e restrições e Habilitar índices e restrições.

• REORGANIZE
  Especifica que o nível folha do índice será reorganizado. A instrução ALTER INDEX REORGANIZE sempre é executada online. Isso significa que os bloqueios de tabela de longo prazo não são mantidos e que as consultas ou atualizações da tabela subjacente podem continuar durante a transação ALTER INDEX REORGANIZE. REORGANIZE não pode ser especificado para um índice desabilitado ou um índice com ALLOW_PAGE_LOCKS definido como OFF.

• WITH ( LOB_COMPACTION = { ON | OFF } )
  Especifica que todas as páginas que contêm dados LOB (objeto grande) serão compactadas. Os tipos de dados LOB são image, text, ntext, varchar(max), nvarchar(max), varbinary(max) e xml. Compactar esses dados pode melhorar o uso do espaço em disco. O padrão é ON.

  • ON
    Todas as páginas que contêm dados de objeto grande são compactadas.

    Reorganizar um índice clusterizado especificado compacta todas as colunas LOB contidas no índice clusterizado. Reorganizar um índice não clusterizado compacta todas as colunas LOB não chave (incluídas) no índice.

    Quando ALL está especificado, todos os índices associados à tabela ou exibição especificada são reorganizados, e todas as colunas LOB associadas ao índice clusterizado, tabela subjacente ou índice não clusterizado com colunas incluídas são compactadas.

  • DESATIVADO
    Páginas que contêm dados de objeto grande não são compactadas.

    OFF não tem nenhum efeito em um heap.

  A cláusula LOB_COMPACTION é ignorada quando não há colunas LOB.

• SET ( <set_index option> [ ,... n] )
  Especifica opções de índice sem recriar ou reorganizar o índice. SET não pode ser especificado para um índice desabilitado.

• PAD_INDEX = { ON | OFF }
  Especifica o preenchimento do índice. O padrão é OFF.

  • ON
    A porcentagem de espaço livre especificada por FILLFACTOR é aplicada às páginas de nível intermediário do índice. Se FILLFACTOR não for especificado ao mesmo tempo em que PAD_INDEX for definido como ON, o valor do fator de preenchimento armazenado em sys.indexes será usado.

  • OFF ou fillfactor não está especificado
    As páginas do nível intermediário são preenchidas até próximo à capacidade máxima. Isso deixa espaço suficiente para pelo menos uma linha do tamanho máximo que o índice pode ter, com base no conjunto de chaves das páginas intermediárias.

  Para obter mais informações, consulte CREATE INDEX (Transact-SQL).

• FILLFACTOR = fillfactor
  Especifica um percentual que indica quanto o Mecanismo de Banco de Dados deve preencher do nível folha de cada página de índice durante a criação ou alteração do índice. fillfactor deve ser um valor inteiro de 1 a 100. O padrão é 0.

  Observação
  Os valores de fator de preenchimento 0 e 100 são iguais em todos os aspectos.

  Uma configuração FILLFACTOR explícita é usada somente quando o índice é criado pela primeira vez ou recriado. O Mecanismo de Banco de Dados não mantém dinamicamente a porcentagem especificada de espaço vazio nas páginas. Para obter mais informações, consulte CREATE INDEX (Transact-SQL).

  Para exibir a configuração do fator de preenchimento, use sys.indexes.

  Importante
  A criação ou alteração de um índice clusterizado com um valor FILLFACTOR afeta a quantidade de espaço de armazenamento que os dados ocupam, pois o Mecanismo de Banco de Dados redistribui os dados quando cria o índice clusterizado.

• SORT_IN_TEMPDB = { ON | OFF }
  Especifica se os resultados de classificação devem ser armazenados em tempdb. O padrão é OFF.

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

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

  Se uma operação de classificação não for necessária, ou se a classificação puder ser executada na memória, SORT_IN_TEMPDB será ignorada.

  Para obter mais informações, consulte Opção SORT_IN_TEMPDB para índices.

• IGNORE_DUP_KEY = { ON | OFF }
  Especifica a resposta de erro quando uma operação de inserção tenta inserir valores da chave duplicada 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 recriado. O padrão é OFF.

  • ON
    Uma mensagem de aviso ocorrerá quando valores de chave duplicada forem inseridos em um índice exclusivo. Ocorrerá falha somente nas linhas que violarem a restrição de exclusividade.

  • DESATIVADO
    Ocorrerá uma mensagem de erro quando os valores da chave duplicada forem inseridos em um índice exclusivo. Toda a operação INSERT será revertida.

  IGNORE_DUP_KEY não pode ser definido como ON para índices criados em uma 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 }
  Especifica se as estatísticas de distribuição são recomputadas. O padrão é OFF.

  • ON
    As estatísticas desatualizadas não são recalculadas automaticamente.

  • DESATIVADO
    A atualização automática de estatísticas está habilitada.

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

  Importante
  Desabilitar o recálculo automático de estatísticas de distribuição pode impedir que o otimizador de consulta selecione os planos de execução ideais para consultas que envolvem a tabela.

• ONLINE = { ON | OFF }
  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.

  Para um índice XML ou índice espacial, só há suporte para ONLINE = OFF e, se ONLINE for definido como ON, um erro será gerado.

  Observação
  As operações de índice online 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, consulte Recursos compatíveis com as edições do SQL Server 2012.

  • ON
    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 Tentativa Compartilhada é mantido na tabela de origem. Isso permite que as consultas ou atualizações na tabela e índices subjacentes continuem. No início da operação, um bloqueio S (Compartilhado) é mantido brevemente no objeto de origem. Ao término da operação, por um breve momento, um bloqueio S será mantido na origem se um índice não clusterizado estiver sendo criado; ou um bloqueio SCH-M (Modificação de Esquema) será adquirido quando um índice clusterizado for criado ou descartado online, ou quando um índice clusterizado ou não clusterizado estiver sendo recriado. Não é possível definir ONLINE como ON quando um índice está sendo criado em uma tabela temporária local.

  • DESATIVADO
    Os bloqueios de tabela são aplicados enquanto durar a operação de índice. Uma operação de índice offline que cria, recria ou descarta um índice clusterizado, espacial ou XML, ou que recria ou descarta um índice não clusterizado, adquire um bloqueio 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 instruções SELECT.

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

  Índices, inclusive aqueles em tabelas temporárias globais, podem ser recriados online com as seguintes exceções:

  • Índices XML

  • Índices em tabelas temporárias locais

  • Um subconjunto de um índice particionado (é possível recriar online um índice particionado inteiro.)

• ALLOW_ROW_LOCKS = { ON | OFF }
  Especifica se bloqueios de linha são permitidos. O padrão é ON.

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

  • DESATIVADO
    Bloqueios de linha não são usados.

• ALLOW_PAGE_LOCKS = { ON | OFF }
  Especifica se bloqueios de página são permitidos. O padrão é ON.

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

  • DESATIVADO
    Bloqueios de página não são usados.

  Observação
  Um índice não pode ser reorganizado quando ALLOW_PAGE_LOCKS está definido como OFF.

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

  Importante
  Embora a opção MAXDOP tenha suporte sintaticamente em todos os índices XML, para um índice espacial ou um índice XML primário, no momento, ALTER INDEX usa apenas um único processador.

  max_degree_of_parallelism pode ser:

  • 1
    Suprime geração de plano paralelo.

  • >1
    Restringe o número máximo de processadores usados em uma operação de índice paralela 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, consulte Recursos compatíveis com as edições do SQL Server 2012.

• DATA_COMPRESSION
  Especifica a opção de compactação de dados para o índice, número de partição ou intervalo de partições especificado. As opções são as seguintes:

  • NONE
    O índice ou as partições especificadas não são compactados.

  • ROW
    O índice ou as partições especificadas são compactados com o uso da compactação de linha.

  • PAGE
    O índice ou as partições especificadas são compactados com o uso da compactação de página.

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

• ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )
  Especifica as partições às quais se aplica a configuração DATA_COMPRESSION. Se o índice não for particionado, o argumento ON PARTITIONS irá gerar um erro. Se a cláusula ON PARTITIONS não for fornecida, a opção DATA_COMPRESSION será aplicada a todas as partições de um índice particionado.

  <partition_number_expression> pode ser especificado das seguintes maneiras:

  • Forneça o número de 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: 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:

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

Comentários

ALTER INDEX não pode ser usado para reparticionar um índice ou movê-lo para um grupo de arquivos diferente. Essa instrução não pode ser usada para modificar a definição de índice, como adicionar ou excluir colunas ou alterar a ordem das colunas. Use CREATE INDEX com a cláusula DROP_EXISTING para executar essas operações.

Quando uma opção não for especificada explicitamente, a configuração atual será aplicada. Por exemplo, se uma configuração FILLFACTOR não for especificada na cláusula REBUILD, o valor do fator de preenchimento armazenado no catálogo do sistema será usado durante o processo de recriação. Para exibir as configurações de opção de índice atuais, use sys.indexes.

Observação
Os valores de ONLINE, MAXDOP e SORT_IN_TEMPDB não são armazenados no catálogo do sistema. A menos que especificado na instrução de índice, o valor padrão da opção será usado.

Em computadores multiprocessador, assim como acontece em outras consultas, ALTER INDEX REBUILD usa automaticamente mais processadores para executar operações de exame e classificação associadas à modificação do índice. Quando você executar ALTER INDEX REORGANIZE, com ou sem LOB_COMPACTION, o valor grau máximo de paralelismo será uma operação de thread único. Para obter mais informações, consulte Configurar operações de índice paralelo.

Um índice não poderá ser reorganizado ou recriado se o grupo de arquivos no qual ele está localizado estiver offline ou definido como somente leitura. Quando a palavra-chave ALL for especificada e um ou mais índices estiver em um grupo de arquivos offline ou somente leitura, a instrução falhará.

Recriando índices

A recriação de um índice descarta e recria o índice. Isso remove a fragmentação, recupera espaço em disco ao compactar as páginas com base na configuração do fator de preenchimento especificada ou existente, e reclassifica as linhas do índice em páginas contíguas. Quando ALL é especificado, todos os índices da tabela são descartados e recriados em uma única transação. As restrições FOREIGN KEY não precisam ser descartadas com antecedência. Quando índices com 128 extensões ou mais são recriados, o Mecanismo de Banco de Dados adia as desalocações de página atuais e seus bloqueios associados até depois da confirmação da transação.

A recriação ou reorganização de índices pequenos geralmente não reduz a fragmentação. As páginas de índices pequenos são armazenadas em extensões mistas. Extensões mistas são compartilhadas por até oito objetos, portanto, a fragmentação em um índice pequeno pode não ser reduzida após a reorganização ou recriação.

No SQL Server 2012, as estatísticas não são criadas por meio do exame de todas as linhas da tabela quando um índice particionado é criado ou reconstruído. Em vez disso, o otimizador de consultas usa o algoritmo de amostragem padrão para gerar estatísticas. Para obter estatísticas em índices particionados por meio do exame de todas as linhas da tabela, use CREATE STATISTICS ou UPDATE STATISTICS com a cláusula FULLSCAN.

Nas versões anteriores do SQL Server, às vezes, era possível recriar um índice não clusterizado para corrigir as inconsistências causadas por falhas de hardware. No SQL Server 2008 e posteriores, ainda é possível reparar essas inconsistências entre o índice e o índice clusterizado recriando um índice não clusterizado offline. Entretanto, não é possível reparar inconsistências de índice não clusterizado recriando o índice online, porque o mecanismo de recriação online usará o índice não clusterizado existente como base para a recriação e, portanto, a inconsistência persistirá. A recriação do índice offline, entretanto, forçará um exame do índice clusterizado (ou heap) e, então, removerá a inconsistência. Como nas versões anteriores, é recomendável que a recuperação de inconsistências seja feita com a restauração dos dados afetados de um backup; porém, talvez seja possível reparar as inconsistências do índice recriando o índice não clusterizado offline. Para obter mais informações, consulte DBCC CHECKDB (Transact-SQL).

Reorganizando índices

A reorganização de um índice utiliza recursos mínimos do sistema. Ela desfragmenta o nível folha de índices clusterizados e não clusterizados em tabelas e exibições, reordenando fisicamente as páginas de nível folha para que correspondam à ordem lógica, da esquerda para a direita, dos nós folha. A reorganização também compacta as páginas de índice. A compactação baseia-se no valor do fator de preenchimento existente. Para exibir a configuração do fator de preenchimento, use sys.indexes.

Quando ALL for especificado, os índices relacionais, clusterizados e não clusterizados e os índices XML da tabela serão reorganizados. Algumas restrições se aplicam quando ALL é especificado; consulte a definição de ALL na seção Argumentos.

Para obter mais informações, consulte Reorganizar e recriar índices.

Desabilitando índices

A desabilitação de um índice impede o acesso do usuário ao índice, e, para índices clusterizados, aos dados da tabela subjacente. A definição de índice permanece no catálogo do sistema. A desabilitação de um índice não clusterizado ou clusterizado em uma exibição exclui fisicamente os dados do índice. A desabilitação de um índice clusterizado impede o acesso aos dados, mas eles permanecem inalterados na árvore B até que o índice seja descartado ou recriado. Para exibir o status de um índice habilitado ou desabilitado, consulte a coluna is_disabled na exibição do catálogo sys.indexes.

Se uma tabela estiver em uma publicação de replicação transacional, não será possível desabilitar nenhum índice associado a colunas de chave primária. Esses índices são necessários para a replicação. Para desabilitar um índice, primeiro remova a tabela da publicação. Para obter mais informações, consulte Publicar dados e objetos de banco de dados.

Use a instrução ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING para habilitar o índice. A recriação de um índice clusterizado desabilitado não pode ser executada com a opção ONLINE definida como ON. Para obter mais informações, consulte Desabilitar índices e restrições.

Opções de configuração

É possível definir as opções ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY e STATISTICS_NORECOMPUTE para um índice especificado sem recriá-lo ou reorganizá-lo. Os valores modificados são aplicados imediatamente ao índice. Para exibir essas configurações, use sys.indexes. Para obter mais informações, consulte Opções Set Index.

Opções de bloqueios de linha e de página

Quando ALLOW_ROW_LOCKS = ON e ALLOW_PAGE_LOCK = ON, os bloqueios em nível de linha, página e tabela são permitidos quando você acessa o índice. O Mecanismo de Banco de Dados escolhe o bloqueio apropriado e pode escalar o bloqueio de uma linha ou página para um bloqueio de tabela.

Quando ALLOW_ROW_LOCKS = OFF e ALLOW_PAGE_LOCK = OFF, somente um bloqueio em nível de tabela é permitido ao acessar o índice.

Se ALL for especificado quando as opções de bloqueio de linha ou de página forem definidas, as configurações serão aplicadas a todos os índices. Quando a tabela subjacente é um heap, as configurações são aplicadas das seguintes maneiras:

ALLOW_ROW_LOCKS = ON ou OFF	Ao heap e a quaisquer índices não clusterizados associados.
ALLOW_PAGE_LOCKS = ON	Ao heap e a quaisquer índices não clusterizados associados.
ALLOW_PAGE_LOCKS = OFF	Totalmente aos índices não clusterizados. Isso significa que todos os bloqueios de página não são permitidos nos índices não clusterizados. No heap, somente os bloqueios S (compartilhados), U (atualização) e X (exclusivos) da página não são permitidos. O Mecanismo de Banco de Dados ainda pode adquirir um bloqueio de página intencional (IS, IU ou IX) para fins internos.

Operações de índice online

Ao recriar um índice, se a opção ONLINE estiver definida como ON, os objetos, as tabelas e os índices associados subjacentes estarão disponíveis para consultas e modificação de dados. Os bloqueios de tabela exclusivos são mantidos por pouco tempo durante o processo de alteração.

A reorganização de um índice sempre é executada online. O processo não mantém bloqueios de longo prazo e, portanto, não bloqueia consultas ou atualizações em execução.

É possível executar operações de índice online simultâneas na mesma tabela somente ao fazer o seguinte:

• Criar vários índices não clusterizados.

• Reorganizar índices diferentes na mesma tabela.

• Reorganizar índices diferentes ao recriar índices não sobrepostos na mesma tabela.

Todas as outras operações de índice online executadas ao mesmo tempo falham. Por exemplo, não é possível recriar dois ou mais índices na mesma tabela ao mesmo tempo, ou criar um novo índice ao recriar um índice existente na mesma tabela.

Para obter mais informações, consulte Executar operações de índice online.

Restrições em índices espaciais

Quando você recria um índice espacial, a tabela de usuário subjacente não está disponível durante a operação do índice porque o índice espacial mantém um bloqueio de esquema.

Não é possível modificar a restrição PRIMARY KEY na tabela de usuário quando um índice espacial está definido em uma coluna dessa tabela. Para alterar a restrição PRIMARY KEY, primeiro descarte todos os índices espaciais da tabela. Depois de modificar a restrição PRIMARY KEY, é possível recriar cada um dos índices espaciais.

Em uma única operação de recriação de partição, não é possível especificar nenhum índice espacial. Entretanto, você pode especificar índices espaciais em uma recriação de partição completa.

Para alterar opções específicas a um índice espacial, como BOUNDING_BOX ou GRID, é possível usar uma instrução CREATE SPATIAL INDEX que especifique DROP_EXISTING = ON ou descartar o índice espacial e criar um novo. Para obter um exemplo, consulte CREATE SPATIAL INDEX (Transact-SQL).

Restrições de índice columnstore

Exceto para a opção REBUILD, um índice de columnstore otimizado de memória xVelocity não pode ser alterado. Em vez disso, remova e recrie o índice columnstore.

Compactação de dados

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

Para avaliar como a alteração do estado de compactação afetará uma tabela, um índice ou uma partição, use o procedimento armazenado sp_estimate_data_compression_savings.

As restrições a seguir se aplicam a índices particionados:

• Ao usar ALTER INDEX ALL ...,, não será possível alterar a configuração de compactação de uma única partição se a tabela tiver índices não alinhados.

• A sintaxe ALTER INDEX <índice> ... REBUILD PARTITION ... recria a partição especificada do índice.

• A sintaxe ALTER INDEX <índice> ... REBUILD WITH... recria todas as partições do índice.

Estatísticas

Quando você executar ALTER INDEX ALL … em uma tabela, somente as estatísticas associadas a índices são atualizadas. As estatísticas automáticas ou manuais criadas na tabela (em vez de um índice) não são atualizadas.

Permissões

Para executar ALTER INDEX, no mínimo, a permissão ALTER na tabela ou exibição é necessária.

Exemplos

A. Recriando um índice

O exemplo a seguir recria um único índice na tabela Employee.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B. Recriando todos os índices em uma tabela e especificando opções

O exemplo a seguir especifica a palavra-chave ALL. Isso recria todos os índices associados à tabela. Três opções são especificadas.

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. Reorganizando um índice com a compactação LOB

O exemplo a seguir reorganiza um único índice clusterizado. Como o índice contém um tipo de dados LOB no nível folha, a instrução também compacta todas as páginas que contêm dados de objeto grande. Observe que não é necessário especificar a opção WITH (LOB_COMPACTION) porque o valor padrão é ON.

USE AdventureWorks2012;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

D. Opções de configuração em um índice

O exemplo a seguir define várias opções no índice AK_SalesOrderHeader_SalesOrderNumber.

USE AdventureWorks2012;
GO
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Desabilitando um índice

O exemplo a seguir desabilita um índice não clusterizado da tabela Employee.

USE AdventureWorks2012;
GO
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee
DISABLE ;
GO

F. Desabilitando restrições

O exemplo a seguir desabilita uma restrição PRIMARY KEY desabilitando o índice PRIMARY KEY. A restrição FOREIGN KEY na tabela subjacente é automaticamente desabilitada e a mensagem de aviso é exibida.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
GO

O conjunto de resultados retorna esta mensagem de aviso.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'

on table 'EmployeeDepartmentHistory' referencing table 'Department'

was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Habilitando restrições

O exemplo a seguir habilita as restrições PRIMARY KEY e FOREIGN KEY que foram desabilitadas no exemplo F.

A restrição PRIMARY KEY é habilitada com a recriação do índice PRIMARY KEY.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;
GO

Em seguida, a restrição FOREIGN KEY é habilitada.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Recriando um índice particionado

O exemplo a seguir recria uma única partição, número de partição 5, do índice particionado IX_TransactionHistory_TransactionDate.

USE AdventureWorks;
GO
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5;
GO

I. Alterando a configuração de compactação de um índice

O exemplo a seguir recria um índice em uma tabela não particionada.

ALTER INDEX IX_INDEX1 
ON T1
REBUILD 
WITH ( DATA_COMPRESSION = PAGE )
GO

Para obter exemplos adicionais de compactação de dados, consulte Compactação de dados.

Consulte também

Referência

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

Conceitos

Desabilitar índices e restrições

Índices XML (SQL Server)

Executar operações de índice online

Reorganizar e recriar índices