CREATE INDEX (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsAnalytics 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.

Observação

A documentação usa o termo árvore B geralmente em referência a índices. Em índices de rowstore, o Database Engine implementa uma árvore B+. Isso não se aplica a índices columnstore ou índice em tabelas com otimização de memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.

O Azure Synapse Analytics e o PDW (Analytics Platform System) não são compatíveis com restrições exclusivas. Todos os exemplos que fazem referência a restrições exclusivas são aplicáveis apenas ao SQL Server, ao Banco de Dados SQL do Azure e à Instância Gerenciada de SQL do Azure.

Para obter informações sobre diretrizes de design de índice, consulte 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:

A partir do SQL Server 2016 (13.x), no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure, você pode usar um índice não clusterizado em um índice columnstore para melhorar o desempenho da consulta de data warehouse. Para obter mais informações, consulte í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

Importante

A estrutura de sintaxe de índice relacional compatível com versões anteriores será removida em uma versão futura do SQL Server. Evite usar essa estrutura de sintaxe em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que a utilizam atualmente. Use a estrutura de sintaxe especificada em <relational_index_option>.

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.

O Mecanismo de Banco de Dados não permite a criação de um índice exclusivo em colunas que já incluem valores duplicados, quer esteja ou não IGNORE_DUP_KEY definido 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.

Uma UNIQUE restrição trata NULL como um valor. Se uma coluna for anulável e houver uma UNIQUE restrição na coluna, no máximo uma linha com uma NULL é permitida.

CLUSTERED

Cria um índice no qual a ordem de classificação especificada para as colunas de chave de índice determina a ordem da página na estrutura de índice no disco. As linhas nas páginas na parte inferior ou folha, nível do índice clusterizado sempre contêm todas as colunas da tabela. As linhas nas páginas nos níveis superiores do índice contêm somente colunas de chave.

Uma tabela pode ter apenas um índice clusterizado. Se houver um índice clusterizado em uma tabela, ele conterá todos os dados na tabela. Uma tabela sem um índice clusterizado é chamada de heap.

Uma exibição com um índice clusterizado exclusivo é chamada de exibição indexada. Uma exibição indexada pode ter apenas um índice clusterizado. 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, consulte Criar exibições indexadas.

Crie o índice clusterizado antes de criar quaisquer índices não clusterizados. Índices não clusterizados existentes em tabelas são recriados quando um índice clusterizado é criado, que é uma operação com uso intensivo de recursos se a tabela for grande.

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

Observação

Como o índice clusterizado contém todos os dados na tabela, criar um índice clusterizado e usar a ON partition_scheme_name cláusula ou ON filegroup_name efetivamente move a tabela do grupo de arquivos no qual a tabela foi criada para o novo esquema de partição ou grupo de arquivos. Antes de criar tabelas ou índices em grupos de arquivos específicos, verifique quais grupos de arquivos estão disponíveis e se eles têm espaço vazio suficiente para o índice.

Em alguns casos, a criação de um índice clusterizado pode habilitar índices desabilitados anteriormente. Para obter mais informações, consulte Habilitar índices e restrições e Desabilitar índices e restrições.

NONCLUSTERED

Cria um índice no qual a ordem de classificação especificada para as colunas de chave de índice determina a ordem da página na estrutura de índice no disco. Ao contrário do índice clusterizado, as linhas nas páginas no nível folha de um índice não clusterizado contêm apenas as colunas de chave de índice. Opcionalmente, um subconjunto de colunas não chave pode ser incluído usando a INCLUDE cláusula.

Cada tabela pode ter até 999 índices não clusterizados, independentemente de como os índices são criados: implicitamente com as PRIMARY KEY restrições ou UNIQUE 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 indexada não pode incluir colunas de ntext, texto ou imagem , mesmo que elas não sejam referenciadas na CREATE INDEX instrução.

É 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 a indexação de colunas de tipo clr definidas pelo usuário, consulte 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 de um í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 INCLUDE lista e não podem ser usados simultaneamente como colunas de chave e não chave. Índices não clusterizados sempre contêm implicitamente as colunas de índice clusterizado se um índice clusterizado for definido na tabela. Para obter mais informações, consulte Criar índices com colunas incluídas.

São permitidos todos os tipos de dados, exceto text, ntexte image. A partir do SQL Server 2012 (11.x), no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure, se qualquer uma das colunas não chave especificadas for varchar(max), nvarchar(max) ou varbinary(max), o índice poderá ser compilado 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 de tipos de dados image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml podem ser incluídas desde que o tipo de dados de coluna computada seja permitido como uma coluna incluída. Para obter mais informações, consulte Índices em colunas computadas.

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 uma lógica de comparação simples e não pode referenciar uma coluna computada, uma coluna UDT (tipo de dados definido pelo usuário), uma coluna de tipo de dados espacial ou uma coluna de tipo de dados hierarchyid . Comparações com NULL literais usando os operadores de comparação não são permitidas. 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 UNIQUE índices, somente as linhas selecionadas devem ter valores de índice exclusivos. Í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 para os quais as partições de um índice particionado sã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 de particionamento para o índice. 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 na tabela base pode ser especificada, exceto ao particionar um índice exclusivo, column_name deve ser escolhido entre aqueles usados como a 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.

Observação

Ao particionar um índice clusterizado não exclusivo, por padrão, o Mecanismo de Banco de Dados adiciona a coluna de particionamento à lista de chaves de índices clusterizados, se ela já não estiver especificada. Ao particionar um índice não clusterizado e não exclusivo, o Mecanismo de Banco de Dados adiciona a coluna de particionamento como uma coluna não chave (incluída) do índice, se ela já não estiver especificada.

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.

Observação

Não é possível especificar um esquema de particionamento em um índice XML. Se a tabela base for particionada, o índice XML usará o mesmo esquema de partição que a tabela.

Para obter mais informações sobre índices de particionamento, tabelas particionadas e índices.

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.

O termo default, neste contexto, não é uma palavra-chave. Ele é um identificador para o grupo de arquivos ou esquema particionado da tabela ou exibição e deve ser delimitado, como em ON "default" ou ON [default]. Se "default" for especificado, a opção QUOTED_IDENTIFIER deverá ser ON para a sessão atual. Essa é a configuração padrão. Para saber mais, confira SET QUOTED_IDENTIFIER.

Observação

No contexto de , "default" e [default] não indique o grupo de CREATE INDEXarquivos padrão do banco de dados. Eles indicam o grupo de arquivos ou esquema de partição usado pela tabela ou exibição base. Isso difere de CREATE TABLE, em que "default" e [default] coloque a tabela no grupo de arquivos padrão do banco de dados.

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

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

Para criar um índice em um modo de exibição, o modo de exibição deve ser definido com SCHEMABINDING. 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 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 <database_name>.<schema_name>.<object_name> de nome de três partes quando <database_name> é o nome do banco de dados atual ou <database_name> é tempdb e <object_name> começa com # ou ##. Se o nome do esquema for dbo, <schema_name> poderá ser omitido.

<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 pelo fator de preenchimento é aplicado às páginas de nível intermediário do índice. Se FILLFACTOR não for especificado ao mesmo tempo PAD_INDEXON, o valor do fator de preenchimento em sys.indexes será usado.

• OFF

  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. Isso também ocorre se PAD_INDEX estiver definido como ON , mas o fator de preenchimento não for especificado.

A PAD_INDEX opção é útil somente quando FILLFACTOR especificada, porque PAD_INDEX usa a porcentagem especificada por FILLFACTOR. Se a porcentagem especificada FILLFACTOR não for grande o suficiente para permitir uma linha, o Mecanismo de Banco de Dados substituirá internamente a porcentagem para permitir o mínimo. O número de linhas em uma página de índice intermediário nunca é menor que dois, independentemente de quão baixo 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 do preenchimento 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 exibir a configuração do fator de preenchimento, use a fill_factor coluna na exibição do catálogo sys.indexes .

Importante

A criação de um índice com FILLFACTOR menos de 100 aumenta a quantidade de espaço de armazenamento que os dados ocupam porque o Mecanismo de Banco de Dados redistribui os dados de acordo com o fator de preenchimento quando ele cria ou recria um índice.

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

SORT_IN_TEMPDB = { ON | OFF }

Especifica se a classificação temporária deve ser armazenada.tempdb O padrão é OFF exceto a Hiperescala do Banco de Dados SQL do Azure. Para todas as operações de build de índice na Hiperescala, é sempreON, SORT_IN_TEMPDB a menos que um build de índice retomável seja usado. Para builds de índice retomáveis, SORT_IN_TEMPDB é sempre OFF.

• ATIVADO

  Os resultados de classificação intermediária usados para compilar o índice são armazenados em tempdb. Isso pode reduzir o tempo necessário para criar um índice. 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 do usuário para criar o índice, tempdb deve ter aproximadamente a mesma quantidade de espaço adicional para manter os resultados de classificação intermediária. Para obter mais informações, consulte SORT_IN_TEMPDB opção 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 ocorre quando valores de chave duplicados são inseridos em um índice exclusivo. Somente as linhas que violam a restrição de exclusividade não são inseridas.

• OFF

  Uma mensagem de erro ocorre quando valores de chave duplicados são inseridos em um índice exclusivo. A instrução inteira INSERT é revertida.

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

Para exibir a IGNORE_DUP_KEY configuração de um índice, use a ignore_dup_key coluna na exibição de catálogo 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 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.

Aviso

Se você desabilitar a recomputação automática de estatísticas definindo STATISTICS_NORECOMPUTE = ON, poderá impedir que o otimizador de consulta escolha planos de execução ideais para consultas que envolvem a tabela.

ON A configuração STATISTICS_NORECOMPUTE não impede a atualização das estatísticas de índice que ocorrem durante a operação de recompilação de índice.

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

STATISTICS_INCREMENTAL = { ON | OFF }

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Quando ON, as estatísticas criadas são estatísticas por partição. Quando OFFa árvore de estatísticas é descartada e o SQL Server computa novamente as estatísticas. 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. Não há suporte para estatísticas incrementais nos seguintes casos:

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

Importante

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

• 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, somente um bloqueio compartilhado deIS intenção é mantido na tabela de origem. Ele permite o prosseguimento de consultas ou atualizações feitas na tabela e nos índices subjacentes. No início da operação, um bloqueio compartilhado (S) é mantido no objeto de origem por um curto período de tempo. No final da operação, por um curto período de tempo, um bloqueio compartilhado (S) será adquirido no objeto se um índice não clusterizado estiver sendo criado. Um bloqueio de modificação de esquema (Sch-M) é adquirido quando um índice clusterizado é criado ou descartado online e quando um índice clusterizado ou não clusterizado está sendo recriado. ONLINE não pode ser definido ON como quando um índice está sendo criado em uma tabela temporária local.

  Observação

  Você pode usar a opção WAIT_AT_LOW_PRIORITY para reduzir ou evitar bloqueios durante operações de índice online. Para obter mais informações, consulte WAIT_AT_LOW_PRIORITY com operações de índice online.

• OFF

  Os bloqueios de tabela são aplicados enquanto durar a operação de índice. Uma operação de índice offline que cria, recompila ou descarta um índice clusterizado, espacial ou XML ou recria ou descarta um índice não clusterizado, adquire um bloqueio de modificação de esquema (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 inicialmente adquire um bloqueio compartilhado (S) na tabela. Isso impede modificações da definição da tabela subjacente, mas permite ler e modificar os dados na tabela enquanto o build do índice está em andamento.

Para obter mais informações, consulte Executar operações de índice online e diretrizes para operações de índice 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 clusterizados no SQL Server 2017 (14.x)) e versões mais antigas
• Índices columnstore não clusterizados no SQL Server 2016 (13.x)) e versões mais antigas
• Í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. No SQL Server (começando com o SQL Server 2012 (11.x)), no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure, quando uma tabela contém colunas varchar(max) ou varbinary(max ), um índice clusterizado que contém outras colunas pode ser compilado ou recriado usando a opção ONLINE .
• Índices não clusterizados em uma tabela com um índice columnstore clusterizado

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

RESUMABLE = { ON | OFF }

aplica-se a: SQL Server 2019 (15.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Especifica se uma operação de índice online é retomável. Para obter mais informações, consulte Operações de índice resumáveis e considerações de índice 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 a: SQL Server 2019 (15.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Especifica por quanto tempo, em minutos, uma operação de índice retomável é executada antes de ser pausada.

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 a: SQL Server 2019 (15.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Especifica se a opção deve ou não ser otimizada para evitar a contenção de inserção da última página. O padrão é OFF. Consulte a seção Chaves Sequenciais para obter mais informações.

MAXDOP = max_degree_of_parallelism

Substitui a opção de configuração de grau máximo de paralelismo para a operação de í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 grau de paralelismo e o consumo de recursos resultante para uma operação de build de índice.

max_degree_of_parallelism pode ser:

• 1

  Suprime a geração de plano paralelo.

• >1

  Restringe o grau máximo de paralelismo usado em uma operação de índice paralelo ao número especificado ou menos com base na carga de trabalho atual do sistema.

• 0 (padrão)

  Usa o grau de paralelismo especificado no nível do servidor, do banco de dados ou do grupo de carga de trabalho, a menos que seja reduzido com base na carga de trabalho atual do sistema.

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

Observação

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

DATA_COMPRESSION

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 compactadas. Isso não se aplica a índices columnstore.

• ROW

  O índice ou as partições especificadas são compactados com o uso da compactação de linha. Isso não se aplica a índices columnstore.

• PAGE

  O índice ou as partições especificadas são compactados com o uso da compactação de página. Isso não se aplica a índices columnstore.

• COLUMNSTORE

  Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

  Aplica-se somente a índices columnstore, incluindo índices columnstore não clusterizados e clusterizados.

• COLUMNSTORE_ARCHIVE

  Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

  Aplica-se somente a índices columnstore, incluindo índices columnstore não clusterizados e clusterizados. COLUMNSTORE_ARCHIVE compacta ainda mais a partição especificada para um tamanho menor. Isso pode ser usado para fins de arquivamento, ou em outras situações que exijam menos armazenamento e possam dispensar mais tempo para armazenamento e recuperação.

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

XML_COMPRESSION

aplica-se 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

  Partições indexadas ou especificadas não são compactadas usando compactação XML.

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 ON PARTITIONS argumento 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-chave 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

Ao criar o plano de consulta para a CREATE INDEX instrução, o otimizador de consulta pode optar por verificar outro índice em vez de executar uma verificação de tabela. A operação de classificação pode ser eliminada em algumas situações. Em computadores multiprocessadores, CREATE INDEX pode usar paralelismo para as operações de verificação e classificação associadas à criação do índice, da mesma forma que outras consultas. Para obter mais informações, consulte Configurar operações de índice paralelo.

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

Os índices podem ser criados em uma tabela temporária. Quando a tabela é descartada ou sai do escopo, os índices são descartados.

Um índice clusterizado é criado em uma variável de tabela quando uma restrição de chave primária é adicionada. Da mesma forma, um índice não clusterizado é criado em uma variável de tabela quando uma restrição exclusiva é adicionada. Quando a variável de tabela sai do escopo, os índices são descartados.

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, consulte Criar índices clusterizados e o guia de arquitetura e design de índice do SQL Server.

Índices não clusterizados

A partir do SQL Server 2016 (13.x), no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure, você pode criar um índice não clusterizado em uma tabela armazenada como um índice columnstore clusterizado. Se você criar primeiro um índice não clusterizado em uma tabela armazenada como um heap ou índice clusterizado, o índice persistirá se você converter posteriormente 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.

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 um índice exclusivo existe, o Mecanismo de Banco de Dados verifica se há valores duplicados sempre que os dados são adicionados ou modificados. As operações que gerariam valores de chave duplicadas são revertidas e o Mecanismo de Banco de Dados retorna uma mensagem de erro. Isso é verdadeiro mesmo se a operação de adição ou modificação de dados alterar muitas linhas, mas causar apenas uma duplicata. Se for feita uma tentativa de inserir linhas quando houver um índice exclusivo com a opção IGNORE_DUP_KEY definida como ON, as linhas que violam o índice exclusivo serão ignoradas.

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

Aviso

É 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. Recomendamos usar apenas índices alinhados quando o número de partições exceder 1.000.

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, consulte tabelas e índices particionados e o guia de arquitetura e design de índice do SQL Server.

Quando um índice é criado ou recriado, a consulta otimiza as estatísticas de atualizações no índice. Para um índice particionado, o otimizador de consulta usa o algoritmo de amostragem padrão em vez de verificar todas as linhas na tabela em busca de um índice não particionado. 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 SET opções na coluna de valor obrigatório são necessárias sempre que qualquer uma das seguintes condições ocorrer:

• Você cria um índice filtrado.

• Uma instrução INSERT, UPDATEou MERGEDELETEinstrução modifica dados em um índice filtrado.

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

  Opção SET	Valor obrigatório	Valor do servidor padrão	Valor OLE DB e ODBC padrão	Valor DB-Library padrão
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS 1	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

  ¹ Configuração ANSI_WARNINGS para ON definir ARITHABORTON implicitamente quando o nível de compatibilidade do banco de dados é definido como 90 ou superior. Se o nível de compatibilidade do banco de dados estiver definido como 80 ou anterior, a opção ARITHABORT deverá ser definida explicitamente como ON.

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

• Falha ao criar o índice filtrado.
• O Mecanismo de Banco de Dados gera um erro e reverte a INSERTinstrução , UPDATEou MERGE , DELETEque altera 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, consulte Criar índices filtrados e o guia de arquitetura e design de índice do SQL Server.

Índices espaciais

Para obter informações sobre índices espaciais, consulte 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.) Índices em colunas varchar que excedem o limite de bytes poderão ser criados se os dados existentes nas colunas não excederem o limite no momento em que o índice for criado; no entanto, as operações subsequentes de inserção ou atualização nas colunas que fazem com que o tamanho total seja maior do que o limite falham. A chave de índice de um índice clusterizado não pode conter colunas varchar que tenham dados existentes na ROW_OVERFLOW_DATA unidade de alocação. Se um índice clusterizado for criado em uma coluna varchar e os dados existentes estiverem na IN_ROW_DATA unidade de alocação, as operações subsequentes de inserção ou atualização na coluna que enviariam os dados para fora da linha falharão.

Índices não clusterizados podem incluir colunas não chave (incluídas) 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, consulte Criar índices com colunas incluídas e o guia de arquitetura e design de índice do SQL Server.

Observação

Quando as tabelas são particionadas, se as colunas de chave de particionamento ainda não estiverem presentes em um índice clusterizado não exclusivo, elas poderão ser adicionadas ao índice pelo Mecanismo de Banco de Dados. O tamanho combinado das colunas indexadas (sem contar as colunas incluídas) mais qualquer coluna de particionamento adicionada não pode exceder 1.800 bytes em um índice clusterizado não exclusivo.

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. No entanto, o uso da PERSISTED propriedade expande o tipo de 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 exigem que as seguintes SET opções sejam definidas, conforme mostrado na seção anterior , opções set necessárias para índices filtrados.

A UNIQUE restrição ou a PRIMARY KEY restrição pode conter uma coluna computada, desde que atenda a todas as condições de 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.

A criação de um índice em uma coluna computada pode causar a falha de uma operação de inserção ou atualização que funcionou anteriormente. Essa falha pode ocorrer quando a coluna computada resulta em um erro aritmético.

Por exemplo, na tabela a seguir, embora a expressão da coluna c computada pareça resultar em um erro aritmético quando a linha é inserida, a INSERT instrução funciona.

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

No entanto, se você criar um índice na coluna ccomputada, a mesma INSERT instrução 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 Índices em colunas computadas.

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 obtenha todas as informações necessárias de uma verificação ou busca de índice não clusterizado; a tabela ou os dados de índice clusterizados não são acessados. Para obter mais informações, consulte Criar índices com colunas incluídas e o guia de arquitetura e design de índice do SQL Server.

Especificar opções de índice

O SQL Server 2005 (9.x) introduziu novas opções de índice e também modificou a maneira 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 impor uma PRIMARY KEY ou UNIQUE restrição e a definição de índice não for alterada de forma alguma, 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 PRIMARY KEY ou UNIQUE restrição, solte 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 que os índices não clusterizados estejam alinhados anteriormente com o índice clusterizado, eles poderão não estar mais alinhados com ele. Para obter mais informações sobre o alinhamento de índice particionado, consulte tabelas e índices particionados.

A DROP_EXISTING cláusula 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 esteja definida como OFF. Se o índice clusterizado estiver desabilitado, a CREATE INDEX WITH DROP_EXISTING operação deverá ser executada com ONLINE set to OFF. Se um índice não clusterizado estiver desabilitado e não estiver associado a um índice clusterizado desabilitado, a CREATE INDEX WITH DROP_EXISTING operação poderá ser executada com ONLINE set to OFF ou ON.

Observação

Quando índices com 128 extensões ou mais são descartados ou 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. Para obter mais informações, consulte Desalocação adiada.

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 WAIT_AT_LOW_PRIORITY opção de argumento permite que você decida como a operação de índice prossegue quando aguarda um Sch-M bloqueio. Para obter mais informações, consulte WAIT_AT_LOW_PRIORITY

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

Operações de índice retomáveis

aplica-se a: SQL Server 2019 (15.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Você pode tornar uma operação de criação de índice online retomável. Isso significa que o build de índice pode ser interrompido e posteriormente reiniciado a partir do ponto em que ele parou. Para executar um build de índice como retomável, especifique a opção RESUMABLE = ON .

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

• Para usar a opção RESUMABLE , você também deve usar a opção ONLINE .
• A RESUMABLE opção não é mantida nos metadados de um determinado índice e se aplica apenas à duração da instrução DDL atual. Portanto, a cláusula RESUMABLE = ON deve ser especificada explicitamente para habilitar a capacidade de retomada.
• A MAX_DURATION opção pode ser especificada em dois contextos:
  • MAX_DURATION para a opção RESUMABLE especifica o intervalo de tempo para um índice que está sendo recriado. Após esse tempo decorrido, e se a recompilação do índice ainda estiver em execução, ela será pausada. Você decide quando a recompilação de um índice pausado pode ser retomada. O tempo em minutos deve MAX_DURATION ser maior que 0 minutos e menor ou igual a uma semana (7 * 24 * 60 = 10080 minutos). Uma longa pausa em uma operação de índice pode afetar visivelmente o desempenho DML em uma tabela específica, bem como a capacidade do disco de banco de dados, pois tanto o índice original quanto o índice recém-criado exigem espaço em disco e precisam ser atualizados pelas operações DML. Se MAX_DURATION a opção for omitida, a operação de índice continuará até a conclusão ou até que ocorra uma falha.
  • MAX_DURATION para a opção WAIT_AT_LOW_PRIORITY especifica o tempo de espera usando bloqueios de baixa prioridade se a operação de índice estiver bloqueada, antes de tomar medidas. Para obter mais informações, consulte WAIT_AT_LOW_PRIORITY com operações de índice online.
• Para pausar a operação de índice imediatamente, você pode executar o ALTER INDEX PAUSE comando ou executar o KILL <session_id> comando.
• Executar novamente a instrução original CREATE INDEX com os mesmos parâmetros retoma uma operação de build de índice pausada. Você também pode retomar uma operação de build de índice pausada executando a ALTER INDEX RESUME instrução.
• O ABORT comando elimina a sessão que está executando um build de índice e cancela a operação de índice. Você não pode retomar uma operação de índice que foi anulada.

Uma operação de índice retomável é executada até concluir, pausar ou falhar. Caso a operação seja pausada, um erro é emitido indicando que a operação foi pausada e que a criação do índice não foi concluída. Caso a operação falhe, um erro também será emitido.

Para ver se uma operação de índice é executada como uma operação retomável e verificar seu estado de execução atual, use a exibição sys.index_resumable_operations catálogo.

Recursos

Os seguintes recursos são necessários para operações de índice retomáveis:

• Espaço adicional necessário para manter o índice sendo criado, incluindo a hora em que o build é pausado.
• 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.
• Instruções DDL que tentam modificar a tabela associada ao índice que está sendo criado enquanto a operação de índice está pausada não são permitidas.
• 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.
• Se a tabela contiver colunas LOB, um build de índice clusterizado retomável exigirá um bloqueio de modificação de esquema (Sch-M) no início da operação.

Limitações funcionais atuais

As operações de criação de índice retomável têm as seguintes limitações:

• Depois que uma operação de criação de índice online retomável for pausada, o valor MAXDOP inicial não poderá ser alterado.
• A SORT_IN_TEMPDB = ON opção não tem suporte para operações de índice retomáveis.
• O comando DDL com RESUMABLE = ON não pode ser executado dentro de uma transação explícita.
• Você não pode criar um índice retomável que contenha:
  • Colunas computadas ou timestamp (rowversion) como colunas de chave.
  • Coluna LOB como uma coluna incluída.
• Não há suporte para operações de índice retomáveis para:
  • O comando ALTER INDEX REBUILD ALL
  • O comando ALTER TABLE REBUILD
  • Índices columnstore
  • Índices filtrados
  • Índices desabilitados

WAIT_AT_LOW_PRIORITY com operações de índice online

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

Quando você não usa a opção WAIT_AT_LOW_PRIORITY , todas as transações de bloqueio ativas que contêm bloqueios na tabela ou índice devem ser concluídas para que a operação de criação de índice seja iniciada e concluída. Quando a operação de índice online é iniciada e antes de ser concluída, ela precisa adquirir um bloqueio compartilhado (S) ou uma modificação de esquema (Sch-M) na tabela e mantê-la por um curto período de tempo. Embora o bloqueio seja mantido apenas por um curto período de tempo, ele pode afetar significativamente a taxa de transferência da carga de trabalho, aumentar a latência da consulta ou causar tempo limite de execução.

Para evitar esses problemas, a opção WAIT_AT_LOW_PRIORITY permite que você gerencie o comportamento ou Sch-MS bloqueios necessários para que uma operação de índice online seja iniciada e concluída, selecionando entre três opções. Em todos os casos, se durante o tempo de espera especificado por MAX_DURATION = n [minutes] não houver bloqueio que envolva a operação de índice, a operação de índice continuará imediatamente.

WAIT_AT_LOW_PRIORITY faz com que a operação de índice online aguarde usando bloqueios de baixa prioridade, permitindo que outras operações usando bloqueios de prioridade normais prossigam enquanto isso. 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 operação de índice online aguarda usando bloqueios de baixa prioridade. Se a operação for bloqueada por tempo MAX_DURATION , a ação especificada ABORT_AFTER_WAIT será executada. MAX_DURATION o tempo é sempre em minutos e a palavra MINUTES pode ser omitida.

ABORT_AFTER_WAIT= [NONE | | SELFBLOCKERS ]

• NONE: continue aguardando o bloqueio com prioridade normal.
• SELF: saia da operação de índice online que está sendo executada no momento, sem executar nenhuma ação. A opção SELF não pode ser usada quando MAX_DURATION é 0.
• BLOCKERS: encerre todas as transações de usuário que bloqueiam a operação de índice online para que a operação possa continuar. A BLOCKERS opção exige que a entidade de segurança que executa a CREATE INDEX instrução ou ALTER INDEX a instrução tenha a ALTER ANY CONNECTION permissão.

Você pode usar os seguintes eventos estendidos para monitorar as operações de índice que esperam por bloqueios de baixa prioridade:

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

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.

Aviso

Não é recomendável desabilitar bloqueios de linha ou página em um índice. Podem ocorrer problemas relacionados à simultaneidade e determinadas funcionalidades podem estar indisponíveis. Por exemplo, um índice não pode ser reorganizado quando ALLOW_PAGE_LOCKS é definido como OFF.

Chaves sequenciais

Aplica-se a: SQL Server 2019 (15.x) e versões posteriores, no Banco de Dados SQL do Azure e na Instância Gerenciada de 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 são inseridas no final da estrutura de í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 para adquirir uma trava para a página em questão. O tipo de espera correspondente é PAGELATCH_EX.

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.

Observação

A documentação usa o termo árvore B geralmente em referência a índices. Em índices de rowstore, o Database Engine implementa uma árvore B+. Isso não se aplica a índices columnstore ou índice em tabelas com otimização de memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.

Compactação de dados

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

Veja a seguir os principais pontos a serem considerados no contexto das operações de build de índice quando a compactação de dados é usada:

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

Para avaliar como a alteração do estado de compactação afeta o uso de espaço por 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 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 de partições em uma tabela particionada, use a xml_compression coluna da exibição de sys.partitions catálogo.

Estatísticas de índice

Quando um índice rowstore é criado, o Mecanismo de Banco de Dados também cria estatísticas nas colunas de chave do índice. O nome do objeto de estatísticas na exibição do catálogo sys.stats corresponde ao nome do índice. Para um índice não particionado, as estatísticas são criadas usando uma verificação completa dos dados. Para um índice particionado, as estatísticas são compiladas usando o algoritmo de amostragem padrão.

Quando um índice columnstore é criado, o Mecanismo de Banco de Dados também cria um objeto de estatísticas em sys.stats . Esse objeto de estatísticas não contém dados de estatísticas, como o histograma e o vetor de densidade. Ele é usado ao criar um clone de banco de dados por meio do script do banco de dados. Nesse momento, os comandos e os DBCC SHOW_STATISTICS comandos UPDATE STATISTICS ... WITH STATS_STREAM são usados para obter metadados columnstore, como segmento, dicionário e tamanho do repositório delta e adicioná-los às estatísticas no índice columnstore. Esses metadados são obtidos dinamicamente em tempo de compilação de consulta para um banco de dados regular, mas são fornecidos pelo objeto de estatísticas para um clone de banco de dados. O comando UPDATE STATISTICS não tem suporte para o objeto de estatísticas em um índice columnstore em qualquer outro cenário.

Permissões

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

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 do Azure não dá suporte a grupos de arquivos que PRIMARYnão sejam .
• O Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure não dão suporte FILESTREAM a opções.
• Os índices Columnstore não estão disponíveis antes do SQL Server 2012 (11.x).
• As operações de índice retomáveis estão disponíveis a partir do SQL Server 2017 (14.x), no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure.

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 a: SQL Server 2019 (15.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de 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 a: SQL Server 2019 (15.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de 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);

Conteúdo relacionado

• 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