CRIAR ÍNDICE (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Base de dados SQL no Microsoft Fabric

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.

Atualmente, o Azure Synapse Analytics e o Analytics Platform System (PDW) não oferecem suporte a restrições exclusivas. Quaisquer exemplos que referenciam restrições únicas são aplicáveis apenas ao SQL Server, Azure SQL Database, SQL database no Microsoft Fabric e Azure SQL Managed Instance.

Para obter informações sobre diretrizes de design de índice, consulte o guia de design de índice do SQL Server.

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), no Azure SQL Database, SQL database no Microsoft Fabric e no Azure SQL Managed Instance, pode usar um índice não clusterizado num índice de coluna para melhorar o desempenho das consultas de data warehousing. Para obter mais informações, consulte Columnstore indexes - data warehouse.

Para tipos adicionais de índices, consulte:

• CRIAR DE ÍNDICE XML
• CRIAR DE ÍNDICE ESPACIAL
• CRIAR COLUMNSTORE INDEX

Transact-SQL convenções de sintaxe

Sintaxe

Sintaxe para SQL Server, Banco de Dados SQL do Azure, Banco de Dados SQL na Malha, Instância Gerenciada SQL do Azure

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

[ ; ]

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

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

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

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

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

<comparison> ::=
        column_name <comparison_op> constant

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

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

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

Índice relacional compatível com versões anteriores

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

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

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

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

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 estar ou não IGNORE_DUP_KEY definido como ON. 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.

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

AGRUPADOS

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

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

Um modo de exibição com um índice clusterizado exclusivo é chamado de modo de exibição indexado. Uma exibição indexada pode ter apenas um índice clusterizado. 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 vistas 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, o que é uma operação que consome muitos recursos se a tabela for grande.

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

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 no qual a ordem de classificação especificada para as colunas de chave de índice determina a ordem das páginas na estrutura de índice no disco. Ao contrário do índice agrupado, as linhas nas páginas no nível de 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 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 exibição indexada não pode incluir colunas ntext, text ou image , mesmo que elas não sejam referenciadas CREATE INDEX na instrução.

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 CLR, consulte Tipos definidos pelo usuário CLR.

[ ASC | DESC ]

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

INCLUIR (coluna [ ,... n ] )

Especifica as colunas não-chave a serem adicionadas ao nível de folha de um í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 INCLUDE na lista e não podem ser usados simultaneamente como colunas chave e não-chave. Os índices não clusterizados sempre contêm implicitamente 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), no Azure SQL Database, SQL Database no Microsoft Fabric e no Azure SQL Managed Instance, se alguma das colunas não-chave especificadas for varchar(max), nvarchar(max) ou varbinary(max), o índice pode ser construído ou reconstruído usando essa ONLINE opção.

Colunas computadas que são determinísticas e precisas ou imprecisas podem ser incluídas colunas. Colunas computadas derivadas dos 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, 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 de tipo de dados definido pelo usuário (UDT), uma coluna de tipo de dados espaciais ou uma coluna de tipo de dados hierarchyid . Não são permitidas comparações com NULL literais usando 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 UNIQUE índices, 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 sã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 de particionamento para o índice. 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 exclusivo, 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.

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.

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

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 default, neste contexto, não é uma palavra-chave. É 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 QUOTED_IDENTIFIER opção deve ser ON para a sessão atual. Esta é a configuração padrão. Para obter mais informações, consulte SET QUOTED_IDENTIFIER.

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

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

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 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 Observações.

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

O Banco de Dados SQL do Azure dá suporte ao formato de nome de três partes <database_name>.<schema_name>.<object_name> quando <database_name> é o 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 = { EM | DESLIGADO }

Especifica o preenchimento do índice. A predefinição é OFF.

• EM

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

• DESLIGADO

  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. 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 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 seja o valor de FILLFACTOR.

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

FILLFACTOR = fator de enchimento

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 do 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 a fill_factor coluna na exibição do catálogo sys.indexes .

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 é sempre ON a menos que uma compilação de índice retomável seja usada. Para compilações de índice retomáveis, SORT_IN_TEMPDB é sempre OFF.

• EM

  Os resultados de classificação intermediária usados para criar o índice são armazenados em tempdb. Isso pode reduzir o tempo necessário para criar um índice. 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á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 = { 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. A predefinição é OFF.

• EM

  Uma mensagem de aviso ocorre quando valores de chave duplicados são inseridos em um índice exclusivo. Apenas as linhas que violam a restrição de exclusividade não são inseridas.

• DESLIGADO

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

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

Para exibir a configuração de IGNORE_DUP_KEY de um índice, use a coluna ignore_dup_key no sys.indexes exibição de catálogo.

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 são recalculadas. A predefiniçã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.

Definir STATISTICS_NORECOMPUTE como ON não impede a atualização das estatísticas de índice que ocorre durante a operação de reconstrução do índice.

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 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric e Azure SQL Managed Instance

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

Se as estatísticas por partição não forem suportadas, a opção será ignorada e um aviso será gerado. As estatísticas incrementais não são suportadas nos seguintes casos:

• 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. A predefiniçã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. A predefinição é OFF.

• 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 intenção compartilhada (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 curto período de tempo. No final da operação, por um curto período de tempo, um bloqueio compartilhado (S) é 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 reconstruído. ONLINE não pode ser definido como ON 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 o bloqueio durante as operações de índice online. Para obter mais informações, 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, espacial ou XML, ou reconstrói 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 inicialmente adquire um bloqueio compartilhado (S) na tabela. Isso impede modificações da definição de tabela subjacente, mas permite ler e modificar os dados na tabela enquanto a compilação 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, 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 versões mais antigas
• Índices columnstore não clusterizados no SQL Server 2016 (13.x)) e versões mais antigas
• Í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)), no Azure SQL Database, SQL database no Microsoft Fabric e no Azure SQL Managed Instance, quando uma tabela contém colunas varchar(max) ou varbinary(max ), pode ser construído ou reconstruído um índice clusterizado contendo outras colunas usando esta ONLINE opção.
• Í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.

RESUMÍVEL = { EM | DESLIGADO }

Aplica-se a: SQL Server 2019 (15.x) e versões posteriores, Azure SQL Database, SQL database no Microsoft Fabric e Azure SQL Managed Instance

Especifica se uma operação de índice online é retomável. Para obter mais informações, consulte Operações de índice retomáveis e Considerações sobre índice 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 2019 (15.x) e versões posteriores, Azure SQL Database, SQL database no Microsoft Fabric e Azure SQL Managed Instance

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

ALLOW_ROW_LOCKS = { EM | DESLIGADO }

Especifica se os bloqueios de linha são permitidos. A predefiniçã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. A predefiniçã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 2019 (15.x) e versões posteriores, Azure SQL Database, SQL database no Microsoft Fabric e Azure SQL Managed Instance

Especifica se a otimização deve ou não ser otimizada para evitar a contenção de inserção na última página. A predefinição é OFF. Consulte a seção Chaves sequenciais para obter mais informações.

MAXDOP = max_degree_of_parallelism

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

max_degree_of_parallelism pode ser:

• 1

  Suprime a geração de planos paralelos.

• >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, banco de dados ou 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.

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

• LINHA

  As partições de índice ou especificadas são compactadas usando a compactação de linha. Isso não se aplica aos índices columnstore.

• PÁGINA

  As partições de índice ou especificadas são compactadas usando a compactação de página. Isso não se aplica aos índices columnstore.

• COLUMNSTORE

  Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric e Azure SQL Managed Instance

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

• COLUMNSTORE_ARCHIVE

  Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric e Azure SQL Managed Instance

  Aplica-se somente a índices columnstore, incluindo índices columnstore não clusterizados e columnstore clusterizados. COLUMNSTORE_ARCHIVE compacta ainda mais a partição especificada para um tamanho menor. Isso pode ser usado para arquivamento ou para outras situações que exigem um tamanho de armazenamento menor e podem dar 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, Azure SQL Database, base de dados SQL no Microsoft Fabric e Azure SQL Managed Instance

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

  Partições de índice ou especificadas não são compactadas usando compactação XML.

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

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

Índices não agrupados

A partir do SQL Server 2016 (13.x), no Azure SQL Database, SQL Database no Microsoft Fabric e no Azure SQL Managed Instance, pode criar um índice não clusterizado numa tabela armazenada como um índice clustered columnstore. 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.

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 ou modificados. As operações que gerariam valores de chave duplicados 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 IGNORE_DUP_KEY opção definida como ON, as linhas que violam o índice exclusivo serão ignoradas.

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

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

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

• Criar um índice filtrado.

• Uma INSERTinstrução , UPDATE, DELETE, ou MERGE modifica dados em um índice filtrado.

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

  SET opção	Valor necessário	Valor padrão do servidor	Valor OLE DB e ODBC padrão	Valor de 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 ARITHABORT implicitamente quando ON 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 ARITHABORT opção deverá ser definida explicitamente como ON.

Se as SET opções estiverem incorretas, as seguintes condições podem ocorrer:

• A criação do índice filtrado falha.
• O Mecanismo de Banco de Dados gera um erro e reverte a INSERTinstrução , UPDATE, DELETEou MERGE que altera os 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 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 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 colunas varchar 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 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 agrupado 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, as IN_ROW_DATA operaçõ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 (incluídas) 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 guia de design e arquitetura de índice do SQL Server.

Colunas computadas

Os índices podem ser criados em colunas computadas. Além disso, as colunas computadas podem ter a propriedade PERSISTED. Isso significa que o Mecanismo de Banco de Dados armazena os valores computados na tabela e os atualiza quando 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 expande PERSISTED 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 SET opções sejam definidas, conforme mostrado na seção anterior: Opções SET necessárias para índices filtrados.

A UNIQUE restrição ou PRIMARY KEY pode conter uma coluna calculada, 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 um erro aritmético.

Por exemplo, na tabela a seguir, embora a expressão da coluna c calculada 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

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 obtenha todas as informações necessárias de uma verificação ou pesquisa de índice não clusterizada; 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 design e arquitetura de índice do SQL Server.

Especificando 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). 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 PRIMARY KEY restrição ou UNIQUE e a definição do í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 PRIMARY KEY ou UNIQUE restrição, 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 que os índices não agrupados anteriormente estejam 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 DROP_EXISTING cláusula não classifica 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 ONLINE opção esteja definida como OFF. Se o índice clusterizado estiver desabilitado, a operação deverá ser executada CREATE INDEX WITH DROP_EXISTING 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 poderá ser executada CREATE INDEX WITH DROP_EXISTING com ONLINE set como OFF ou ON.

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 WAIT_AT_LOW_PRIORITY opção de argumento permite que você decida como a operação de índice prossegue quando aguarda por 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, Azure SQL Database, SQL database no Microsoft Fabric e Azure SQL Managed Instance

Você pode tornar uma operação de criação de índice on-line retomável. Isso significa que a compilação do índice pode ser interrompida e posteriormente reiniciada a partir do ponto em que parou. Para executar uma compilação de índice como retomável, especifique a RESUMABLE = ON opção.

As seguintes diretrizes 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 opção RESUMABLE não persiste nos metadados de um determinado índice e aplica-se apenas à duração da instrução DDL atual. Portanto, a cláusula RESUMABLE = ON deve ser especificada explicitamente para permitir a retomabilidade.
• A opção MAX_DURATION pode ser especificada em dois contextos:
  • MAX_DURATION para a RESUMABLE opção especifica o intervalo de tempo para um índice que está sendo reconstruído. Após esse tempo, e se a reconstrução do índice ainda estiver em execução, ela será pausada. Você decide quando a reconstrução de um índice pausado pode ser retomada. O tempo em minutos para MAX_DURATION deve ser superior a 0 minutos e inferior ou igual a uma semana (7 * 24 * 60 = 10080 minutos). Uma longa pausa em uma operação de índice pode afetar visivelmente o desempenho do DML em uma tabela específica, bem como a capacidade do disco do banco de dados, uma vez que 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 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 comando ALTER INDEX PAUSE ou executar o comando KILL <session_id>.
• A nova execução da instrução original CREATE INDEX com os mesmos parâmetros retoma uma operação de compilação de índice pausada. Você também pode retomar uma operação de compilação de índice pausada executando a ALTER INDEX RESUME instrução.
• O comando ABORT mata a sessão que está executando uma compilação de índice e cancela a operação de índice. Não é possível retomar uma operação de índice que foi anulada.

Uma operação de índice retomável é executada até ser concluída, pausada 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 é emitido.

Para ver se uma operação de índice é executada como uma operação retomável e para verificar seu estado de execução atual, use o sys.index_resumable_operations exibição de 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 construído, incluindo o tempo em que a compilação é pausada.
• 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.
• Não são permitidas instruções DDL que tentem modificar a tabela associada ao índice que está sendo criado enquanto a operação de índice está pausada.
• 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.
• Se a tabela contiver colunas LOB, uma compilação 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 é pausada, o valor inicial de MAXDOP não pode ser alterado.
• A opção SORT_IN_TEMPDB = ON não é suportada 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.
• Não é possível criar um índice retomável que contenha:
  • Computado ou timestamp (rowversion) coluna(s) como colunas-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 de Columnstore
  • Índices filtrados
  • Índices desativados

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

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric e Azure SQL Managed Instance

Quando você não usa a WAIT_AT_LOW_PRIORITY opção, todas as transações de bloqueio ativas que mantê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ê-lo por um curto período de tempo. Mesmo que 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 tempos limite de execução.

Para evitar esses problemas, a opção WAIT_AT_LOW_PRIORITY permite gerenciar o comportamento de S ou Sch-M bloqueios necessários para iniciar e concluir uma operação de índice online, 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 nenhum bloqueio que envolva a operação de índice, a operação de índice prossegue imediatamente.

WAIT_AT_LOW_PRIORITY faz com que a operação de índice on-line aguarde usando bloqueios de baixa prioridade, permitindo que outras operações usando bloqueios de prioridade normal 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 = tempo [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 pelo MAX_DURATION tempo, a ação ABORT_AFTER_WAIT especificada será executada. MAX_DURATION tempo é sempre em minutos, e a palavra MINUTES pode ser omitida.

ABORT_AFTER_WAIT= [NONE | SELF | BLOCKERS ]

• NONE: Continue aguardando o cadeado com prioridade normal.
• SELF: Saia da operação de índice online que está sendo executada, sem tomar nenhuma ação. A opção SELF não pode ser usada quando MAX_DURATION é 0.
• BLOCKERS: Mate todas as transações do usuário que bloqueiam a operação de índice on-line para que a operação possa continuar. A opção BLOCKERS requer que a entidade que executa a instrução CREATE INDEX ou ALTER INDEX tenha a permissão ALTER ANY CONNECTION.

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

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

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 2019 (15.x) e versões posteriores, no Azure SQL Database, SQL database no Microsoft Fabric e no Azure SQL Managed Instance.

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 inseridas são sequenciais, todas as novas linhas sã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 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 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.

Compressão de dados

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

A seguir estão os pontos-chave a serem considerados no contexto de operações de compilação 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.
• 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.

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

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores, Azure SQL Database, SQL database no Microsoft Fabric e Azure SQL Managed Instance.

Muitas das considerações de 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, use a xml_compression coluna da sys.partitions exibição de catálogo.

Estatísticas do índice

Quando um índice de armazenamento de linhas é criado, o Mecanismo de Banco de Dados também cria estatísticas nas colunas de chave do índice. O nome do objeto statistics na exibição de catálogo sys.stats corresponde ao nome do índice. Para um índice não particionado, as estatísticas são construídas 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 statistics em sys.stats . Este objeto de estatísticas não contém dados estatísticos, como o histograma e o vetor de densidade. Ele é usado ao criar um clone de banco de dados criando scripts para o banco de dados. Nesse momento, os DBCC SHOW_STATISTICS comandos e UPDATE STATISTICS ... WITH STATS_STREAM são usados para obter metadados columnstore, como segment, dicionário e tamanho do repositório delta, e adicioná-los às estatísticas no índice columnstore. Esses metadados são obtidos dinamicamente no momento da compilação da consulta para um banco de dados regular, mas são fornecidos pelo objeto statistics para um clone de banco de dados. O comando UPDATE STATISTICS não é suportado para o objeto statistics em um índice columnstore em nenhum 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 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

• Azure SQL Database e SQL Database no Microsoft Fabric não suportam grupos de ficheiros além de PRIMARY.
• Azure SQL Database, SQL Database no Microsoft Fabric e Azure SQL Managed Instance não suportam FILESTREAM opções.
• Os índices Columnstore não estão disponíveis antes do SQL Server 2012 (11.x).
• Operações de índice retomáveis estão disponíveis a partir do SQL Server 2017 (14.x), no Azure SQL Database, SQL Database no Microsoft Fabric e no Azure SQL Managed Instance.

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, Banco de Dados SQL no Fabric

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 AdventureWorks2025. 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 AdventureWorks2025 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 AdventureWorks2025. 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 AdventureWorks2025. 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, Azure SQL Database, SQL database no Microsoft Fabric e Azure SQL Managed Instance.

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 2019 (15.x) e versões posteriores, Azure SQL Database, SQL database no Microsoft Fabric e Azure SQL Managed Instance

-- 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 2019 (15.x) e versões posteriores, Azure SQL Database, SQL database no Microsoft Fabric e Azure SQL Managed Instance

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

Conteúdo relacionado

• Guia de Design e Arquitetura de Índice do SQL Server
• Executar operações de índice on-line
• Índices e ALTER TABELA
• ÍNDICE ALTER
• CRIAR FUNÇÃO DE PARTIÇÃO
• CRIAR ESQUEMA DE PARTIÇÃO
• CRIAR DE ÍNDICE ESPACIAL
• CRIAR ESTATÍSTICAS
• CRIAR TABELA
• CRIAR DE ÍNDICE XML
• tipos de dados
• DBCC SHOW_STATISTICS
• QUEDA ÍNDICE
• índices XML (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA