CREATE INDEX (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW)

Cria um índice relacional em uma tabela ou exibição. Também chamado de um índice rowstore porque é um índice de árvore B clusterizado ou não clusterizado. Você pode criar um índice rowstore antes que haja dados na tabela. Use um índice rowstore para melhorar o desempenho de consulta, especialmente quando as consultas forem selecionadas de colunas específicas ou exigirem que os valores sejam classificados em uma ordem específica.

O Azure Synapse Analytics e o PDW (Analytics Platform System) não são compatíveis com restrições exclusivas. Quaisquer exemplos fazendo referência a restrições exclusivas são aplicáveis somente a SQL Server e Banco de Dados SQL.

Para obter informações sobre as diretrizes de design de índice, confira o Guia de design de índice do SQL Server.

Exemplos:

1. Para criar um índice não clusterizado em uma tabela

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. Criar um índice clusterizado em uma tabela e usar um nome de três partes para a tabela

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. Criar um índice não clusterizado com uma restrição exclusiva e especificar a ordem de classificação

   CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
   

Cenário principal:

Começando com o SQL Server 2016 (13.x) e o Banco de Dados SQL, use um índice não clusterizado em um índice columnstore para melhorar o desempenho da consulta de armazenamento de dados. Para obter mais informações, veja Índices Columnstore – data warehouse.

Para ver tipos de índices adicionais, consulte:

• CREATE XML INDEX
• CREATE SPATIAL INDEX
• CREATE COLUMNSTORE INDEX

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe do SQL Server, do Banco de Dados SQL do Azure, da Instância Gerenciada de SQL do Azure

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

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

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

<filter_predicate> ::=
    <conjunct> [ AND ] [ ...n ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

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

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

Índice relacional compatível com versões anteriores

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

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

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Sintaxe do Azure Synapse Analytics e do Parallel Data Warehouse

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

Argumentos

UNIQUE

Cria um índice exclusivo em uma tabela ou exibição. Um índice exclusivo é aquele no qual duas linhas não podem ter o mesmo valor de chave de índice. Um índice clusterizado em uma exibição deve ser exclusivo.

O Mecanismo de Banco de Dados não permite a criação de um índice exclusivo em colunas que já contêm valores duplicados, independentemente de IGNORE_DUP_KEY estar definido ou não como ON. Se isso for tentado, o Mecanismo de Banco de Dados exibirá uma mensagem de erro. Valores duplicados devem ser removidos para que um índice exclusivo possa ser criado em uma ou mais colunas. As colunas usadas em um índice exclusivo devem ser definidas como NOT NULL, pois vários valores nulos serão considerados duplicatas quando um índice exclusivo for criado.

CLUSTERED

Cria um índice no qual a ordem lógica dos valores de chave determina a ordem física das linhas correspondentes em uma tabela. O nível inferior, ou folha, do índice clusterizado contém as linhas de dados reais da tabela. Uma tabela ou exibição pode ter um índice clusterizado por vez.

Uma exibição com um índice clusterizado exclusivo é chamada de exibição indexada. Criar um índice clusterizado exclusivo em uma exibição materializa fisicamente a exibição. Um índice clusterizado exclusivo deve ser criado em uma exibição para que qualquer outro índice possa ser definido na mesma exibição. Para obter mais informações, veja Criar exibições indexadas.

Crie o índice clusterizado antes de criar quaisquer índices não clusterizados. Os índices não clusterizados existentes nas tabelas serão recriados quando um índice clusterizado for criado.

Se CLUSTERED não for especificado, um índice não clusterizado será criado.

Em alguns casos, criar um novo índice clusterizado pode habilitar índices previamente desabilitados. Para obter mais informações, veja Habilitar índices e restrições e Desabilitar índices e restrições.

NONCLUSTERED

Cria um índice que especifica a ordem lógica de uma tabela. Com um índice não clusterizado, a ordem física das linhas de dados é independente de sua ordem indexada.

Cada tabela pode ter até 999 índices não clusterizados, independentemente de como eles são criados: seja implicitamente com as restrições PRIMARY KEY e UNIQUE ou explicitamente com CREATE INDEX.

Para exibições indexadas, os índices não clusterizados podem ser criados somente em uma exibição que tenha um índice clusterizado exclusivo já definido.

Se não for especificado de outra forma, o tipo de índice padrão será não clusterizado.

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 precisam seguir as regras para identificadores.

column

É a coluna, ou colunas, em que o índice se baseia. Especifique dois ou mais nomes de coluna para criar um índice composto com os valores combinados das colunas especificadas. Liste as colunas que serão incluídas no índice composto, em ordem de prioridade de classificação, entre parênteses depois de table_or_view_name.

Até 32 colunas podem ser combinadas em uma única chave de índice composto. Todas as colunas de uma chave de índice composto devem estar na mesma tabela ou exibição. O tamanho máximo permitido de valores de índice combinados é de 900 bytes para um índice clusterizado ou de 1.700 para um índice não clusterizado. Os limites são 16 colunas e 900 bytespara versões anteriores a Banco de Dados SQL e a SQL Server 2016 (13.x).

Colunas que são dos tipos de dados LOB (Objeto Grande) ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, ou image não podem ser especificadas como colunas chave para um índice. Além disso, uma definição de exibição não pode incluir colunas ntext, text ou image, mesmo que elas não sejam referenciadas na instrução CREATE INDEX.

É possível criar índices em colunas do tipo CLR definido pelo usuário se o tipo der suporte à ordenação binária. Também é possível criar índices em colunas computadas definidas como invocações de método de uma coluna de tipo definido pelo usuário, desde que os métodos sejam marcados como determinísticos e não executem operações de acesso aos dados. Para obter mais informações sobre como indexar colunas CLR de tipo definido pelo usuário, veja Tipos CLR definidos pelo usuário.

[ ASC | DESC ]

Determina a direção de classificação crescente ou decrescente da coluna de índice específica. O padrão é ASC.

INCLUDE (column [ ,... n ] )

Especifica as colunas não chave a serem adicionadas ao nível folha do índice não clusterizado. O índice não clusterizado pode ser exclusivo ou não exclusivo.

Os nomes de coluna não podem ser repetidos na lista INCLUDE e não podem ser usados simultaneamente como colunas de chave e não chave. Índices não clusterizados sempre conterão as colunas de índice clusterizado se um índice clusterizado for definido na tabela. Para obter mais informações, consulte Create Indexes with Included Columns.

São permitidos todos os tipos de dados, exceto text, ntexte image. Começando com SQL Server 2012 (11.x) e Banco de Dados SQL do Azure, se qualquer uma das colunas não chave especificadas for varchar(max), nvarchar(max) ou varbinary(max), o índice poderá ser criado ou recriado usando a opção ONLINE.

As colunas computadas que são determinísticas e precisas ou imprecisas podem ser colunas incluídas. Colunas computadas derivadas dos tipos de dados image, ntext, text, varchar(max) , nvarchar(max) , varbinary(max) e xml podem ser incluídas em colunas não chave, desde que os tipos de dados da coluna computada sejam permitidos como uma coluna incluída. Para obter mais informações, consulte Indexes on Computed Columns.

Para obter informações sobre como criar um índice XML, veja CREATE XML INDEX.

WHERE <filter_predicate>

Cria um índice filtrado especificando quais linhas serão incluídas nele. O índice filtrado deve ser um índice não clusterizado em uma tabela. Cria estatísticas filtradas para as linhas de dados no índice filtrado.

O predicado de filtro usa a lógica de comparação simples e não pode fazer referência a uma coluna computada, a uma coluna UDT, a uma coluna de tipo de dados espacial ou a uma coluna de tipo de dados hierarchyID. Comparações que usam literais NULL não são permitidas com os operadores de comparação. Use os operadores IS NULL e IS NOT NULL, nesse caso.

Estes são alguns exemplos de predicados de filtro da tabela Production.BillOfMaterials:

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Índices filtrados não se aplicam a índices XML e índices de texto completo. Para índices UNIQUE, somente as linhas selecionadas devem ter valores de índice exclusivo. Índices filtrados não permitem a opção IGNORE_DUP_KEY.

ON partition_scheme_name ( column_name )

Especifica o esquema de partição que define os grupos de arquivos nos quais as partições de um índice particionado serão mapeadas. O esquema de partição deve existir no banco de dados com a execução de CREATE PARTITION SCHEME ou ALTER PARTITION SCHEME. column_name especifica a coluna com relação à qual um índice particionado será particionado. Essa coluna precisa corresponder ao tipo de dados, ao comprimento e à precisão do argumento da função de partição que partition_scheme_name está usando. column_name não é restrito às colunas na definição de índice. Qualquer coluna da tabela base pode ser especificada, exceto que, ao particionar um índice UNIQUE, column_name deve ser escolhido entre aqueles usados como chave exclusiva. Essa restrição permite ao Mecanismo de Banco de Dados verificar a exclusividade de valores de chave em uma única partição apenas.

Se partition_scheme_name ou filegroup não for especificado e a tabela estiver particionada, o índice será colocado no mesmo esquema de partição, usando a mesma coluna de particionamento que a tabela subjacente.

Para obter mais informações sobre particionamento de índices, consulte Tabelas e índices particionados.

ON filegroup_name

Cria o índice especificado no grupo de arquivos especificado. Se nenhum local for especificado e a tabela ou exibição não for particionada, o índice usará o mesmo grupo de arquivos que a tabela ou exibição subjacente. O grupo de arquivos já deve existir.

ON "padrão"

Cria o índice especificado no mesmo esquema de partição ou grupo de arquivos que a tabela ou a exibição.

Nesse contexto, default não é uma palavra-chave. É um identificador para o grupo de arquivos padrão e precisa ser delimitado, como em ON "default" ou ON [default]. Se "padrão" for especificado, a opção QUOTED_IDENTIFIER deverá ser definida como ON para a sessão atual. Essa é a configuração padrão. Para saber mais, confira SET QUOTED_IDENTIFIER.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

Especifica a colocação de dados FILESTREAM para a tabela quando um índice clusterizado é criado. A cláusula FILESTREAM_ON permite mover os dados FILESTREAM para outro grupo de arquivos ou esquema de partição FILESTREAM.

filestream_filegroup_name é o nome de um grupo de arquivos FILESTREAM. O grupo de arquivos deve ter um arquivo definido para o grupo de arquivos usando uma instrução CREATE DATABASE ou ALTER DATABASE; caso contrário, será gerado um erro.

Se a tabela for particionada, a cláusula FILESTREAM_ON precisará ser incluída e especificar um esquema de partição de grupos de arquivos FILESTREAM que use a mesma função de partição e as mesmas colunas de partição do esquema de partição da tabela. Caso contrário, será gerado um erro.

Se a tabela não estiver particionada, a coluna FILESTREAM não poderá ser particionada. Os dados FILESTREAM da tabela precisam ser armazenados em um único grupo de arquivos que é especificado na cláusula FILESTREAM_ON.

FILESTREAM_ON NULL poderá ser especificado em uma instrução CREATE INDEX se um índice clusterizado estiver sendo criado e a tabela não contiver uma coluna FILESTREAM.

Para obter mais informações, veja FILESTREAM (SQL Server).

<object>::=

O objeto totalmente qualificado ou não totalmente qualificado a ser indexado.

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 a ser indexada.

A exibição deve ser definida com SCHEMABINDING para criar um índice nela. Um índice clusterizado exclusivo deve ser criado em uma exibição antes que qualquer índice não clusterizado seja criado. Para obter mais informações sobre exibições indexadas, consulte a seção Comentários.

A partir do SQL Server 2016 (13.x), o objeto pode ser uma tabela armazenada com um índice columnstore clusterizado.

O Banco de Dados SQL do Azure dá suporte ao formato de nome de três partes database_name.[schema_name].object_name quando database_name é o banco de dados atual ou o database_name é tempdb e o object_name começa com #.

<relational_index_option>::=

Especifica as opções a serem usadas ao criar o índice.

PAD_INDEX = { ON | OFF }

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

ATIVADO
O percentual de espaço livre especificado 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 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, considerando o conjunto de chaves em páginas intermediárias.

A opção PAD_INDEX só é útil quando FILLFACTOR é especificado, porque PAD_INDEX usa o percentual especificado por FILLFACTOR. Se a porcentagem especificada para FILLFACTOR não for grande o suficiente para permitir uma linha, o Mecanismo de Banco de Dados substituirá a porcentagem internamente para permitir o valor mínimo. O número de linhas em uma página de índice intermediária nunca é menor do que dois, independentemente de quão baixo seja o valor de fillfactor.

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

FILLFACTOR = fillfactor

Especifica uma porcentagem que indica quanto o Mecanismo de Banco de Dados deve preencher o nível folha de cada página de índice durante a criação ou recriação do índice. O valor de fillfactor deve ser um valor inteiro de 1 a 100. Os valores de fator de preenchimento 0 e 100 são iguais em todos os aspectos. Se fillfactor for 100, o Mecanismo de Banco de Dados criará índices com páginas de folha preenchidas até a capacidade total.

A configuração FILLFACTOR se aplica somente quando o índice é criado ou recriado. O Mecanismo de Banco de Dados não mantém dinamicamente o percentual especificado de espaço vazio nas páginas.

Para ver a configuração do fator de preenchimento, use fill_factor em sys.indexes.

Para obter mais informações, veja Especificar fator de preenchimento para um índice.

SORT_IN_TEMPDB = { ON | OFF }

Especifica se os resultados de classificação temporários devem ser armazenados no tempdb. O padrão é OFF exceto para a Hiperescala do Banco de Dados SQL do Azure. Para todas as operações de build de índice em Hiperescala, SORT_IN_TEMPDB está sempre ativado, independentemente da opção especificada, a menos que a recompilação de índice retomável seja usada.

ATIVADO
Os resultados de classificação intermediários usados para criar o índice são armazenados no tempdb. Isso poderá reduzir o tempo necessário para criar um índice se tempdb estiver em um conjunto de discos diferente do banco de dados de 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.

Além do espaço necessário no banco de dados de usuário para criar o índice, tempdb deve ter aproximadamente a mesma quantidade de espaço adicional para conter os resultados de classificação intermediários. Para obter mais informações, consulte a Opção SORT_IN_TEMPDB para índices.

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

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 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 forem inseridos em um índice exclusivo. Ocorrerá falha somente nas linhas que violarem a restrição de exclusividade.

OFF
Ocorrerá uma mensagem de erro quando os valores de chave duplicados 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.

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

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

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

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

STATISTICS_INCREMENTAL = { ON | OFF }

Aplica-se ao: SQL Server (Começando pelo SQL Server 2014 (12.x)) e Banco de Dados SQL do Azure

Quando estiver ON, as estatísticas serão criadas conforme as estatísticas de partição. Quando estiver OFF, a árvore de estatísticas será removida e o SQL Server calculará as estatísticas novamente. O padrão é OFF.

Se as estatísticas por partição não tiverem suporte, a opção será ignorada e um aviso será gerado. As estatísticas incrementais não têm suporte para os seguintes tipos de estatísticas:

• Estatísticas criadas com os índices que não estejam alinhados por partição com a tabela base.
• Estatísticas criadas em bancos de dados secundários legíveis AlwaysOn.
• Estatísticas criadas em bancos de dados somente leitura.
• Estatísticas criadas em índices filtrados.
• Estatísticas criadas em exibições.
• Estatísticas criadas em tabelas internas.
• Estatísticas criadas com índices espaciais ou índices XML.

DROP_EXISTING = { ON | OFF }

É uma opção para remover e recompilar o índice clusterizado ou não clusterizado existente com as especificações da coluna modificada e manter o mesmo nome para o índice. O padrão é OFF.

ATIVADO
Especifica remover e recompilar o índice existente, que deve ter o mesmo nome que o parâmetro index_name.

OFF
Especifica não remover e recompilar o índice existente. O SQL Server exibirá um erro se o nome do índice especificado já existir.

Com DROP_EXISTING, você pode alterar:

• Um índice rowstore não clusterizado para um índice rowstore clusterizado.

Com DROP_EXISTING, você não pode alterar:

• Um índice rowstore clusterizado para um índice rowstore não clusterizado.
• Um índice columnstore clusterizado para qualquer tipo de índice de rowstore.

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

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.

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 período muito curto. Ao término da operação, por um curto período, um bloqueio S (Compartilhado) será adquirido na origem se um índice não clusterizado estiver sendo criado. Um bloqueio Sch-M (Modificação de Esquema) será adquirido quando um índice clusterizado for criado ou descartado online e quando um índice clusterizado ou não clusterizado estiver sendo recompilado. Não será possível definir ONLINE como ON quando um índice estiver sendo criado em uma tabela temporária local.

OFF
Os bloqueios de tabela são aplicados enquanto durar a operação de índice. 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 instruções SELECT.

Para obter mais informações, consulte Perform Index Operations Online.

Índices, inclusive aqueles em tabelas temporárias globais, podem ser criados online exceto nos seguintes casos:

• Índice XML
• Índice em uma tabela temporária local
• Índice clusterizado exclusivo inicial em uma exibição
• Índices clusterizados desabilitados
• Índices columnstore
• Índice clusterizado se a tabela subjacente contiver tipos de dados LOB (image, ntext, text) e tipos de dados espaciais
• As colunas varchar(max) e varbinary(max) não podem fazer parte de uma chave de índice. Em SQL Server (começando com o SQL Server 2012 (11.x)) e Banco de Dados SQL do Azure, quando uma tabela contém as colunas varchar(max) ou varbinary(max) , um índice clusterizado contendo outras colunas pode ser criado ou recriado usando a opção ONLINE.

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

RESUMABLE = { ON | OFF }

Aplica-se ao: SQL Server (Começando pelo SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

Especifica se uma operação de índice online é retomável.

ATIVADO
A operação do índice é retomável.

OFF
A operação do índice não é retomável.

MAX_DURATION = tempo [MINUTOS] usado com RESUMABLE = ON (requer ONLINE = ON)

Aplica-se ao: SQL Server (Começando pelo SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

Indica o tempo (um valor inteiro especificado em minutos) pelo qual um uma operação de índice online retomável é executada antes de ser colocada em pausa.

ALLOW_ROW_LOCKS = { ON | OFF }

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 }

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 são usados.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Aplica-se ao: SQL Server (Começando pelo SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

Especifica se a contenção de inserção de última página será ou não otimizada. O padrão é OFF. Confira a seção sobre Chaves sequenciais para saber mais.

MAXDOP = max_degree_of_parallelism

Substitui a opção de configuração max degree of parallelism enquanto durar a operação do índice. Para obter mais informações, veja Configurar a opção max degree of parallelism de configuração de 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 plano paralelo.

>1
Restringe o número máximo de processadores usados em uma operação de índice paralela ao número especificado, ou menos, com base na carga de trabalho atual do sistema.

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.

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 descritas a seguir:

Nenhuma
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.

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 o índice especificado que contém uma ou mais colunas de tipo de dados xml. As opções são as descritas a seguir:

ATIVADO
O índice ou as partições especificadas são compactados com a compactação XML.

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

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

Especifica as partições às quais as configurações DATA_COMPRESSION e XML_COMPRESSION se aplicam. Se o índice não for particionado, o argumento ON PARTITIONS gerará um erro. Se a cláusula ON PARTITIONS não for fornecida, a opção DATA_COMPRESSION ou XML_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, 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:

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

Você também pode especificar a opção XML_COMPRESSION mais de uma vez, por exemplo:

REBUILD WITH
(
  XML_COMPRESSION = OFF ON PARTITIONS (1),
  XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
  XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

Comentários

A instrução CREATE INDEX é otimizada como qualquer outra consulta. Para salvar as operações de E/S, o processador de consultas pode optar por examinar outro índice em vez de executar um exame de tabela. A operação de classificação pode ser eliminada em algumas situações. Em computadores com multiprocessadores, CREATE INDEX pode usar mais processadores para executar operações de exame e classificação associadas à criação do índice, exatamente como fazem outras consultas. Para obter mais informações, consulte Configurar operações de índice paralelo.

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

Os índices podem ser criados em uma tabela temporária. Quando a tabela for removida ou a sessão encerrada, os índices serão removidos.

Um índice clusterizado pode ser criado em uma variável de tabela quando uma chave primária é criada. Quando a consulta é concluída ou a sessão é encerrada, o índice é removido.

Os índices dão suporte a propriedades estendidas.

CREATE INDEX não tem suporte no Microsoft Fabric.

Índices cluster

Criar um índice clusterizado em uma tabela (heap) ou descartar e recriar um índice clusterizado existente requer workspace adicional disponível no banco de dados, para acomodar a classificação de dados e uma cópia temporária da tabela original ou dos dados do índice clusterizado existente. Para obter mais informações sobre índices clusterizados, confira Criar índices clusterizados e o Guia de arquitetura e design de índices do SQL Server.

Índices não clusterizados

A partir do SQL Server 2016 (13.x) e no Banco de Dados SQL do Azure, você pode criar um índice não clusterizado em uma tabela armazenada como um índice columnstore clusterizado. Se você primeiro criar um índice não clusterizado em uma tabela armazenada como um índice clusterizado ou heap, o índice persistirá se você depois converter a tabela em um índice columnstore clusterizado. Também não é necessário remover o índice não clusterizado ao recompilar o índice columnstore clusterizado.

Limitações e restrições:

• A opção FILESTREAM_ON não é válida quando você cria um índice não clusterizado em uma tabela armazenada como um índice columnstore clusterizado.

Índices exclusivos

Quando existe um índice exclusivo, o Mecanismo de Banco de Dados verifica se há valores duplicados sempre que dados são adicionados por operações de inserção. As operações de inserção que geram valores de chave duplicados são revertidas, e o Mecanismo de Banco de Dados exibe uma mensagem de erro. Isso acontece mesmo que a operação de inserção altere muitas linhas, mas crie apenas uma duplicata. Se for feita uma tentativa de inserir dados para os quais há um índice exclusivo e a cláusula IGNORE_DUP_KEY for definida como ON, somente as linhas que violarem o índice UNIQUE falharão.

Índices particionados

Índices particionados são criados e mantidos de uma maneira semelhante às tabelas particionadas, mas, como índices comuns, são tratados como objetos de banco de dados separados. É possível haver um índice particionado em uma tabela não particionada, bem como é possível ter um índice não particionado em uma tabela particionada.

Se você estiver criando um índice em uma tabela particionada e não especificar um grupo de arquivos para colocar o índice, ele será particionado da mesma maneira que a tabela subjacente. Isso ocorre porque, por padrão, os índices são colocados nos mesmos grupos de arquivos que suas tabelas subjacentes e, para uma tabela particionada, no mesmo esquema de partição que usa as mesmas colunas de particionamento. Quando o índice usa o mesmo esquema de partição e coluna de particionamento que a tabela, ele é alinhado à tabela.

Ao particionar um índice clusterizado não exclusivo, por padrão, o Mecanismo de Banco de Dados adiciona quaisquer colunas de particionamento à lista de chaves de índice clusterizado, se já não estiverem especificadas.

As exibições indexadas podem ser criadas em tabelas particionadas da mesma maneira que os índices em tabelas. Para obter mais informações sobre índices particionados, confira Tabelas e índices particionados e o Guia de arquitetura e design de índices do SQL Server.

No SQL Server, 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 as estatísticas dos índices particionados ao examinar todas as linhas da tabela, use CREATE STATISTICS ou UPDATE STATISTICS com a cláusula FULLSCAN.

Índices filtrados

Índice filtrado é um índice não clusterizado otimizado, adequado a consultas que selecionam uma pequena porcentagem de linhas de uma tabela. Ele usa um predicado de filtro para indexar uma parte dos dados na tabela. Um índice filtrado bem projetado pode melhorar o desempenho da consulta, além de reduzir custos de armazenamento e de manutenção.

Opções SET necessárias para índices filtrados

As opções SET na coluna Valor necessário são necessárias sempre que ocorrer alguma das seguintes condições:

• Criar um índice filtrado.

• A operação INSERT, UPDATE, DELETE ou MERGE modificar os dados de um índice filtrado.

• O índice filtrado é usado pelo otimizador de consulta para produzir o plano de consulta.

  Opções Set	Valor obrigatório	Valor do servidor padrão	Padrão Valor OLE DB e ODBC	Padrão Valor da DB-Library
  ANSI_NULLS	ATIVADO	ATIVADO	ATIVADO	OFF
  ANSI_PADDING	ATIVADO	ATIVADO	ATIVADO	OFF
  ANSI_WARNINGS*	ATIVADO	ATIVADO	ATIVADO	OFF
  ARITHABORT	ATIVADO	ATIVADO	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ATIVADO	ATIVADO	ATIVADO	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ATIVADO	ATIVADO	ATIVADO	OFF

  • A definição de ANSI_WARNINGS como ON definirá ARITHABORT implicitamente como ON quando o nível de compatibilidade do banco de dados estiver definido como 90 ou mais. Se o nível de compatibilidade do banco de dados estiver definido como 80 ou menos, a opção ARITHABORT deverá ser definida explicitamente como ON.

Se as opções SET estiverem incorretas, as seguintes condições poderão ocorrer:

• O índice filtrado não é criado.
• O Mecanismo de Banco de Dados gera um erro e reverte as instruções INSERT, UPDATE, DELETE ou MERGE que alteram os dados no índice.
• O otimizador de consulta não considera o índice no plano de execução para qualquer instrução Transact-SQL.

Para obter mais informações sobre índices filtrados, confira Criar índices filtrados e o Guia de arquitetura e design de índices do SQL Server.

Índices espaciais

Para obter informações sobre índices espaciais, veja CREATE SPATIAL INDEX e Visão geral de índices espaciais.

índices XML

Para informações sobre índices XML, veja CREATE XML INDEX e Índices XML (SQL Server).

Tamanho de chave de índice

O tamanho máximo para uma chave de índice é de 900 bytes para um índice clusterizado e de 1.700 bytes para um índice não clusterizado. (Antes do Banco de Dados SQL e SQL Server 2016 (13.x) o limite sempre era de 900 bytes.) Os índices nas colunas varchar que excederem o limite de bytes poderão ser criados se os dados existentes nas colunas não excederem o limite no momento da criação do índice; entretanto, as ações subsequentes de inserção ou atualização nas colunas que fazem com que o tamanho total seja maior que o limite de bytes falharão. A chave de um índice clusterizado não pode conter colunas varchar que tenham dados existentes na unidade de alocação ROW_OVERFLOW_DATA. Se um índice clusterizado for criado em uma coluna varchar e os dados existentes estiverem na unidade de alocação IN_ROW_DATA, as ações subsequentes de inserção ou atualização na coluna que faria o push dos dados da linha apresentarão falha.

Índices não clusterizados podem incluir colunas não chave no nível folha do índice. Essas colunas não são consideradas pelo Mecanismo de Banco de Dados ao calcular o tamanho da chave de índice. Para obter mais informações, confira Criar índices com colunas incluídas e o Guia de arquitetura e design de índices do SQL Server.

Colunas computadas

Os índices podem ser criados em colunas computadas. Além disso, as colunas computadas podem ter a propriedade PERSISTED. Isso significa que o Mecanismo de Banco de Dados armazena os valores computados na tabela e os atualiza quando as outras colunas das quais a coluna computada depende são atualizadas. O Mecanismo de Banco de Dados usa esses valores persistentes ao criar um índice na coluna e quando o índice é referenciado em uma consulta.

Uma coluna computada deve ser determinística e precisa para ser indexada. Entretanto, o uso da propriedade PERSISTED expande o tipo das colunas computadas indexáveis para incluir:

• Colunas computadas baseadas nas funções Transact-SQL e CLR, e métodos de tipo CLR definidos pelo usuário que são marcados como determinísticos pelo usuário.
• Colunas computadas baseadas em expressões que são determinísticas, conforme definidas pelo Mecanismo de Banco de Dados, mas imprecisas.

As colunas computadas persistentes requerem que as seguintes opções SET sejam definidas como mostrado na seção anterior Opções SET necessárias para índices filtrados.

A restrição UNIQUE ou PRIMARY KEY pode conter uma coluna computada contanto que satisfaça todas as condições para indexação. Especificamente, a coluna computada deve ser determinística e precisa ou determinística e persistente. Para obter mais informações sobre determinismo de funções, veja Funções determinísticas e não determinísticas.

Colunas computadas derivadas dos tipos de dados image, ntext, text, varchar(max) , nvarchar(max) , varbinary(max) e xml podem ser indexadas como coluna chave ou não chave incluída, desde que o tipo de dados da coluna computada seja permitido como uma coluna chave ou não chave de índice. Por exemplo, não é possível criar um índice XML primário em uma coluna xml computada. Se o tamanho da chave de índice exceder 900 bytes, uma mensagem de aviso será exibida.

Criar um índice em uma coluna computada pode causar a falha de uma operação de inserção ou atualização que tenha funcionado anteriormente. Essa falha pode ocorrer quando a coluna computada resultar em erro aritmético. Por exemplo, na tabela a seguir, embora a coluna computada c resulte em um erro aritmético, a instrução INSERT funciona.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Se, em vez disso, depois de criar a tabela, você criar um índice em uma coluna computada c, a mesma instrução INSERT agora falhará.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Para obter mais informações, consulte Indexes on Computed Columns.

Colunas incluídas em índices

As colunas não chave, chamadas de colunas incluídas, podem ser adicionadas ao nível folha de um índice não clusterizado para melhorar o desempenho da consulta ao abrangê-la. Isso quer dizer que todas as colunas referenciadas na consulta são incluídas no índice como colunas de chave ou não chave. Isso permite que o otimizador de consulta localize todas as informações necessárias de um exame de índice; os dados de tabela ou de índice clusterizado não são acessados. Para obter mais informações, confira Criar índices com colunas incluídas e o Guia de arquitetura e design de índices do SQL Server.

Especificar opções de índice

O SQL Server 2005 (9.x) introduziu novas opções de índice e também modifica o modo como as opções são especificadas. Na sintaxe compatível com versões anteriores, WITH option_name é equivalente a WITH (option_name = ON). Ao definir as opções de índice, aplicam-se as seguintes regras:

• Novas opções de índice só podem ser especificadas usando WITH (<option_name> = <ON | OFF>).
• As opções não podem ser especificadas com o uso de sintaxe compatível com versões anteriores e nova sintaxe na mesma instrução. Por exemplo, especificar WITH (DROP_EXISTING, ONLINE = ON) faz com que a instrução falhe.
• Ao criar um índice XML, as opções devem ser especificadas usando WITH (<option_name> = <ON | OFF>).

cláusula DROP_EXISTING

É possível usar a cláusula DROP_EXISTING para recompilar o índice, adicionar ou descartar colunas, modificar opções, modificar a ordem de classificação de colunas ou alterar o esquema de partição ou o grupo de arquivos.

Se o índice impuser uma restrição PRIMARY KEY ou UNIQUE e a definição de índice não for alterada de alguma maneira, o índice será descartado e recriado preservando a restrição existente. Entretanto, se a definição de índice for alterada, a instrução falhará. Para alterar a definição de uma restrição PRIMARY KEY ou UNIQUE, descarte a restrição e adicione uma restrição com a nova definição.

DROP_EXISTING melhora o desempenho quando você recria um índice clusterizado, com o mesmo conjunto de chaves ou um conjunto diferente, em uma tabela que também tenha índices não clusterizados. DROP_EXISTING substitui a execução de uma instrução DROP INDEX no índice clusterizado antigo, seguida da execução de uma instrução CREATE INDEX para o novo índice clusterizado. Os índices não clusterizados são recriados uma vez e, depois disso, somente se a definição de índice for alterada. A cláusula DROP_EXISTING não recompila os índices não clusterizados quando a definição de índice tem o mesmo nome de índice, chave e colunas de partição, atributo de exclusividade e ordem de classificação que o índice original.

Os índices não clusterizados podem ser recriados ou não, mas sempre permanecem em seus grupos de arquivos ou esquemas de partição originais e usam as funções de partição originais. Se um índice clusterizado for recriado para um grupo de arquivos ou esquema de partição diferente, os índices não clusterizados não serão movidos para coincidir com o novo local do índice clusterizado. Portanto, mesmo os índices não clusterizados previamente alinhados ao índice clusterizado, podem não estar mais alinhados a ele. Para obter mais informações sobre o alinhamento de índices particionados, consulte Índices e tabelas particionadas.

A cláusula DROP_EXISTING não classificará os dados novamente se as mesmas colunas de chave de índice forem usadas na mesma ordem e com a mesma ordem crescente ou decrescente, a menos que a instrução de índice especifique um índice não clusterizado e a opção ONLINE seja definida como OFF. Se o índice clusterizado for desabilitado, a operação CREATE INDEX WITH DROP_EXISTING deverá ser executada com ONLINE definido como OFF. Se um índice não clusterizado for desabilitado e não for associado a um índice clusterizado desabilitado, a operação CREATE INDEX WITH DROP_EXISTING poderá ser executada com ONLINE definido como OFF ou ON.

opção ONLINE

As diretrizes a seguir são aplicáveis ao executar operações de índice online:

• A tabela subjacente não pode ser alterada, truncada ou descartada quando uma operação de índice online estiver em andamento.
• É necessário espaço em disco temporário adicional durante a operação de índice.
• As operações online podem ser executadas em índices particionados e índices que contenham colunas computadas ou colunas incluídas persistentes.
• A opção de argumento low_priority_lock_wait permite que você decida como a operação de índice pode continuar quando bloqueada no bloqueio Sch-M.

Para obter mais informações, consulte Perform Index Operations Online.

Recursos

Os recursos a seguir são necessários para a operação de criação de índice online retomável:

• Espaço adicional necessário para manter o índice que está sendo criado, incluindo o tempo em que o índice está em pausa
• Taxa de transferência de log adicional durante a fase de classificação. O uso de espaço de log geral para índice retomável é menor em comparação com a criação de índice online regular e permite o truncamento de log durante a operação.
• Um estado DDL que impede qualquer modificação de DDL
• A limpeza de fantasma está bloqueada no índice no build pela duração da operação, em pausa e enquanto a operação está em execução.

Limitações funcionais atuais

A seguinte funcionalidade está desabilitada para operações de criação de índice retomáveis:

• Depois que uma operação de criação de índice online retomável é pausada, o valor inicial de MAXDOP não pode ser alterado

• Criar um índice que contém:

  • Coluna(s) calculada(s) ou TIMESTAMP como colunas de chave
  • Coluna LOB como coluna incluída para criação de índice retomável
  • Índice filtrado

Operações de índice retomáveis

Aplica-se ao: SQL Server (Começando pelo SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

As diretrizes a seguir se aplicam a operações de índice retomável:

• A criação de índice online é especificada como retomável usando a opção RESUMABLE = ON.
• A opção RESUMABLE não persiste nos metadados para um determinado índice e se aplica somente à duração de uma instrução DDL atual. Portanto, a cláusula RESUMABLE = ON deve ser especificada explicitamente para habilitar a capacidade de retomada.
• Só há suporte para a opção MAX_DURATION na opção RESUMABLE = ON.
• MAX_DURATION para a opção RESUMABLE especifica o intervalo para um índice que está sendo compilado. Depois que esse tempo é consumido, a recompilação de índice é colocada em pausa ou conclui sua execução. O usuário decide quando uma recompilação de um índice em pausa pode ser retomada. O tempo em minutos para MAX_DURATION deve ser superior a 0 minutos e inferior ou igual a uma semana (7 * 24 * 60 = 10080 minutos). Ter uma longa pausa para uma operação de índice pode afetar o desempenho de DML em uma tabela específica, bem como a capacidade de disco de banco de dados, já que tanto o original quanto o recém-criado exigem espaço em disco e precisam ser atualizados durante as operações DML. Se a opção MAX_DURATION for omitida, a operação de índice continuará até sua conclusão ou até que ocorra uma falha.
• Para pausar imediatamente a operação de índice, você pode interromper (Ctrl-C) o comando em andamento, executar o comando ALTER INDEX PAUSE ou executar o comando KILL <session_id>. Depois que o comando for colocado em pausa, ele poderá ser retomado usando o comando ALTER INDEX.
• Executar novamente a instrução CREATE INDEX original para o índice retomável retoma automaticamente uma operação de criação de índice em pausa.
• Não há suporte para a opção SORT_IN_TEMPDB = ON no índice retomável.
• O comando DDL com RESUMABLE = ON não pode ser executado em uma transação explícita (não pode fazer parte do bloco TRAN ... COMMIT).
• Para retomar/anular uma criação/recompilação de índice, use a sintaxe T-SQL ALTER INDEX .
• Não há suporte para índices desabilitados.

Para indicar que a criação de um índice é executada como uma operação retomável e para verificar seu estado de execução atual, confira sys.index_resumable_operations.

WAIT_AT_LOW_PRIORITY com operações de índice online

Aplica-se a: essa sintaxe de CREATE INDEX atualmente só se aplica ao SQL Server 2022 (16.x), ao Banco de Dados SQL do Azure e à Instância Gerenciada de SQL do Azure. Para ALTER INDEX, essa sintaxe se aplica a SQL Server (começando com SQL Server 2014 (12.x)) e Banco de Dados SQL do Azure. Para saber mais, confira ALTER INDEX.

A sintaxe low_priority_lock_wait permite especificar o comportamento WAIT_AT_LOW_PRIORITY. WAIT_AT_LOW_PRIORITY pode ser usado apenas com ONLINE=ON.

A opção WAIT_AT_LOW_PRIORITY permite que os DBAs gerenciem o bloqueio Sch-S e os bloqueios Sch-M necessários à criação de índice online e que eles selecionem uma das três opções. Nos três casos, se, durante o tempo de espera MAX_DURATION = n [minutes], não houver nenhuma atividade de bloqueio, a recompilação de índice online será executada imediatamente sem aguardar e a instrução DDL será concluída.

WAIT_AT_LOW_PRIORITY indica que a operação de criação do índice online aguardará bloqueios de baixa prioridade, permitindo que outras operações continuem enquanto a operação de build de índice online estiver aguardando. Omitir a opção WAIT AT LOW PRIORITY é equivalente a WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES]

O tempo de espera (um valor inteiro especificado em minutos) que a opção ou os bloqueios de criação de índice online deverão aguardar com baixa prioridade ao executar o comando DDL. Se a operação estiver bloqueada pelo tempo MAX_DURATION, a ação ABORT_AFTER_WAIT especificada será executada. O tempo MAX_DURATION está sempre em minutos e a palavra MINUTES pode ser omitida.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

NONE Continue aguardando o bloqueio com prioridade normal (regular).

SELF Saia da operação DDL de criação de índice online em execução no momento sem realizar a ação. A opção SELF não pode ser usada com um MAX_DURATION de 0.

BLOCKERS Elimina todas as transações de usuário que bloqueiam a operação DDL de recompilação de índice online para que a operação possa continuar. A opção BLOCKERS requer que o logon tenha a permissão ALTER ANY CONNECTION.

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

Quando a opção for ALLOW_ROW_LOCKS = ON e ALLOW_PAGE_LOCK = ON, os bloqueios em nível de linha, página e tabela serão permitidos ao acessar 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 a opção for ALLOW_ROW_LOCKS = OFF e ALLOW_PAGE_LOCK = OFF, somente o bloqueio em nível de tabela será permitido ao acessar o índice.

Chaves sequenciais

Aplica-se ao: SQL Server (Começando pelo SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

A contenção de inserção de última página é um problema de desempenho comum que ocorre quando um grande número de threads simultâneos tenta inserir linhas em um índice com uma chave sequencial. Um índice é considerado sequencial quando a coluna de chave à esquerda contém valores que sempre aumentam (ou diminuem), como uma coluna de identidade ou uma data que assume como padrão a data/hora atual. Como as chaves que estão sendo inseridas são sequenciais, todas as novas linhas serão inseridas no final da estrutura do índice – em outras palavras, na mesma página. Isso leva à contenção da página na memória que pode ser observada como vários threads aguardando PAGELATCH_EX para a página em questão.

Habilitar a opção de índice OPTIMIZE_FOR_SEQUENTIAL_KEY permite uma otimização no mecanismo de banco de dados que ajuda a aprimorar a taxa de transferência para inserções de alta simultaneidade em um índice. Ele se destina aos índices que têm uma chave sequencial e, portanto, estão sujeitos à contenção de inserção de última página, mas também pode ajudar com os índices que têm pontos de acesso em outras áreas da estrutura de índice de árvore B.

Exibir informações de índice

Para retornar informações sobre índices, é possível usar exibições do catálogo, funções do sistema e procedimentos armazenados do sistema.

Compactação de dados

Compactação de dados é descrita no tópico Compactação de dados. A seguir estão os pontos-chave a serem considerados:

• A compactação pode permitir que mais linhas sejam armazenadas em uma página, mas não altera o tamanho máximo da linha.
• Páginas não folha de um índice não são compactadas por página, mas podem ser compactadas por linha.
• Cada índice não clusterizado tem uma configuração de compactação individual e não herda a configuração de compactação da tabela subjacente.
• Quando um índice clusterizado é criado em um heap, ele herda o estado de compactação do heap, a menos que um estado de compactação alternativo seja especificado.

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

• 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 <index> ... REBUILD PARTITION ... recompila a partição especificada do índice.
• A sintaxe ALTER INDEX <index> ... REBUILD WITH ... recompila todas as partições do índice.

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 .

Compactação XML

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.

Muitas das mesmas considerações de compactação de dados se aplicam à compactação XML. Considere também o seguinte:

• Quando uma lista de partições é especificada, a compactação XML pode ser habilitada em partições individuais. Se a lista de partições não for especificada, todas as partições serão definidas para usar compactação a XML. Quando uma tabela ou um índice é criado, a compactação de dados XML é desabilitada, a menos que haja outra especificação. Quando uma tabela é modificada a compactação existente é preservada, a menos que especificada de outra maneira.
• Se for especificada uma lista de partições ou uma partição fora do intervalo, um erro será gerado.
• Quando um índice clusterizado é criado em um heap, ele herda o estado de compactação XML do heap, a menos que um estado de compactação alternativo seja especificado.
• A alteração da configuração de compactação XML de um heap exige que todos os índices não clusterizados na tabela sejam recriados a fim de conterem ponteiros para os novos locais de linha no heap.
• Você pode habilitar ou desabilitar a compactação online ou offline. A habilitação da compactação em um heap tem thread único para uma operação online.
• Para determinar o estado de compactação XML das partições em uma tabela particionada, consulte a coluna xml_compression da exibição de catálogo sys.partitions.

Permissões

Requer permissão ALTER na tabela, exibição ou associação na função de banco de dados fixa db_ddladmin.

Limitações e restrições

No Azure Synapse Analytics e no PDW (Analytics Platform System), não é possível criar:

• Um índice rowstore clusterizado ou não clusterizado em uma tabela de data warehouse quando já existe um índice columnstore. Esse comportamento é diferente do SMP SQL Server, o que permite aos índices rowstore e columnstore coexistir na mesma tabela.
• Você não pode criar um índice em uma exibição.

Metadados

Para exibir informações sobre índices existentes, você pode consultar a exibição do catálogo sys.indexes.

Notas de versão

O Banco de Dados SQL não é compatível com as opções grupo de arquivos e fluxo de arquivos.

Exemplos: Todas as versões. Usa o banco de dados AdventureWorks

a. Criar um índice rowstore não clusterizado simples

Os exemplos a seguir criam um índice não clusterizado na coluna VendorID da tabela Purchasing.ProductVendor.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. Criar um índice composto rowstore não clusterizado simples

O exemplo a seguir cria um índice composto não clusterizado nas colunas SalesQuota e SalesYTD da tabela Sales.SalesPerson.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. Criar um índice em uma tabela em outro banco de dados

O exemplo a seguir cria um índice clusterizado na coluna VendorID da tabela ProductVendor no banco de dados Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. Adicionar uma coluna a um índice

O exemplo a seguir cria o índice IX_FF com duas colunas da tabela dbo.FactFinance. A próxima instrução recompila o índice com mais uma coluna e mantém o nome existente.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

Exemplos: SQL Server, Banco de Dados SQL do Azure

E. Criando um índice não clusterizado exclusivo

O exemplo a seguir cria um índice não clusterizado exclusivo na coluna Name da tabela Production.UnitMeasure no banco de dados AdventureWorks2022. O índice imporá a exclusividade dos dados inseridos na coluna Name.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

A consulta a seguir testa a restrição de exclusividade tentando inserir uma linha com o mesmo valor que o de uma linha existente.

-- Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GETDATE());

A mensagem de erro resultante é:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F. Use a opção IGNORE_DUP_KEY

O exemplo a seguir demonstra o efeito da opção IGNORE_DUP_KEY inserindo várias linhas em uma tabela temporária primeiro com a opção definida como ON e, em seguida, com a opção definida como OFF. Uma única linha é inserida na tabela #Test, causando intencionalmente um valor duplicado quando a segunda instrução de várias linhas INSERT for executada. Uma contagem de linhas na tabela retorna o número de linhas inseridas.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

A seguir são apresentados os resultados da segunda instrução INSERT.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

Observe que as linhas inseridas da tabela Production.UnitMeasure que não violaram a restrição de exclusividade foram inseridas com êxito. Um aviso foi emitido e a linha duplicada ignorada, mas a transação inteira não foi revertida.

As mesmas instruções são executadas novamente, mas com IGNORE_DUP_KEY definido como OFF.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

A seguir são apresentados os resultados da segunda instrução INSERT.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

Observe que nenhuma linha da tabela Production.UnitMeasure foi inserida na tabela, embora somente uma linha violasse a restrição de índice UNIQUE.

G. Usando DROP_EXISTING para descartar e recriar um índice

O exemplo a seguir remove e recria um índice existente na coluna ProductID da tabela Production.WorkOrder no banco de dados AdventureWorks2022 usando a opção DROP_EXISTING. As opções FILLFACTOR e PAD_INDEX também são definidas.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H. Criar um índice em um modo de exibição

O exemplo a seguir cria uma exibição e um índice nessa exibição. Duas consultas que usam a exibição indexada são incluídas.

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
  DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
  WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO

-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND ProductID BETWEEN 700 AND 800
    AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO

-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND DATEPART(mm, OrderDate) = 3
  AND DATEPART(yy, OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

I. Criando um índice com colunas incluídas (não chave)

O exemplo a seguir cria um índice não clusterizado com uma coluna de chave (PostalCode) e quatro colunas não chave (AddressLine1, AddressLine2, City, StateProvinceID). Uma consulta incluída pelo índice vem em seguida. Para exibir o índice que é selecionado pelo otimizador de consulta, no menu Consulta no SQL Server Management Studio, selecione Exibir Plano de Execução Real antes de executar a consulta.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

J. Criar um índice particionado

O exemplo a seguir cria um índice particionado não clusterizado em TransactionsPS1, um esquema de partição existente no banco de dados AdventureWorks2022. Este exemplo pressupõe que o exemplo de índice particionado tenha sido instalado.

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K. Criando um índice filtrado

O exemplo a seguir cria um índice filtrado na tabela Production.BillOfMaterials do banco de dados AdventureWorks2022. O predicado de filtro pode incluir colunas que não sejam de chave no índice filtrado. O predicado deste exemplo seleciona apenas as linhas em que EndDate é não NULL.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L. Criar um índice compactado

O exemplo a seguir cria um índice em uma tabela não particionada usando a compactação de linha.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

O exemplo a seguir cria um índice em uma tabela particionada usando a compactação de linha em todas as partições do índice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

O exemplo a seguir cria um índice em uma tabela particionada usando a compactação de página na partição 1 do índice e a compactação de linha nas partições 2 a 4 do índice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

M. Criar um índice com a compactação XML

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.

O exemplo a seguir cria um índice em uma tabela não particionada usando a compactação XML. Pelo menos uma coluna no índice precisa ser do tipo de dados xml.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (XML_COMPRESSION = ON);
GO

O exemplo a seguir cria um índice em uma tabela particionada usando a compactação XML em todas as partições do índice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (XML_COMPRESSION = ON);
GO

N. Criar, retomar, pausar e anular operações de índice retomável

Aplica-se ao: SQL Server (Começando pelo SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

O. CREATE INDEX com diferentes opções de bloqueio de baixa prioridade

Os exemplos a seguir usam a opção WAIT_AT_LOW_PRIORITY para especificar estratégias diferentes para lidar com o bloqueio.

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

O exemplo a seguir usa a opção RESUMABLE e especifica dois valores MAX_DURATION, o primeiro aplica-se à opção ABORT_AFTER_WAIT e o segundo aplica-se à opção RESUMABLE.

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

P. Sintaxe básica

Criar, retomar, pausar e anular operações de índice retomável

Aplica-se ao: SQL Server (Começando pelo SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

Q. Criar um índice não clusterizado em uma tabela no banco de dados atual

O exemplo a seguir cria um índice não clusterizado na coluna VendorID da tabela ProductVendor.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. Criar um índice clusterizado em uma tabela em outro banco de dados

O exemplo a seguir cria um índice não clusterizado na coluna VendorID da tabela ProductVendor no banco de dados Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. Criar um índice clusterizado ordenado em uma tabela

O exemplo a seguir cria um índice clusterizado ordenado nas colunas c1 e c2 da tabela T1 no banco de dados MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. Converter um CCI em um índice clusterizado ordenado em uma tabela

O exemplo a seguir converte o índice columnstore clusterizado existente em um índice columnstore clusterizado ordenado chamado MyOrderedCCI nas colunas c1 e c2 da tabela T2 no banco de dados MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

Confira também

• Guia de arquitetura e design de índices do SQL Server
• Executar operações de índice online
• Índices e ALTER TABLE
• ALTER INDEX
• CREATE PARTITION FUNCTION
• CREATE PARTITION SCHEME
• CREATE SPATIAL INDEX
• CREATE STATISTICS
• CREATE TABLE
• CREATE XML INDEX
• Data Types
• DBCC SHOW_STATISTICS
• DROP INDEX
• Índices XML (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA