Partilhar via

CRIAR ÍNDICE (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure do Azure Synapse AnalyticsAnalytics Platform System (PDW)

Cria um índice relacional em uma tabela ou exibição. Também chamado de índice rowstore porque é um índice de árvore B clusterizado ou não clusterizado. Você pode criar um índice de armazenamento de linha antes que haja dados na tabela. Use um índice de armazenamento de linhas para melhorar o desempenho da consulta, especialmente quando as consultas selecionam colunas específicas ou exigem 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 armazenamento de linha, o Mecanismo de Banco de Dados implementa uma árvore B+. Isso não se aplica a índices columnstore ou índices em tabelas com otimização de memória. Para obter mais informações, consulte o SQL Server e o guia de design de arquitetura e design de índice SQL do Azure.

Atualmente, o Azure Synapse Analytics e o Analytics Platform System (PDW) não oferecem suporte a restrições exclusivas. Quaisquer exemplos que façam referência a restrições exclusivas são aplicáveis apenas ao SQL Server e ao Banco de dados SQL.

Para obter informações sobre diretrizes de design de índice, consulte o SQL Server Index Design Guide.

Exemplos:

  1. Criar um índice não clusterizado em uma tabela ou exibição

    CREATE INDEX index1 ON schema1.table1 (column1);

  2. Criar um índice clusterizado em uma tabela e usar um nome de 3 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-chave:

A partir do SQL Server 2016 (13.x) e do Banco de Dados SQL, você pode usar um índice não clusterizado em um índice columnstore para melhorar o desempenho da consulta de data warehousing. Para obter mais informações, consulte Columnstore Indexes - Data Warehouse.

Para tipos adicionais de índices, consulte:

Transact-SQL convenções de sintaxe

Sintaxe

Sintaxe para SQL Server, Banco de Dados SQL do Azure, Instância Gerenciada do 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 aplicativos que atualmente usam o recurso. Em vez disso, 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 para o Azure Synapse Analytics e o 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

ÚNICO

Cria um índice exclusivo em uma tabela ou exibição. Um índice exclusivo é aquele em que não é permitido que duas linhas tenham o mesmo valor de chave de índice. Um índice clusterizado em um modo de 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á incluem valores duplicados, independentemente de IGNORE_DUP_KEY estar ou não definido como ATIVADO. Se isso for tentado, o Mecanismo de Banco de Dados exibirá uma mensagem de erro. Os valores duplicados devem ser removidos antes que um índice exclusivo possa ser criado na coluna ou colunas. As colunas usadas em um índice exclusivo devem ser definidas como NOT NULL, porque vários valores nulos são considerados duplicados quando um índice exclusivo é criado.

AGRUPADOS

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 agrupado contém as linhas de dados reais da tabela. Uma tabela ou exibição é permitida um índice clusterizado de cada vez.

Um modo de exibição com um índice clusterizado exclusivo é chamado de modo de exibição indexado. A criação de um índice clusterizado exclusivo em um modo de exibição materializa fisicamente o modo de exibição. Um índice clusterizado exclusivo deve ser criado em um modo de exibição antes que qualquer outro índice possa ser definido no mesmo modo de exibição. Para obter mais informações, consulte Criar exibições indexadas.

Crie o índice clusterizado antes de criar qualquer índice não clusterizado. Os índices não clusterizados existentes em tabelas são reconstruídos quando um índice clusterizado é criado.

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

Observação

Como o nível de folha de um índice clusterizado e as páginas de dados são os mesmos por definição, criar um índice clusterizado e usar a cláusula ON partition_scheme_name ou ON filegroup_name efetivamente move uma 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.

NÃO AGRUPADO

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 os índices são criados: implicitamente com restrições PRIMARY KEY e UNIQUE ou explicitamente com CREATE INDEX.

Para modos de exibição indexados, os índices não clusterizados podem ser criados somente em um modo de exibição que tenha um índice clusterizado exclusivo já definido.

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

index_name

O nome do índice. Os nomes de índice devem ser exclusivos dentro de uma tabela ou exibição, mas não precisam ser exclusivos dentro de um banco de dados. Os nomes de índice devem seguir as regras de identificadores.

coluna

A coluna ou colunas nas quais o índice se baseia. Especifique dois ou mais nomes de coluna para criar um índice composto nos valores combinados nas colunas especificadas. Liste as colunas a serem incluídas no índice composto, em ordem de prioridade de classificação, dentro dos parênteses após table_or_view_name.

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

As colunas que são dos tipos de dados de objeto grande (LOB) ntext, de texto , varchar(max), nvarchar(max), varbinary(max), xmlou imagem não podem ser especificadas como colunas chave para um índice. Além disso, uma definição de modo de exibição não pode incluir ntext, de texto ou imagem colunas, mesmo que elas não sejam referenciadas na instrução CREATE INDEX.

Você pode criar índices em colunas de tipo definidas pelo usuário CLR se o tipo suportar ordenação binária. Você também pode criar índices em colunas computadas que são definidas como invocações de método em uma coluna de tipo definida pelo usuário, desde que os métodos sejam marcados determinísticos e não executem operações de acesso a dados. Para obter mais informações sobre como indexar colunas de tipo definidas pelo usuário do CLR, consulte Tipos definidos pelo usuário do CLR.

[ ASC | DESC ]

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

INCLUIR (coluna [ ,... n ] )

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

Os nomes das colunas não podem ser repetidos na lista INCLUIR e não podem ser usados simultaneamente como colunas chave e não-chave. Os índices não clusterizados sempre contêm as colunas de índice clusterizadas se um índice clusterizado for definido na tabela. Para obter mais informações, consulte Criar índices com colunas incluídas.

Todos os tipos de dados são permitidos, exceto de texto, ntext e de imagem. A partir do SQL Server 2012 (11.x) e do Banco de Dados SQL do Azure, se qualquer uma das colunas não-chave especificadas estiver varchar(max), nvarchar(max)ou tipos de dados varbinary(max), o índice poderá ser criado ou reconstruído usando a opção ONLINE.

Colunas computadas que são determinísticas e precisas ou imprecisas podem ser incluídas colunas. Colunas computadas derivadas de de imagem, ntext, de texto , varchar(max), nvarchar(max), varbinary(max)e tipos de dados xml podem ser incluídos em colunas não-chave, desde que os tipos de dados de coluna computados sejam permitidos 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, consulte CREATE XML INDEX.

ONDE <filter_predicate>

Cria um índice filtrado especificando quais linhas incluir no índice. 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 fazer referência a uma coluna computada, uma coluna UDT, uma coluna de tipo de dados espaciais ou uma coluna de tipo de dados hierarchyID. Comparações usando literais NULL não são permitidas com os operadores de comparação. Em vez disso, use os operadores IS NULL e IS NOT NULL.

Aqui estão alguns exemplos de predicados de filtro para a 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

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

EM 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 dentro do banco de dados executando CREATE PARTITION SCHEME ou ALTER PARTITION SCHEME. column_name especifica a coluna em relação à qual um índice particionado será particionado. Esta coluna deve corresponder ao tipo de dados, comprimento e precisão do argumento da função de partição que partition_scheme_name está usando. column_name não se restringe às colunas na definição de índice. Qualquer coluna na tabela base pode ser especificada, exceto ao particionar um índice UNIQUE, column_name deve ser escolhida entre as usadas como chave exclusiva. Essa restrição permite que o Mecanismo de Banco de Dados verifique a exclusividade dos valores de chave apenas em uma única partição.

Observação

Quando você particiona um índice clusterizado não exclusivo, o Mecanismo de Banco de Dados, por padrão, adiciona a coluna de particionamento à lista de chaves de índice clusterizadas, se ainda não estiver especificado. Ao particionar um índice não exclusivo e não clusterizado, o Mecanismo de Banco de Dados adiciona a coluna de particionamento como uma coluna não-chave (incluída) do índice, se ainda não estiver especificado.

Se partition_scheme_name ou de grupo de arquivos não for especificado e a tabela for 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 particionamento de índices, Tabelas e índices particionados.

EM filegroup_name

Cria o índice especificado no grupo de arquivos especificado. Se nenhum local for especificado e a tabela ou exibição não estiver 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 grupo de arquivos ou esquema de partição que a tabela ou exibição.

O termo padrão, neste contexto, não é uma palavra-chave. É um identificador para o grupo de arquivos padrão e deve ser delimitado, como em ON "default" ou ON [default]. Se "default" for especificado, a opção QUOTED_IDENTIFIER deve estar ON para a sessão atual. Esta é a configuração padrão. Para obter mais informações, consulte SET QUOTED_IDENTIFIER.

Observação

"Default" não indica o grupo de arquivos padrão do banco de dados no contexto de CREATE INDEX. Isso difere de CREATE TABLE, onde "default" localiza a tabela no grupo de arquivos padrão do banco de dados.

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

Especifica o posicionamento dos dados FILESTREAM para a tabela quando um índice clusterizado é criado. A cláusula FILESTREAM_ON permite que os dados FILESTREAM sejam movidos para um grupo de arquivos FILESTREAM ou esquema de partição diferente.

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 CREATE DATABASE ou instrução ALTER DATABASE; caso contrário, um erro será gerado.

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

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

FILESTREAM_ON NULL pode 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, consulte FILESTREAM (SQL Server).

<objeto>::=

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 um modo de exibição antes que qualquer índice não clusterizado seja criado. Para obter mais informações sobre modos de exibição indexados, 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 o 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 = { EM | DESLIGADO }

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

EM
A porcentagem de espaço livre especificada por de fator de preenchimento é aplicada às páginas de nível intermediário do índice.

A de fator de preenchimento OFF ou não é especificada
As páginas de nível intermediário são preenchidas até perto da capacidade, deixando espaço suficiente para pelo menos uma linha do tamanho máximo que o índice pode ter, considerando o conjunto de teclas nas páginas intermediárias.

A opção PAD_INDEX é útil somente quando FILLFACTOR é especificado, porque PAD_INDEX usa a porcentagem especificada 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á internamente a porcentagem para permitir o mínimo. O número de linhas em uma página de índice intermediário nunca é inferior a dois, independentemente de quão baixo o valor de fator de preenchimento.

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

FILLFACTOR = fillfactor

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

A configuração FILLFACTOR se aplica somente quando o índice é criado ou reconstruído. O Mecanismo de Banco de Dados não mantém dinamicamente a porcentagem especificada de espaço vazio nas páginas.

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

Importante

A criação de um índice clusterizado com um FILLFACTOR inferior a 100 afeta a quantidade de espaço de armazenamento que os dados ocupam porque o Mecanismo de Banco de Dados redistribui os dados quando cria o índice clusterizado.

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

SORT_IN_TEMPDB = { EM | DESLIGADO }

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

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

DESLIGADO
Os resultados da classificação intermediária 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 armazenar os resultados de classificação intermediários. 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 = { EM | DESLIGADO }

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

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

DESLIGADO
Uma mensagem de erro ocorrerá quando 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 ATIVADO 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 = { LIGADO | DESLIGADO}

Especifica se as estatísticas de distribuição são recalculadas. O padrão é OFF.

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

DESLIGADO
A atualização automática de estatísticas está ativada.

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

Importante

A desativação do recálculo automático de estatísticas de distribuição pode impedir que o otimizador de consultas escolha planos de execução ideais para consultas que envolvam a tabela.

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

STATISTICS_INCREMENTAL = { LIGADO | DESLIGADO }

Aplica-se a: SQL Server (a partir do SQL Server 2014 (12.x)) e Banco de Dados SQL do Azure

Quando ON, as estatísticas criadas são por estatísticas de partição. Quando OFF, a árvore de estatísticas é descartada e o SQL Server recalcula as estatísticas. O padrão é OFF.

Se as estatísticas por partição não forem suportadas, a opção será ignorada e um aviso será gerado. Não há suporte para estatísticas incrementais para os seguintes tipos de estatísticas:

  • Estatísticas criadas com índices que não estão alinhados com a tabela base.
  • Estatísticas criadas em bases de dados secundárias legíveis Always On.
  • Estatísticas criadas em bases de dados só de leitura.
  • Estatísticas criadas em índices filtrados.
  • Estatísticas criadas em visualizações.
  • Estatísticas criadas em tabelas internas.
  • Estatísticas criadas com índices espaciais ou índices XML.

DROP_EXISTING = { EM | DESLIGADO }

É uma opção para descartar e reconstruir o índice clusterizado ou não clusterizado existente com especificações de coluna modificadas e manter o mesmo nome para o índice. O padrão é OFF.

EM
Especifica para descartar e reconstruir o índice existente, que deve ter o mesmo nome que o parâmetro index_name.

DESLIGADO
Especifica não descartar e reconstruir o índice existente. O SQL Server exibirá um erro se o nome de índice especificado já existir.

Com DROP_EXISTING, você pode alterar:

  • Um índice de armazenamento de linhas não clusterizado para um índice de armazenamento de linhas clusterizado.

Com DROP_EXISTING, não é possível alterar:

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

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

ONLINE = { LIGADO | DESLIGADO }

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 do Microsoft SQL Server. Para obter uma lista de recursos suportados pelas edições do SQL Server, consulte edições e recursos com suporte do SQL Server 2022.

EM
Os bloqueios de tabela de longo prazo não são mantidos durante a operação de índice. Durante a fase principal da operação de índice, apenas um bloqueio de compartilhamento de intenção (IS) é mantido na tabela de origem. Isso permite que as consultas ou atualizações da tabela e dos índices subjacentes prossigam. No início da operação, um bloqueio compartilhado (S) é mantido no objeto de origem por um período muito curto de tempo. No final da operação, por um curto período de tempo, um bloqueio S (compartilhado) é adquirido na origem se um índice não clusterizado estiver sendo criado. Um bloqueio Sch-M (Modificação de Esquema) é adquirido quando um índice clusterizado é criado ou descartado online e quando um índice clusterizado ou não clusterizado está sendo reconstruído. ONLINE não pode ser definido como ON quando um índice está sendo criado em uma tabela temporária local.

Observação

A criação de índice online pode definir as opções de low_priority_lock_wait, consulte WAIT_AT_LOW_PRIORITY com operações de índice online.

DESLIGADO
Os bloqueios de tabela são aplicados durante a operação de índice. Uma operação de índice offline que cria, reconstrói ou descarta um índice clusterizado, ou recria ou descarta um índice não clusterizado, adquire um bloqueio de modificação de esquema (Sch-M) na tabela. Isso impede que todo o usuário acesse a tabela subjacente durante 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 Executar operações de índice online.

Índices, incluindo índices 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 um modo de exibição
  • Índices clusterizados desativados
  • Índices columnstore clusterizados no SQL Server 2017 (14.x)) e mais antigos
  • Índices columnstore não clusterizados no SQL Server 2016 (13.x)) e mais antigos
  • Índice agrupado, se a tabela subjacente contiver tipos de dados LOB (imagem, ntext, texto) e tipos de dados espaciais
  • colunas varchar(max) e varbinary(max) não podem fazer parte de uma chave de índice. No SQL Server (a partir do SQL Server 2012 (11.x)) e no Banco de Dados SQL do Azure, quando uma tabela contém colunas varchar(max) ou varbinary(max), um índice clusterizado contendo outras colunas pode ser criado ou reconstruído 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 on-line.

RESUMÍVEL = { EM | DESLIGADO }

Aplica-se a: SQL Server (a partir do SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

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

EM
A operação de índice é retomável.

DESLIGADO
A operação de índice não é retomável.

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

Aplica-se a: SQL Server (a partir do SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

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

Importante

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

Observação

Não há suporte para reconstruções de índice online retomáveis em índices columnstore ou índices desabilitados.

ALLOW_ROW_LOCKS = { EM | DESLIGADO }

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

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

DESLIGADO
Os bloqueios de linha não são usados.

ALLOW_PAGE_LOCKS = { EM | DESLIGADO }

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

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

DESLIGADO
Os bloqueios de página não são usados.

OTIMIZE_FOR_SEQUENTIAL_KEY = { EM | DESLIGADO }

Aplica-se a: SQL Server (a partir do SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

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

MAXDOP = max_degree_of_parallelism

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

max_degree_of_parallelism pode ser:

1
Suprime a geração de planos paralelos.

>1
Restringe o número máximo de processadores usados em uma operação de índice paralelo ao número especificado 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.

Observação

Operações de índice paralelo não estão disponíveis em todas as edições do Microsoft SQL Server. Para obter uma lista de recursos suportados pelas edições do SQL Server, consulte edições e recursos com suporte do 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 seguintes:

NENHUM
As partições de índice ou especificadas não são compactadas.

LINHA
As partições de índice ou especificadas são compactadas usando a compactação de linha.

PÁGINA
As partições de índice ou especificadas são compactadas usando a compactação de página.

Para obter mais informações sobre compactação, consulte de 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 SQL do Azure.

Especifica a opção de compactação XML para o índice especificado que contém uma ou mais colunas xml tipo de dados. As opções são as seguintes:

EM
As partições de índice ou especificadas são compactadas usando a compactação XML.

DESLIGADO
As partições de índice ou especificadas não são compactadas.

EM PARTIÇÕES ( { <partition_number_expression> | <intervalo> } [ ,...n ] )

Especifica as partições às quais as configurações de DATA_COMPRESSION ou XML_COMPRESSION se aplicam. Se o índice não estiver 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 se aplicará a todas as partições de um índice particionado.

<partition_number_expression> podem ser especificados das seguintes formas:

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

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

Para definir diferentes tipos 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 economizar em operações de E/S, o processador de consultas 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 mais processadores para executar 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 operação CREATE INDEX pode 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 a sessão termina, os índices são descartados.

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 termina, o índice é descartado.

Os índices suportam propriedades estendidas.

CREATE INDEX não é suportado no Microsoft Fabric.

Índices agrupados

Criar um índice clusterizado em uma tabela (heap) ou descartar e recriar um índice clusterizado existente requer espaço de trabalho 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 de índice clusterizados existentes. Para obter mais informações sobre índices clusterizados, consulte Create Clustered Indexes e o SQL Server Index Architecture and Design Guide.

Índices não agrupados

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 heap ou índice clusterizado, o índice persistirá se você converter posteriormente a tabela em um índice columnstore clusterizado. Também não é necessário descartar o índice não clusterizado ao reconstruir 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 únicos

Quando existe um índice exclusivo, o Mecanismo de Banco de Dados verifica se há valores duplicados sempre que os dados são adicionados por operações de inserção. As operações de inserção que gerariam valores de chave duplicados são revertidas e o Mecanismo de Banco de Dados exibe uma mensagem de erro. Isso é verdadeiro mesmo se a operação de inserção altera muitas linhas, mas causa 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 estiver definida como ON, somente as linhas que violam o índice UNIQUE falharão.

Índices particionados

Os índices particionados são criados e mantidos de maneira semelhante às tabelas particionadas, mas, como índices comuns, eles são manipulados como objetos de banco de dados separados. Você pode ter um índice particionado em uma tabela que não está particionada e pode ter um índice não particionado em uma tabela que está particionada.

Se você estiver criando um índice em uma tabela particionada e não especificar um grupo de arquivos no qual colocar o índice, o índice será particionado da mesma maneira que a tabela subjacente. Isso ocorre porque os índices, por padrão, 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, o índice é alinhado com a tabela.

Advertência

Criar e reconstruir índices não alinhados em uma tabela com mais de 1.000 partições é possível, mas não é suportado. Isso pode causar desempenho degradado 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, o Mecanismo de Banco de Dados por padrão adiciona quaisquer colunas de particionamento à lista de chaves de índice clusterizadas, se ainda não tiver sido especificado.

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 de tabelas e índices particionados e o Guia de Arquitetura e Design de Índice do SQL Server.

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

Índices filtrados

Um índice filtrado é um índice não clusterizado otimizado, adequado para 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, reduzir os custos de armazenamento e os custos 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 qualquer uma das seguintes condições:

  • Crie um índice filtrado.

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

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

    Opções SET Valor necessário Valor padrão do servidor Inadimplência

    Valor OLE DB e ODBC    		 Inadimplência

    Valor DB-Library
    ANSI_NULLS EM EM EM DESLIGADO
    ANSI_PADDING EM EM EM DESLIGADO
    ANSI_WARNINGS* EM EM EM DESLIGADO
    ARITHABORT EM EM DESLIGADO DESLIGADO
    CONCAT_NULL_YIELDS_NULL EM EM EM DESLIGADO
    NUMERIC_ROUNDABORT DESLIGADO DESLIGADO DESLIGADO DESLIGADO
    QUOTED_IDENTIFIER EM EM EM DESLIGADO
    • Definir ANSI_WARNINGS como ON define implicitamente ARITHABORT como ON 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 opções SET estiverem incorretas, as seguintes condições podem ocorrer:

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

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

Índices espaciais

Para obter informações sobre índices espaciais, consulte CREATE SPATIAL INDEX e Spatial Indexes Overview.

Índices XML

Para obter informações sobre índices XML, consulte CREATE XML INDEX e XML Indexes (SQL Server).

Tamanho da chave de índice

O tamanho máximo para uma chave de índice é 900 bytes para um índice clusterizado e 1.700 bytes para um índice não clusterizado. (Antes do Banco de Dados SQL e do SQL Server 2016 (13.x), o limite sempre era de 900 bytes.) Índices em varchar colunas que excedem o limite de bytes podem ser criados se os dados existentes nas colunas não excederem o limite no momento em que o índice é criado; No entanto, as ações subsequentes de inserção ou atualização nas colunas que fazem com que o tamanho total seja maior do que o limite falharão. A chave de índice 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 empurrariam os dados para fora da linha falharão.

Os índices não agrupados podem incluir colunas não-chave no nível de 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 SQL Server Index Architecture and Design Guide.

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 serão adicionadas ao índice pelo Mecanismo de Banco de Dados. O tamanho combinado das colunas indexadas (sem contar as colunas incluídas), mais quaisquer colunas de particionamento adicionadas, não pode exceder 1800 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 quaisquer outras colunas das quais a coluna computada depende são atualizadas. O Mecanismo de Banco de Dados usa esses valores persistentes quando cria um índice na coluna e quando o índice é referenciado em uma consulta.

Para indexar uma coluna calculada, a coluna calculada deve ser determinística e precisa. No entanto, o uso da propriedade PERSISTED expande o tipo de colunas computadas indexáveis para incluir:

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

As colunas computadas persistentes exigem que as seguintes opções SET sejam definidas, conforme 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, desde que satisfaça todas as condições de indexação. Especificamente, a coluna calculada deve ser determinística e precisa ou determinística e persistente. Para obter mais informações sobre determinismo, consulte Deterministic and Nondeterministic Functions.

As colunas computadas derivadas de de imagem, ntext, de texto , varchar(max), nvarchar(max), varbinary(max)e xml tipos de dados podem ser indexados como uma coluna chave ou coluna não-chave incluída, desde que o tipo de dados de coluna computada seja permitido como uma coluna de chave de índice ou coluna não-chave. Por exemplo, não é possível criar um índice XML primário em uma coluna de 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 funcionou anteriormente. Tal falha pode ocorrer quando a coluna calculada resulta em erro aritmético. Por exemplo, na tabela a seguir, embora o c de coluna computado 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 na coluna calculada c, a mesma instrução INSERT falhará agora.

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

Colunas não-chave, chamadas colunas incluídas, podem ser adicionadas ao nível de folha de um índice não clusterizado para melhorar o desempenho da consulta cobrindo a consulta. Ou seja, todas as colunas referenciadas na consulta são incluídas no índice como colunas chave ou não-chave. Isso permite que o otimizador de consulta localize todas as informações necessárias de uma verificação de índice; 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 SQL Server Index Architecture and Design Guide.

Especificando opções de índice

O SQL Server 2005 (9.x) introduziu novas opções de índice e também modifica 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). Quando você define opções de índice, as seguintes regras se aplicam:

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

DROP_EXISTING cláusula

Você pode usar a cláusula DROP_EXISTING para reconstruir o índice, adicionar ou soltar colunas, modificar opções, modificar a ordem de classificação das colunas ou alterar o esquema de partição ou o grupo de arquivos.

Se o índice impor uma restrição de CHAVE PRIMÁRIA ou EXCLUSIVA e a definição de índice não for alterada de forma alguma, o índice será descartado e recriado, preservando a restrição existente. No entanto, se a definição do índice for alterada, a instrução falhará. Para alterar a definição de uma restrição de CHAVE PRIMÁRIA ou ÚNICA, elimine 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 ou diferente de chaves, em uma tabela que também tem índices não clusterizados. DROP_EXISTING substitui a execução de uma instrução DROP INDEX no índice clusterizado antigo seguida pela execução de uma instrução CREATE INDEX para o novo índice clusterizado. Os índices não agrupados são reconstruídos uma vez e, em seguida, somente se a definição do índice tiver sido alterada. A cláusula DROP_EXISTING não reconstrói os índices não clusterizados quando a definição de índice tem o mesmo nome de índice, colunas de chave e partição, atributo de exclusividade e ordem de classificação que o índice original.

Quer os índices não clusterizados sejam reconstruídos ou não, eles 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 reconstruído 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 agrupados anteriormente alinhados com o índice agrupado, eles podem não estar mais alinhados com ele. Para obter mais informações sobre alinhamento de índice particionado, consulte Tabelas e índices particionados.

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

Observação

Quando índices com 128 extensões ou mais são descartados ou reconstruídos, o Mecanismo de Banco de Dados adia os locais de negociação de página reais e seus bloqueios associados até que a transação seja confirmada.

Opção ONLINE

As seguintes diretrizes se aplicam à execução de operações de índice on-line:

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

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

Recursos

Os seguintes recursos são necessários para a operação de criação de índice on-line retomável:

  • Espaço adicional necessário para manter o índice sendo construído, incluindo o tempo em que o índice está sendo pausado
  • Taxa de transferência de log adicional durante a fase de classificação. O uso geral do espaço de log para índice retomável é menor em comparação com a criação regular de índice on-line e permite o truncamento de log durante essa operação.
  • Um estado DDL impedindo qualquer modificação DDL
  • A limpeza fantasma é bloqueada no índice de compilação durante a operação durante a pausa e enquanto a operação está em execução.

Limitações funcionais atuais

A funcionalidade a seguir 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

  • Crie um índice que contenha:

    • Coluna(s) computada(s) ou carimbo de data/hora como colunas chave
    • Coluna LOB como coluna incluída para criação de índice retomável
    • Índice filtrado

Operações de índice retomáveis

Aplica-se a: SQL Server (a partir do SQL Server 2019 (15.x)) e Banco de Dados SQL do Azure

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

  • 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 de um determinado índice e aplica-se apenas à duração de uma instrução DDL atual. Portanto, a cláusula RESUMABLE = ON deve ser especificada explicitamente para permitir a retomabilidade.
  • MAX_DURATION opção só é suportada para RESUMABLE = ON opção.
  • MAX_DURATION opção RESUMABLE especifica o intervalo de tempo para um índice que está sendo criado. Uma vez que esse tempo é usado, a compilação de índice é pausada ou conclui sua execução. O usuário decide quando uma compilação para um índice pausado pode ser retomada. O tempo em minutos para MAX_DURATION deve ser maior que 0 minutos e menor ou igual a uma semana (7 * 24 * 60 = 10080 minutos). Ter uma longa pausa para uma operação de índice pode afetar o desempenho do DML em uma tabela específica, bem como a capacidade do disco do banco de dados, uma vez que ambos os índices o original e o recém-criado exigem espaço em disco e precisam ser atualizados durante as operações DML. Se MAX_DURATION opção 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 parar (Ctrl-C) o comando em andamento, executar o comando ALTER INDEX PAUSE ou executar o comando KILL <session_id>. Uma vez pausado, o comando pode ser retomado usando comando ALTER INDEX.
  • A nova execução da instrução CREATE INDEX original para índice retomável retoma automaticamente uma operação de criação de índice pausada.
  • A opção SORT_IN_TEMPDB = ON não é suportada para índice retomável.
  • O comando DDL com RESUMABLE = ON não pode ser executado dentro de uma transação explícita (não pode fazer parte do bloco start TRAN ... COMMIT).
  • Para retomar/abortar uma criação/reconstrução de índice, use o ALTER INDEX sintaxe T-SQL.
  • Não há suporte para índices desabilitados.

Observação

O comando DDL é executado até ser concluído, pausado ou falhar. Caso o comando pause, será emitido um erro indicando que a operação foi pausada e que a criação do índice não foi concluída. Mais informações sobre o status atual do índice podem ser obtidas em sys.index_resumable_operations. Como antes, em caso de falha, um erro também será emitido.

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

WAIT_AT_LOW_PRIORITY com operações de índice on-line

Aplica-se a: Esta sintaxe para CREATE INDEX atualmente se aplica apenas ao SQL Server 2022 (16.x), Banco de Dados SQL do Azure e Instância Gerenciada SQL do Azure. Por ALTER INDEX, essa sintaxe se aplica ao SQL Server (começando com o SQL Server 2014 (12.x)) e ao Banco de Dados SQL do Azure. Para obter mais informações, consulte ALTER INDEX.

A sintaxe low_priority_lock_wait permite especificar WAIT_AT_LOW_PRIORITY comportamento. WAIT_AT_LOW_PRIORITY só pode ser utilizado com ONLINE=ON.

A opção WAIT_AT_LOW_PRIORITY permite que os DBAs gerenciem os bloqueios de Sch-S e Sch-M necessários para a criação de índices on-line e permite que eles selecionem uma das 3 opções. Em todos os 3 casos, se durante o tempo de espera MAX_DURATION = n [minutes], não houver atividades de bloqueio, a reconstrução do índice on-line é executada imediatamente sem esperar e a instrução DDL é concluída.

WAIT_AT_LOW_PRIORITY indica que a operação de criação de índice online aguardará bloqueios de baixa prioridade, permitindo que outras operações prossigam enquanto a operação de compilação 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 = tempo [MINUTOS]

O tempo de espera (um valor inteiro especificado em minutos) que o índice online cria bloqueia aguardará com baixa prioridade ao executar o comando DDL. Se a operação for bloqueada pelo MAX_DURATION tempo, a ação de ABORT_AFTER_WAIT especificada será executada. MAX_DURATION tempo é sempre em minutos, e a palavra MINUTOS pode ser omitida.

ABORT_AFTER_WAIT = [NENHUM | BLOQUEADORES DE | } ]

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

SELF Saia do índice on-line criar operação DDL atualmente sendo executada, sem tomar qualquer ação. A opção SELF não pode ser usada com uma MAX_DURATION de 0.

BLOCKERS Mate todas as transações do usuário que bloqueiam a operação DDL de reconstrução do índice on-line para que a operação possa continuar. A opção BLOCKERS requer que o login tenha permissão .

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

Quando ALLOW_ROW_LOCKS = ON e ALLOW_PAGE_LOCK = ON, bloqueios de linha, página e nível de tabela são permitidos ao acessar o índice. O Mecanismo de Banco de Dados escolhe o bloqueio apropriado e pode escalá-lo de um bloqueio de linha ou página para um bloqueio de tabela.

Quando ALLOW_ROW_LOCKS = OFF e ALLOW_PAGE_LOCK = OFF, apenas um bloqueio no nível da tabela é permitido ao acessar o índice.

Chaves sequenciais

Aplica-se a: SQL Server (a partir do 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 principal contém valores que estão sempre aumentando (ou diminuindo), 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 à disputa pela página na memória, que pode ser observada como vários tópicos esperando em PAGELATCH_EX para a página em questão.

Habilitar a opção de índice OPTIMIZE_FOR_SEQUENTIAL_KEY permite uma otimização dentro do mecanismo de banco de dados que ajuda a melhorar a taxa de transferência para inserções de alta simultaneidade no índice. Destina-se a índices que têm uma chave sequencial e, portanto, são propensos a contenção de inserção de última página, mas também pode ajudar com índices que têm pontos de acesso em outras áreas da estrutura de índice B-Tree.

Observação

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

Visualizando informações de índice

Para retornar informações sobre índices, você pode usar exibições de catálogo, funções do sistema e procedimentos armazenados do sistema.

Compressão de dados

A compactação de dados é descrita no tópico Compactação de dados. Seguem-se os principais pontos a considerar:

  • A compactação pode permitir que mais linhas sejam armazenadas em uma página, mas não altera o tamanho máximo da linha.
  • As páginas que não são folhas de um índice não são comprimidas por página, mas podem ser comprimidas 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, o índice clusterizado herda o estado de compactação do heap, a menos que um estado de compactação alternativo seja especificado.

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

  • Não é 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 ... reconstrói a partição especificada do índice.
  • A sintaxe ALTER INDEX <index> ... REBUILD WITH ... reconstrói 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

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

Muitas das mesmas considerações para compactação de dados se aplicam à compactação XML. Você também deve estar ciente das seguintes considerações:

  • 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 a compactação XML. Quando uma tabela ou índice é criado, a compactação de dados XML é desabilitada, a menos que especificado de outra forma. Quando uma tabela é modificada, a compactação existente é preservada, a menos que especificado de outra forma.
  • Se você especificar uma lista de partições ou uma partição fora do intervalo, um erro será gerado.
  • Quando um índice clusterizado é criado em uma pilha, o índice clusterizado herda o estado de compactação XML da pilha, a menos que uma opção de compactação alternativa seja especificada.
  • Alterar a configuração de compactação XML de uma pilha requer que todos os índices não clusterizados na tabela sejam reconstruídos para que tenham ponteiros para os novos locais de linha na pilha.
  • Você pode habilitar ou desabilitar a compactação XML online ou offline. A habilitação da compactação em uma pilha é de thread único para uma operação online.
  • Para determinar o estado de compactação XML de partições em uma tabela particionada, consulte a coluna xml_compression da exibição de catálogo sys.partitions.

Permissões

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

Limitações e restrições

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

  • Um índice de armazenamento de linha clusterizado ou não clusterizado em uma tabela de data warehouse quando um índice columnstore já existe. Esse comportamento é diferente do SMP SQL Server, que permite que os índices rowstore e columnstore coexistam na mesma tabela.
  • Não é possível criar um índice em uma exibição.

Metadados

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

Notas de versão

O Banco de dados SQL não oferece suporte a opções de grupo de arquivos e fluxo de arquivos.

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

Um. Criar um índice de armazenamento de linhas simples não clusterizado

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 de armazenamento de linha 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 IX_FF de índice com duas colunas do dbo. Tabela FactFinance. A próxima instrução reconstrói 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. Criar 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á exclusividade nos 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 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 novamente com a opção definida como OFF. Uma única linha é inserida na tabela #Test que causará intencionalmente um valor duplicado quando a segunda instrução INSERT de várias linhas 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

Eis os resultados da segunda declaraçã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 foi ignorada, mas toda a transação 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

Eis os resultados da segunda declaraçã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 das linhas da tabela Production.UnitMeasure foi inserida na tabela, mesmo que apenas uma linha na tabela violasse a restrição de índice UNIQUE.

G. Usando DROP_EXISTING para soltar e recriar um índice

O exemplo a seguir descarta 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 estã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 numa vista

O exemplo a seguir cria um modo de exibição e um índice nesse modo de exibição. São incluídas duas consultas que usam o modo de exibição indexado.

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

Eu. Criar 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). Segue-se uma consulta coberta pelo índice. Para exibir o índice selecionado pelo otimizador de consulta, no menu de 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 no 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 no banco de dados AdventureWorks2022. O predicado de filtro pode incluir colunas que não são colunas de chave no índice filtrado. O predicado neste 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 em 1 de partição do índice e a compactação de linha em 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 compactação XML

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada 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 deve ser o xml tipo de dados.

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áveis

Aplica-se a: SQL Server (a partir do 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 diferentes estratégias 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 se aplica à opção ABORT_AFTER_WAIT, o segundo se aplica à 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 and Analytics Platform System (PDW)

P. Sintaxe básica

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

Aplica-se a: SQL Server (a partir do 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 uma 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);

Ver também