CREATE INDEX (Transact-SQL)
Cria um índice relacional em uma tabela ou exibição. Também chamado de um índice rowstore porque é um índice de árvore B clusterizado ou não clusterizado. Você pode criar um índice rowstore antes que haja dados na tabela. Use um índice rowstore para melhorar o desempenho de consulta, especialmente quando as consultas forem selecionadas de colunas específicas ou exigirem que os valores sejam classificados em uma ordem específica.
A documentação do SQL Server usa o termo árvore B geralmente em referência a índices. Em índices de armazenamento de linha, o SQL Server implementa uma árvore B+. Isso não se aplica a índices columnstore ou armazenamentos de dados na memória. Saiba mais no Guia de design e arquitetura de índice do SQL Server.
O Azure Synapse Analytics e o PDW (Analytics Platform System) não são compatíveis com restrições exclusivas. Quaisquer exemplos fazendo referência a restrições exclusivas são aplicáveis somente a SQL Server e Banco de Dados SQL.
Para obter informações sobre as diretrizes de design de índice, confira o Guia de design de índice do SQL Server.
Exemplos:
Para criar um índice não clusterizado em uma tabela.
CREATE INDEX index1 ON schema1.table1 (column1);
CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
Cenário principal:
Começando com o SQL Server 2022 (13.x) e o Banco de Dados SQL, use um índice não clusterizado em um índice columnstore para melhorar o desempenho da consulta de armazenamento de dados. Para obter mais informações, veja Índices Columnstore – data warehouse.
Para ver tipos de índices adicionais, consulte:
CREATE XML INDEX CREATE SPATIAL INDEX CREATE COLUMNSTORE INDEX.
Sintaxe.
Sintaxe do SQL Server, do Banco de Dados SQL do Azure, da Instância Gerenciada de SQL do Azure.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index name ON ( column [ ASC | DESC ] [ . n ] ) [ INCLUDE ( column name [ . n ] ) ] [ WHERE ] [ WITH ( [ . n ] ) ] [ ON ] [ FILESTREAM ON ] [ ; ] ::= ::= | FILLFACTOR = fillfactor | SORT IN TEMPDB = | IGNORE DUP KEY = | STATISTICS NORECOMPUTE = | STATISTICS INCREMENTAL = | DROP EXISTING = | ONLINE = | RESUMABLE = | MAX DURATION = [MINUTES] | ALLOW ROW LOCKS = | ALLOW PAGE LOCKS = | OPTIMIZE FOR SEQUENTIAL KEY = | MAXDOP = max degree of parallelism | DATA COMPRESSION = [ ON PARTITIONS ( [ , . n ] ) ] | XML COMPRESSION = [ ON PARTITIONS ( [ , . n ] ) ] > ::= [ AND ] [ . n ] ::= | ::= column name IN (constant . n) ::= column name constant ::= | != | > | >= | !> | ) > ::= TO.
Índice relacional compatível com versões anteriores.
A estrutura de sintaxe de índice relacional compatível com versões anteriores será removida em uma versão futura do SQL Server. Evite usar essa estrutura de sintaxe em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que a utilizam atualmente. Use a estrutura de sintaxe especificada em .
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index name ON ( column name [ ASC | DESC ] [ . n ] ) [ WITH [ . n ] ] [ ON ] ::= ::=
Sintaxe do Azure Synapse Analytics e do Parallel Data Warehouse.
CREATE CLUSTERED COLUMNSTORE INDEX index name ON [ database name . [ schema ] . | schema . ] table name [ORDER (column[. n])] [WITH ( DROP EXISTING = )] [;] CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index name ON [ database name . [ schema ] . | schema . ] table name ( [ . n ] ) WITH ( DROP EXISTING = ) [;]
Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.
Argumentos.
UNIQUE.
Cria um índice exclusivo em uma tabela ou exibição. Um índice exclusivo é aquele no qual duas linhas não podem ter o mesmo valor de chave de índice. Um índice clusterizado em uma exibição deve ser exclusivo.
O Mecanismo de Banco de Dados não permite a criação de um índice exclusivo em colunas que já contêm valores duplicados, independentemente de IGNORE DUP KEY estar definido ou não como ON. Se isso for tentado, o Mecanismo de Banco de Dados exibirá uma mensagem de erro. Valores duplicados devem ser removidos para que um índice exclusivo possa ser criado em uma ou mais colunas. As colunas usadas em um índice exclusivo devem ser definidas como NOT NULL, pois vários valores nulos serão considerados duplicatas quando um índice exclusivo for criado.
CLUSTERED.
Cria um índice no qual a ordem lógica dos valores de chave determina a ordem física das linhas correspondentes em uma tabela. O nível inferior, ou folha, do índice clusterizado contém as linhas de dados reais da tabela. Uma tabela ou exibição pode ter um índice clusterizado por vez.
Uma exibição com um índice clusterizado exclusivo é chamada de exibição indexada. Criar um índice clusterizado exclusivo em uma exibição materializa fisicamente a exibição. Um índice clusterizado exclusivo deve ser criado em uma exibição para que qualquer outro índice possa ser definido na mesma exibição. Para obter mais informações, veja Criar exibições indexadas.
Crie o índice clusterizado antes de criar quaisquer índices não clusterizados. Os índices não clusterizados existentes nas tabelas serão recriados quando um índice clusterizado for criado.
Se CLUSTERED não for especificado, um índice não clusterizado será criado.
Como o nível 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 em que 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, criar um novo índice clusterizado pode habilitar índices previamente desabilitados. Para obter mais informações, veja Habilitar índices e restrições e Desabilitar índices e restrições.
NONCLUSTERED.
Cria um índice que especifica a ordem lógica de uma tabela. Com um índice não clusterizado, a ordem física das linhas de dados é independente de sua ordem indexada.
Cada tabela pode ter até 999 índices não clusterizados, independentemente de como eles são criados: seja implicitamente com as restrições PRIMARY KEY e UNIQUE ou explicitamente com CREATE INDEX .
Para exibições indexadas, os índices não clusterizados podem ser criados somente em uma exibição que tenha um índice clusterizado exclusivo já definido.
Se não for especificado de outra forma, o tipo de índice padrão será não clusterizado.
index name.
O nome do índice. Os nomes de índice devem ser exclusivos em uma tabela ou exibição, mas não precisam ser exclusivos no banco de dados. Os nomes de índice precisam seguir as regras para identificadores.
column.
É a coluna, ou colunas, em que o índice se baseia. Especifique dois ou mais nomes de coluna para criar um índice composto com os valores combinados das colunas especificadas. Liste as colunas que serão incluídas no índice composto, em ordem de prioridade de classificação, entre parênteses depois de table or view name .
Até 32 colunas podem ser combinadas em uma única chave de índice composto. Todas as colunas de uma chave de índice composto devem estar na mesma tabela ou exibição. O tamanho máximo permitido de valores de índice combinados é de 900 bytes para um índice clusterizado ou de 1.700 para um índice não clusterizado. Os limites são 16 colunas e 900 bytespara versões anteriores a Banco de Dados SQL e a SQL Server 2022 (13.x).
Colunas que são dos tipos de dados LOB (Objeto Grande) ntext , text , varchar(max) , nvarchar(max) , varbinary(max) , xml , ou image não podem ser especificadas como colunas chave para um índice. Além disso, uma definição de exibição não pode incluir colunas ntext , text ou image , mesmo que elas não sejam referenciadas na instrução CREATE INDEX .
É possível criar índices em colunas do tipo CLR definido pelo usuário se o tipo der suporte à ordenação binária. Também é possível criar índices em colunas computadas definidas como invocações de método de uma coluna de tipo definido pelo usuário, desde que os métodos sejam marcados como determinísticos e não executem operações de acesso aos dados. Para obter mais informações sobre como indexar colunas CLR de tipo definido pelo usuário, veja Tipos CLR definidos pelo usuário.
[ ASC | DESC ]
Determina a direção de classificação crescente ou decrescente da coluna de índice específica. O padrão é ASC .
INCLUDE ( column [ . n ] )
Especifica as colunas não chave a serem adicionadas ao nível folha do índice não clusterizado. O índice não clusterizado pode ser exclusivo ou não exclusivo.
Os nomes de coluna não podem ser repetidos na lista INCLUDE e não podem ser usados simultaneamente como colunas de chave e não chave. Índices não clusterizados sempre conterão as colunas de índice clusterizado se um índice clusterizado for definido na tabela. Para obter mais informações, consulte Create Indexes with Included Columns.
São permitidos todos os tipos de dados, exceto text , ntext e image . Começando com SQL Server 2012 (11.x) e Banco de Dados SQL do Azure, se qualquer uma das colunas não chave especificadas for varchar(max) , nvarchar(max) ou varbinary(max), o índice poderá ser criado ou recriado usando a opção ONLINE.
As colunas computadas que são determinísticas e precisas ou imprecisas podem ser colunas incluídas. Colunas computadas derivadas dos tipos de dados image , ntext , text , varchar(max) , nvarchar(max) , varbinary(max) e xml podem ser incluídas em colunas não chave, desde que os tipos de dados da coluna computada sejam permitidos como uma coluna incluída. Para obter mais informações, consulte Indexes on Computed Columns.
Para obter informações sobre como criar um índice XML, veja CREATE XML INDEX.
WHERE.
Cria um índice filtrado especificando quais linhas serão incluídas nele. O índice filtrado deve ser um índice não clusterizado em uma tabela. Cria estatísticas filtradas para as linhas de dados no índice filtrado.
O predicado de filtro usa a lógica de comparação simples e não pode fazer referência a uma coluna computada, a uma coluna UDT, a uma coluna de tipo de dados espacial ou a uma coluna de tipo de dados hierarchyID. Comparações que usam literais NULL não são permitidas com os operadores de comparação. Use os operadores IS NULL e IS NOT NULL , nesse caso.
Estes são alguns exemplos de predicados de filtro da tabela Production.BillOfMaterials :
WHERE StartDate > '20000101' AND EndDate.
Índices filtrados não se aplicam a índices XML e índices de texto completo. Para índices UNIQUE, somente as linhas selecionadas devem ter valores de índice exclusivo. Índices filtrados não permitem a opção IGNORE DUP KEY .
ON partition scheme name ( column name )
Especifica o esquema de partição que define os grupos de arquivos nos quais as partições de um índice particionado serão mapeadas. O esquema de partição deve existir no banco de dados com a execução de CREATE PARTITION SCHEME ou ALTER PARTITION SCHEME. column name especifica a coluna com relação à qual um índice particionado será particionado. Essa coluna precisa corresponder ao tipo de dados, ao comprimento e à precisão do argumento da função de partição que partition scheme name está usando. column name não é restrito às colunas na definição de índice. Qualquer coluna da tabela base pode ser especificada, exceto que, ao particionar um índice UNIQUE, column name deve ser escolhido entre aqueles usados como chave exclusiva. Essa restrição permite ao Mecanismo de Banco de Dados verificar a exclusividade de valores de chave em uma única partição apenas.
Ao particionar um índice clusterizado não exclusivo, por padrão, o Mecanismo de Banco de Dados adiciona a coluna de particionamento à lista de chaves de índices clusterizados, se ela já não estiver especificada. Ao particionar um índice não clusterizado e não exclusivo, o Mecanismo de Banco de Dados adiciona a coluna de particionamento como uma coluna não chave (incluída) do índice, se ela já não estiver especificada.
Se partition scheme name ou filegroup não for especificado e a tabela estiver particionada, o índice será colocado no mesmo esquema de partição, usando a mesma coluna de particionamento que a tabela subjacente.
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, consulte Tabelas e índices particionados.
ON filegroup name.
Cria o índice especificado no grupo de arquivos especificado. Se nenhum local for especificado e a tabela ou exibição não for particionada, o índice usará o mesmo grupo de arquivos que a tabela ou exibição subjacente. O grupo de arquivos já deve existir.
ON "padrão"
Cria o índice especificado no mesmo esquema de partição ou grupo de arquivos que a tabela ou a exibição.
Nesse contexto, default não é uma palavra-chave. É um identificador para o grupo de arquivos padrão e precisa ser delimitado, como em ON "default" ou ON [default] . Se "padrão" for especificado, a opção QUOTED IDENTIFIER deverá ser definida como ON para a sessão atual. Essa é a configuração padrão. Para saber mais, confira SET QUOTED IDENTIFIER.
"default" não indica o grupo de arquivos padrão de banco de dados no contexto de CREATE INDEX . Isso é diferente de CREATE TABLE , em que "default" localiza a tabela no grupo de arquivos de banco de dados padrão.
[ FILESTREAM ON filestream filegroup name | partition scheme name | "NULL" > ]
Especifica a colocação de dados FILESTREAM para a tabela quando um índice clusterizado é criado. A cláusula FILESTREAM ON permite mover os dados FILESTREAM para outro grupo de arquivos ou esquema de partição FILESTREAM.
filestream filegroup name é o nome de um grupo de arquivos FILESTREAM. O grupo de arquivos deve ter um arquivo definido para o grupo de arquivos usando uma instrução CREATE DATABASE ou ALTER DATABASE; caso contrário, será gerado um erro.
Se a tabela for particionada, a cláusula FILESTREAM ON precisará ser incluída e especificar um esquema de partição de grupos de arquivos FILESTREAM que use a mesma função de partição e as mesmas colunas de partição do esquema de partição da tabela. Caso contrário, será gerado um erro.
Se a tabela não estiver particionada, a coluna FILESTREAM não poderá ser particionada. Os dados FILESTREAM da tabela precisam ser armazenados em um único grupo de arquivos que é especificado na cláusula FILESTREAM ON .
FILESTREAM ON NULL poderá ser especificado em uma instrução CREATE INDEX se um índice clusterizado estiver sendo criado e a tabela não contiver uma coluna FILESTREAM.
Para obter mais informações, veja FILESTREAM (SQL Server).
O objeto totalmente qualificado ou não totalmente qualificado a ser indexado.
database name.
O nome do banco de dados.
schema name.
O nome do esquema ao qual a tabela ou exibição pertence.
table or view name.
O nome da tabela ou exibição a ser indexada.
A exibição deve ser definida com SCHEMABINDING para criar um índice nela. Um índice clusterizado exclusivo deve ser criado em uma exibição antes que qualquer índice não clusterizado seja criado. Para obter mais informações sobre exibições indexadas, consulte a seção Comentários.
A partir do SQL Server 2022 (13.x), o objeto pode ser uma tabela armazenada com um índice columnstore clusterizado.
O Banco de Dados SQL do Azure dá suporte ao formato de nome de três partes database name .[ schema name ]. object name quando database name é o banco de dados atual ou o database name é tempdb e o object name começa com #.
Especifica as opções a serem usadas ao criar o índice.
PAD INDEX =
Especifica o preenchimento do índice. O padrão é OFF .
ATIVADO O percentual de espaço livre especificado por fillfactor é aplicado às páginas de nível intermediário do índice.
OFF ou fillfactor não está especificado As páginas de nível intermediário são preenchidas até próximo de sua capacidade, deixando espaço suficiente para pelo menos uma linha do tamanho máximo que o índice pode ter, considerando o conjunto de chaves em páginas intermediárias.
A opção PAD INDEX só é útil quando FILLFACTOR é especificado, porque PAD INDEX usa o percentual especificado por FILLFACTOR. Se a porcentagem especificada para FILLFACTOR não for grande o suficiente para permitir uma linha, o Mecanismo de Banco de Dados substituirá a porcentagem internamente para permitir o valor mínimo. O número de linhas em uma página de índice intermediária nunca é menor do que dois, independentemente de quão baixo seja o valor de fillfactor .
Na sintaxe compatível com versões anteriores, WITH PAD INDEX é equivalente a WITH PAD INDEX = ON .
FILLFACTOR = fillfactor.
Especifica uma porcentagem que indica quanto o Mecanismo de Banco de Dados deve preencher o nível folha de cada página de índice durante a criação ou recriação do índice. O valor de fillfactor deve ser um valor inteiro de 1 a 100. Os valores de fator de preenchimento 0 e 100 são iguais em todos os aspectos. Se fillfactor for 100, o Mecanismo de Banco de Dados criará índices com páginas de folha preenchidas até a capacidade total.
A configuração FILLFACTOR se aplica somente quando o índice é criado ou recriado. O Mecanismo de Banco de Dados não mantém dinamicamente a porcentagem especificada de espaço vazio nas páginas.
Para ver a configuração do fator de preenchimento, use fill factor em sys.indexes .
A criação de um índice clusterizado com FILLFACTOR inferior a 100 afeta a quantidade de espaço de armazenamento ocupado pelos dados, porque o Mecanismo de Banco de Dados redistribui os dados quando cria o índice clusterizado.
SORT IN TEMPDB =
Especifica se os resultados de classificação temporários devem ser armazenados no tempdb . O padrão é OFF exceto para a Hiperescala do Banco de Dados SQL do Azure. Para todas as operações de build de índice em Hiperescala, SORT IN TEMPDB está sempre ativado, independentemente da opção especificada, a menos que a recompilação de índice retomável seja usada.
ATIVADO Os resultados de classificação intermediários usados para criar o índice são armazenados no tempdb . Isso poderá reduzir o tempo necessário para criar um índice se tempdb estiver em um conjunto de discos diferente do banco de dados de usuário. Entretanto, isso aumenta o espaço em disco usado durante a criação do índice.
OFF Os resultados intermediários de classificação são armazenados no mesmo banco de dados que o índice.
Além do espaço necessário no banco de dados de usuário para criar o índice, tempdb deve ter aproximadamente a mesma quantidade de espaço adicional para conter os resultados de classificação intermediários. Para obter mais informações, consulte a Opção SORT IN TEMPDB para índices.
Na sintaxe compatível com versões anteriores, WITH SORT IN TEMPDB é equivalente a WITH SORT IN TEMPDB = ON .
IGNORE DUP KEY =
Especifica a resposta de erro quando uma operação de inserção tenta inserir valores da chave duplicada em um índice exclusivo. A opção IGNORE DUP KEY aplica-se apenas a operações de inserção depois que o índice é criado ou recompilado. A opção não tem nenhum efeito ao executar CREATE INDEX, ALTER INDEX ou UPDATE. O padrão é OFF .
ATIVADO Uma mensagem de aviso será exibida quando valores de chave duplicados forem inseridos em um índice exclusivo. Ocorrerá falha somente nas linhas que violarem a restrição de exclusividade.
OFF Ocorrerá uma mensagem de erro quando os valores de chave duplicados forem inseridos em um índice exclusivo. Toda a operação INSERT será revertida.
IGNORE DUP KEY não pode ser definido como ON para índices criados em uma exibição, índices não exclusivos, índices XML, índices espaciais e índices filtrados.
Para exibir IGNORE DUP KEY , use sys.indexes.
Na sintaxe compatível com versões anteriores, WITH IGNORE DUP KEY é equivalente a WITH IGNORE DUP KEY = ON .
STATISTICS NORECOMPUTE =
Especifica se as estatísticas de distribuição são recomputadas. O padrão é OFF .
ATIVADO As estatísticas desatualizadas não são recalculadas automaticamente.
OFF A atualização automática de estatísticas está habilitada.
Para restaurar a atualização automática de estatísticas, defina STATISTICS NORECOMPUTE como DESATIVADO ou execute UPDATE STATISTICS sem a cláusula NORECOMPUTE .
Se o recálculo automático de estatísticas de distribuição for desabilitado, o otimizador de consulta possivelmente ficará impedido de selecionar 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 =
Aplica-se ao : SQL Server (Começando pelo SQL Server 2014 (12.x)) e Banco de Dados SQL do Azure.
Quando estiver ON , as estatísticas serão criadas conforme as estatísticas de partição. Quando estiver OFF , a árvore de estatísticas será removida e o SQL Server calculará as estatísticas novamente. O padrão é OFF .
Se as estatísticas por partição não tiverem suporte, a opção será ignorada e um aviso será gerado. As estatísticas incrementais não têm suporte para os seguintes tipos de estatísticas:
Estatísticas criadas com os índices que não estejam alinhados por partição com a tabela base. Estatísticas criadas em bancos de dados secundários legíveis AlwaysOn. Estatísticas criadas em bancos de dados somente leitura. Estatísticas criadas em índices filtrados. Estatísticas criadas em exibições. Estatísticas criadas em tabelas internas. Estatísticas criadas com índices espaciais ou índices XML.
DROP EXISTING =
É uma opção para remover e recompilar o índice clusterizado ou não clusterizado existente com as especificações da coluna modificada e manter o mesmo nome para o índice. O padrão é OFF .
ATIVADO Especifica remover e recompilar o índice existente, que deve ter o mesmo nome que o parâmetro index name .
OFF Especifica não remover e recompilar o índice existente. O SQL Server exibirá um erro se o nome do índice especificado já existir.
Com DROP EXISTING , você pode alterar:
Um índice rowstore não clusterizado para um índice rowstore clusterizado.
Com DROP EXISTING , você não pode alterar:
Um índice rowstore clusterizado para um índice rowstore não clusterizado. Um índice columnstore clusterizado para qualquer tipo de índice de rowstore.
Na sintaxe compatível com versões anteriores, WITH DROP EXISTING é equivalente a WITH DROP EXISTING = ON .
ONLINE =
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 .
As operações de índice online não estão disponíveis em todas as edições de Microsoft SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, veja Edições e recursos com suporte no SQL Server 2022.
ATIVADO Bloqueios de tabela de longa duração não são mantidos durante a operação do índice. Durante a fase principal da operação de índice, apenas um bloqueio IS (Tentativa Compartilhada) é mantido na tabela de origem. Ele permite o prosseguimento de consultas ou atualizações feitas na tabela e nos índices subjacentes. No início da operação, um bloqueio Compartilhado (S) é mantido no objeto de origem por um período muito curto. Ao término da operação, por um curto período, um bloqueio S (Compartilhado) será adquirido na origem se um índice não clusterizado estiver sendo criado. Um bloqueio Sch-M (Modificação de Esquema) será adquirido quando um índice clusterizado for criado ou descartado online e quando um índice clusterizado ou não clusterizado estiver sendo recompilado. Não será possível definir ONLINE como ON quando um índice estiver sendo criado em uma tabela temporária local.
A criação de índice online pode definir as opções low priority lock wait ; confira WAIT AT LOW PRIORITY com operações de índice online.
OFF Os bloqueios de tabela são aplicados enquanto durar a operação de índice. Uma operação de índice offline que cria, recria ou cancela um índice clusterizado ou recria ou cancela um índice não clusterizado, adquire um bloqueio de esquema de modificação (Sch-M) na tabela. Isso evita o acesso de todos os usuários à tabela subjacente enquanto durar a operação. Uma operação de índice offline que cria um índice não clusterizado adquire um bloqueio Compartilhado (S) na tabela. Isso impede atualizações na tabela subjacente, mas permite operações de leitura, como instruções SELECT.
Para obter mais informações, consulte Perform Index Operations Online.
Índices, inclusive aqueles em tabelas temporárias globais, podem ser criados online exceto nos seguintes casos:
Índice XML Índice em uma tabela temporária local Índice clusterizado exclusivo inicial em uma exibição Índices clusterizados desabilitados Índices columnstore Índice clusterizado se a tabela subjacente contiver tipos de dados LOB ( image , ntext , text ) e tipos de dados espaciais As colunas varchar(max) e varbinary(max) não podem fazer parte de uma chave de índice. Em SQL Server (começando com o SQL Server 2012 (11.x)) e Banco de Dados SQL do Azure, quando uma tabela contém as colunas varchar(max) ou varbinary(max) , um índice clusterizado contendo outras colunas pode ser criado ou recriado usando a opção ONLINE .
RESUMABLE =
Aplica-se ao : SQL Server (Começando pelo SQL Server 2022 (15.x)) e Banco de Dados SQL do Azure.
Especifica se uma operação de índice online é retomável.
ATIVADO A operação do índice é retomável.
OFF A operação do índice não é retomável.
MAX DURATION = tempo [MINUTOS] usado com RESUMABLE = ON (requer ONLINE = ON )
Aplica-se ao : SQL Server (Começando pelo SQL Server 2022 (15.x)) e Banco de Dados SQL do Azure.
Indica o tempo (um valor inteiro especificado em minutos) pelo qual um uma operação de índice online retomável é executada antes de ser colocada em pausa.
Para obter mais informações sobre operações de índice que podem ser executadas online, consulte Diretrizes para operações de índice online.
Não há suporte para recompilações de índice online retomáveis em índices columnstore.
ALLOW ROW LOCKS =
Especifica se bloqueios de linha são permitidos. O padrão é ON .
ATIVADO Bloqueios de linha são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de linha são usados.
OFF Bloqueios de linha não são usados.
ALLOW PAGE LOCKS =
Especifica se bloqueios de página são permitidos. O padrão é ON .
ATIVADO Bloqueios de página são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de página são usados.
OFF Bloqueios de página não são usados.
OPTIMIZE FOR SEQUENTIAL KEY =
Aplica-se ao : SQL Server (Começando pelo SQL Server 2022 (15.x)) e Banco de Dados SQL do Azure.
Especifica se a contenção de inserção de última página será ou não otimizada. O padrão é OFF . Confira a seção sobre Chaves sequenciais para saber mais.
MAXDOP = max degree of parallelism.
Substitui a opção de configuração max degree of parallelism enquanto durar a operação do índice. Para obter mais informações, veja Configurar a opção max degree of parallelism de configuração de servidor. Use MAXDOP para limitar o número de processadores usados em uma execução de plano paralelo. O máximo é de 64 processadores.
max degree of parallelism pode ser:
1 Suprime a geração de plano paralelo.
>1 Restringe o número máximo de processadores usados em uma operação de índice paralela ao número especificado, ou menos, com base na carga de trabalho atual do sistema.
0 (padrão) Usa o número real de processadores, ou menos, com base na carga de trabalho atual do sistema.
As operações de índice paralelas não estão disponíveis em todas as edições do Microsoft SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, veja Edições e recursos com suporte no SQL Server 2022 e Edições e recursos com suporte no SQL Server 2022.
DATA COMPRESSION.
Especifica a opção de compactação de dados para o índice, número de partição ou intervalo de partições especificado. As opções são as descritas a seguir:
Nenhuma O índice ou as partições especificadas não são compactados.
ROW O índice ou as partições especificadas são compactados com o uso da compactação de linha.
PAGE O índice ou as partições especificadas são compactados com o uso da compactação de página.
Para obter mais informações sobre compactação, consulte Compactação de dados.
XML COMPRESSION.
Aplica-se a : SQL Server 2022 (16.x) e versões posteriores e à versão prévia do Banco de Dados SQL do Azure.
Especifica a opção de compactação XML para o índice especificado que contém uma ou mais colunas de tipo de dados xml . As opções são as descritas a seguir:
ATIVADO O índice ou as partições especificadas são compactados com a compactação XML.
OFF O índice ou as partições especificadas não são compactados.
ON PARTITIONS ( [ . n ] )
Especifica as partições às quais as configurações DATA COMPRESSION e XML COMPRESSION se aplicam. Se o índice não for particionado, o argumento ON PARTITIONS gerará um erro. Se a cláusula ON PARTITIONS não for fornecida, a opção DATA COMPRESSION ou XML COMPRESSION será aplicada a todas as partições de um índice particionado.
XML COMPRESSION só está disponível do SQL Server 2022 (16.x) e da versão prévia do Banco de Dados SQL do Azure em diante.
pode ser especificado das seguintes maneiras:
Forneça o número de uma partição, por exemplo: ON PARTITIONS (2) . Forneça os números de várias partições individuais separados por vírgulas, por exemplo: ON PARTITIONS (1, 5) . Forneça os intervalos e as partições individuais, por exemplo: ON PARTITIONS (2, 4, 6 TO 8) .
pode ser especificado como números de partição separados pela palavra TO, por exemplo: ON PARTITIONS (6 TO 8) .
Para definir tipos diferentes de compactação de dados para partições diferentes, especifique a opção DATA COMPRESSION mais de uma vez, por exemplo:
REBUILD WITH ( DATA COMPRESSION = NONE ON PARTITIONS (1), DATA COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), DATA COMPRESSION = PAGE ON PARTITIONS (3, 5) );
Você também pode especificar a opção XML COMPRESSION mais de uma vez, por exemplo:
REBUILD WITH ( XML COMPRESSION = OFF ON PARTITIONS (1), XML COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8), XML COMPRESSION = OFF ON PARTITIONS (3, 5) );
Comentários.
A instrução CREATE INDEX é otimizada como qualquer outra consulta. Para salvar as operações de E/S, o processador de consultas pode optar por examinar outro índice em vez de executar um exame de tabela. A operação de classificação pode ser eliminada em algumas situações. Em computadores com multiprocessadores, CREATE INDEX pode usar mais processadores para executar operações de exame e classificação associadas à criação do índice, exatamente como fazem outras consultas. Para obter mais informações, consulte Configurar operações de índice paralelo.
A operação CREATE INDEX poderá ser registrada minimamente se o modelo de recuperação de banco de dados for definido como bulk-logged ou simples.
Os índices podem ser criados em uma tabela temporária. Quando a tabela for removida ou a sessão encerrada, os índices serão removidos.
Um índice clusterizado pode ser criado em uma variável de tabela quando uma chave primária é criada. Quando a consulta é concluída ou a sessão é encerrada, o índice é removido.
Os índices dão suporte a propriedades estendidas.
Índices cluster.
Criar um índice clusterizado em uma tabela (heap) ou descartar e recriar um índice clusterizado existente requer workspace adicional disponível no banco de dados, para acomodar a classificação de dados e uma cópia temporária da tabela original ou dos dados do índice clusterizado existente. Para obter mais informações sobre índices clusterizados, confira Criar índices clusterizados e o Guia de arquitetura e design de índices do SQL Server.
Índices não clusterizados.
A partir do SQL Server 2022 (13.x) e no Banco de Dados SQL do Azure, você pode criar um índice não clusterizado em uma tabela armazenada como um índice columnstore clusterizado. Se você primeiro criar um índice não clusterizado em uma tabela armazenada como um índice clusterizado ou heap, o índice persistirá se você depois converter a tabela em um índice columnstore clusterizado. Também não é necessário remover o índice não clusterizado ao recompilar o índice columnstore clusterizado.
Limitações e restrições:
A opção FILESTREAM ON não é válida quando você cria um índice não clusterizado em uma tabela armazenada como um índice columnstore clusterizado.
Índices exclusivos.
Quando existe um índice exclusivo, o Mecanismo de Banco de Dados verifica se há valores duplicados sempre que dados são adicionados por operações de inserção. As operações de inserção que geram valores de chave duplicados são revertidas, e o Mecanismo de Banco de Dados exibe uma mensagem de erro. Isso acontece mesmo que a operação de inserção altere muitas linhas, mas crie apenas uma duplicata. Se for feita uma tentativa de inserir dados para os quais há um índice exclusivo e a cláusula IGNORE DUP KEY for definida como ON, somente as linhas que violarem o índice UNIQUE falharão.
Índices particionados.
Índices particionados são criados e mantidos de uma maneira semelhante às tabelas particionadas, mas, como índices comuns, são tratados como objetos de banco de dados separados. É possível haver um índice particionado em uma tabela não particionada, bem como é possível ter um índice não particionado em uma tabela particionada.
Se você estiver criando um índice em uma tabela particionada e não especificar um grupo de arquivos para colocar o índice, ele será particionado da mesma maneira que a tabela subjacente. Isso ocorre porque, por padrão, os índices são colocados nos mesmos grupos de arquivos que suas tabelas subjacentes e, para uma tabela particionada, no mesmo esquema de partição que usa as mesmas colunas de particionamento. Quando o índice usa o mesmo esquema de partição e coluna de particionamento que a tabela, ele é alinhado à tabela.
É possível criar e reconstruir índices não alinhados em uma tabela com mais de 1.000 partições, mas não há suporte para isso. Fazer isso pode provocar degradação do desempenho ou consumo excessivo de memória durante essas operações. É recomendável usar índices alinhados apenas quando o número de partições for maior que 1.000.
Ao particionar um índice clusterizado não exclusivo, por padrão, o Mecanismo de Banco de Dados adiciona quaisquer colunas de particionamento à lista de chaves de índice clusterizado, se já não estiverem especificadas.
As exibições indexadas podem ser criadas em tabelas particionadas da mesma maneira que os índices em tabelas. Para obter mais informações sobre índices particionados, confira Tabelas e índices particionados e o Guia de arquitetura e design de índices do SQL Server.
No SQL Server, as estatísticas não são criadas por meio do exame de todas as linhas da tabela quando um índice particionado é criado ou reconstruído. Em vez disso, o otimizador de consultas usa o algoritmo de amostragem padrão para gerar estatísticas. Para obter as estatísticas dos índices particionados ao examinar todas as linhas da tabela, use CREATE STATISTICS ou UPDATE STATISTICS com a cláusula FULLSCAN .
Índices filtrados.
Índice filtrado é um índice não clusterizado otimizado, adequado a consultas que selecionam uma pequena porcentagem de linhas de uma tabela. Ele usa um predicado de filtro para indexar uma parte dos dados na tabela. Um índice filtrado bem projetado pode melhorar o desempenho da consulta, além de reduzir custos de armazenamento e de manutenção.
Opções SET necessárias para índices filtrados.
As opções SET na coluna Valor necessário são necessárias sempre que ocorrer alguma das seguintes condições:
Valor OLE DB e ODBC Padrão.
A definição de ANSI WARNINGS como ON definirá ARITHABORT implicitamente como ON quando o nível de compatibilidade do banco de dados estiver definido como 90 ou mais. Se o nível de compatibilidade do banco de dados estiver definido como 80 ou menos, a opção ARITHABORT deverá ser definida explicitamente como ON.
Se as opções SET estiverem incorretas, as seguintes condições poderão ocorrer:
O índice filtrado não é criado. O Mecanismo de Banco de Dados gera um erro e reverte as instruções INSERT, UPDATE, DELETE ou MERGE que alteram os dados no índice. O otimizador de consulta não considera o índice no plano de execução para qualquer instrução Transact-SQL.
Para obter mais informações sobre índices filtrados, confira Criar índices filtrados e o Guia de arquitetura e design de índices do SQL Server.
Índices espaciais.
Para obter informações sobre índices espaciais, veja CREATE SPATIAL INDEX e Visão geral de índices espaciais.
índices XML.
Para informações sobre índices XML, veja CREATE XML INDEX e Índices XML (SQL Server).
Tamanho de chave de índice.
O tamanho máximo para uma chave de índice é de 900 bytes para um índice clusterizado e de 1.700 bytes para um índice não clusterizado. (Antes do Banco de Dados SQL e SQL Server 2022 (13.x) o limite sempre era de 900 bytes.) Os índices nas colunas varchar que excederem o limite de bytes poderão ser criados se os dados existentes nas colunas não excederem o limite no momento da criação do índice; entretanto, as ações subsequentes de inserção ou atualização nas colunas que fazem com que o tamanho total seja maior que o limite de bytes falharão. A chave de um índice clusterizado não pode conter colunas varchar que tenham dados existentes na unidade de alocação ROW OVERFLOW DATA. Se um índice clusterizado for criado em uma coluna varchar e os dados existentes estiverem na unidade de alocação IN ROW DATA, as ações subsequentes de inserção ou atualização na coluna que faria o push dos dados da linha apresentarão falha.
Índices não clusterizados podem incluir colunas não chave no nível folha do índice. Essas colunas não são consideradas pelo Mecanismo de Banco de Dados ao calcular o tamanho da chave de índice. Para obter mais informações, confira Criar índices com colunas incluídas e o Guia de arquitetura e design de índices do SQL Server.
Quando as tabelas são particionadas, se as colunas de chave de particionamento ainda não estiverem presentes em um índice clusterizado não exclusivo, elas poderão ser adicionadas ao índice pelo Mecanismo de Banco de Dados. O tamanho combinado das colunas indexadas (sem contar as colunas incluídas) mais qualquer coluna de particionamento adicionada não pode exceder 1.800 bytes em um índice clusterizado não exclusivo.
Colunas computadas.
Os índices podem ser criados em colunas computadas. Além disso, as colunas computadas podem ter a propriedade PERSISTED. Isso significa que o Mecanismo de Banco de Dados armazena os valores computados na tabela e os atualiza quando as outras colunas das quais a coluna computada depende são atualizadas. O Mecanismo de Banco de Dados usa esses valores persistentes ao criar um índice na coluna e quando o índice é referenciado em uma consulta.
Uma coluna computada deve ser determinística e precisa para ser indexada. Entretanto, o uso da propriedade PERSISTED expande o tipo das colunas computadas indexáveis para incluir:
Colunas computadas baseadas nas funções Transact-SQL e CLR, e métodos de tipo CLR definidos pelo usuário que são marcados como determinísticos pelo usuário. Colunas computadas baseadas em expressões que são determinísticas, conforme definidas pelo Mecanismo de Banco de Dados, mas imprecisas.
As colunas computadas persistentes requerem que as seguintes opções SET sejam definidas como mostrado na seção anterior Opções SET necessárias para índices filtrados.
A restrição UNIQUE ou PRIMARY KEY pode conter uma coluna computada contanto que satisfaça todas as condições para indexação. Especificamente, a coluna computada deve ser determinística e precisa ou determinística e persistente. Para obter mais informações sobre determinismo de funções, veja Funções determinísticas e não determinísticas.
Colunas computadas derivadas dos tipos de dados image , ntext , text , varchar(max) , nvarchar(max) , varbinary(max) e xml podem ser indexadas como coluna chave ou não chave incluída, desde que o tipo de dados da coluna computada seja permitido como uma coluna chave ou não chave de índice. Por exemplo, não é possível criar um índice XML primário em uma coluna xml computada. Se o tamanho da chave de índice exceder 900 bytes, uma mensagem de aviso será exibida.
Criar um índice em uma coluna computada pode causar a falha de uma operação de inserção ou atualização que tenha funcionado anteriormente. Essa falha pode ocorrer quando a coluna computada resultar em erro aritmético. Por exemplo, na tabela a seguir, embora a coluna computada c resulte em um erro aritmético, a instrução INSERT funciona.
CREATE TABLE t1 (a INT, b INT, c AS a/b); INSERT INTO t1 VALUES (1, 0);
Se, em vez disso, depois de criar a tabela, você criar um índice em uma coluna computada c , a mesma instrução INSERT agora falhará.
CREATE TABLE t1 (a INT, b INT, c AS a/b); CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c); INSERT INTO t1 VALUES (1, 0);
Para obter mais informações, consulte Indexes on Computed Columns.
Colunas incluídas em índices.
As colunas não chave, chamadas de colunas incluídas, podem ser adicionadas ao nível folha de um índice não clusterizado para melhorar o desempenho da consulta ao abrangê-la. Isso quer dizer que todas as colunas referenciadas na consulta são incluídas no índice como colunas de chave ou não chave. Isso permite que o otimizador de consulta localize todas as informações necessárias de um exame de índice; os dados de tabela ou de índice clusterizado não são acessados. Para obter mais informações, confira Criar índices com colunas incluídas e o Guia de arquitetura e design de índices do SQL Server.
Especificar opções de índice.
O SQL Server 2005 (9.x) introduziu novas opções de índice e também modifica o modo como as opções são especificadas. Na sintaxe compatível com versões anteriores, WITH option name é equivalente a WITH (option name = ON) . Ao definir as opções de índice, aplicam-se as seguintes regras:
Novas opções de índice só podem ser especificadas usando WITH ( = ) . As opções não podem ser especificadas com o uso de sintaxe compatível com versões anteriores e nova sintaxe na mesma instrução. Por exemplo, especificar WITH (DROP EXISTING, ONLINE = ON) faz com que a instrução falhe. Ao criar um índice XML, as opções devem ser especificadas usando WITH ( = ) .
cláusula DROP EXISTING.
É possível usar a cláusula DROP EXISTING para recompilar o índice, adicionar ou descartar colunas, modificar opções, modificar a ordem de classificação de colunas ou alterar o esquema de partição ou o grupo de arquivos.
Se o índice impuser uma restrição PRIMARY KEY ou UNIQUE e a definição de índice não for alterada de alguma maneira, o índice será descartado e recriado preservando a restrição existente. Entretanto, se a definição de índice for alterada, a instrução falhará. Para alterar a definição de uma restrição PRIMARY KEY ou UNIQUE, descarte a restrição e adicione uma restrição com a nova definição.
DROP EXISTING melhora o desempenho quando você recria um índice clusterizado, com o mesmo conjunto de chaves ou um conjunto diferente, em uma tabela que também tenha índices não clusterizados. DROP EXISTING substitui a execução de uma instrução DROP INDEX no índice clusterizado antigo, seguida da execução de uma instrução CREATE INDEX para o novo índice clusterizado. Os índices não clusterizados são recriados uma vez e, depois disso, somente se a definição de índice for alterada. A cláusula DROP EXISTING não recompila os índices não clusterizados quando a definição de índice tem o mesmo nome de índice, chave e colunas de partição, atributo de exclusividade e ordem de classificação que o índice original.
Os índices não clusterizados podem ser recriados ou não, mas sempre permanecem em seus grupos de arquivos ou esquemas de partição originais e usam as funções de partição originais. Se um índice clusterizado for recriado para um grupo de arquivos ou esquema de partição diferente, os índices não clusterizados não serão movidos para coincidir com o novo local do índice clusterizado. Portanto, mesmo os índices não clusterizados previamente alinhados ao índice clusterizado, podem não estar mais alinhados a ele. Para obter mais informações sobre o alinhamento de índices particionados, consulte Índices e tabelas particionadas.
A cláusula DROP EXISTING não classificará os dados novamente se as mesmas colunas de chave de índice forem usadas na mesma ordem e com a mesma ordem crescente ou decrescente, a menos que a instrução de índice especifique um índice não clusterizado e a opção ONLINE seja definida como OFF. Se o índice clusterizado for desabilitado, a operação CREATE INDEX WITH DROP EXISTING deverá ser executada com ONLINE definido como OFF. Se um índice não clusterizado for desabilitado e não for associado a um índice clusterizado desabilitado, a operação CREATE INDEX WITH DROP EXISTING poderá ser executada com ONLINE definido como OFF ou ON.
Quando índices com 128 extensões ou mais são descartados ou recriados, o Mecanismo de Banco de Dados adia as desalocações de página atuais e seus bloqueios associados até depois da confirmação da transação.
opção ONLINE.
As diretrizes a seguir são aplicáveis ao executar operações de índice online:
A tabela subjacente não pode ser alterada, truncada ou descartada quando uma operação de índice online estiver em andamento. É necessário espaço em disco temporário adicional durante a operação de índice. As operações online podem ser executadas em índices particionados e índices que contenham colunas computadas ou colunas incluídas persistentes. A opção de argumento low priority lock wait permite que você decida como a operação de índice pode continuar quando bloqueada no bloqueio Sch-M.
Para obter mais informações, consulte Perform Index Operations Online.
Recursos.
Os recursos a seguir são necessários para a operação de criação de índice online retomável:
Espaço adicional necessário para manter o índice que está sendo criado, incluindo o tempo em que o índice está em pausa Taxa de transferência de log adicional durante a fase de classificação. O uso de espaço de log geral para índice retomável é menor em comparação com a criação de índice online regular e permite o truncamento de log durante a operação. Um estado DDL que impede qualquer modificação de DDL A limpeza de fantasma está bloqueada no índice no build pela duração da operação, em pausa e enquanto a operação está em execução.
Limitações funcionais atuais.
A seguinte funcionalidade está desabilitada para operações de criação de índice retomáveis:
Depois que uma operação de criação de índice online retomável é pausada, o valor inicial de MAXDOP não pode ser alterado Criar um índice que contém: Coluna(s) calculada(s) ou TIMESTAMP como colunas de chave Coluna LOB como coluna incluída para criação de índice retomável Índice filtrado.
Operações de índice retomáveis.
Aplica-se ao : SQL Server (Começando pelo SQL Server 2022 (15.x)) e Banco de Dados SQL do Azure.
As diretrizes a seguir se aplicam a operações de índice retomável:
A criação de índice online é especificada como retomável usando a opção RESUMABLE = ON . A opção RESUMABLE não persiste nos metadados para um determinado índice e se aplica somente à duração de uma instrução DDL atual. Portanto, a cláusula RESUMABLE = ON deve ser especificada explicitamente para habilitar a capacidade de retomada. Só há suporte para a opção MAX DURATION na opção RESUMABLE = ON . MAX DURATION para a opção RESUMABLE especifica o intervalo para um índice que está sendo compilado. Depois que esse tempo é consumido, a recompilação de índice é colocada em pausa ou conclui sua execução. O usuário decide quando uma recompilação de um índice em pausa pode ser retomada. O tempo em minutos para MAX DURATION deve ser superior a 0 minutos e inferior ou igual a uma semana (7 * 24 * 60 = 10080 minutos). Ter uma longa pausa para uma operação de índice pode afetar o desempenho de DML em uma tabela específica, bem como a capacidade de disco de banco de dados, já que tanto o original quanto o recém-criado exigem espaço em disco e precisam ser atualizados durante as operações DML. Se a opção MAX DURATION for omitida, a operação de índice continuará até sua conclusão ou até que ocorra uma falha. Para pausar imediatamente a operação de índice, você pode interromper (Ctrl-C) o comando em andamento, executar o comando ALTER INDEX PAUSE ou executar o comando KILL . Depois que o comando for colocado em pausa, ele poderá ser retomado usando o comando ALTER INDEX. Executar novamente a instrução CREATE INDEX original para o índice retomável retoma automaticamente uma operação de criação de índice em pausa. Não há suporte para a opção SORT IN TEMPDB = ON no índice retomável. O comando DDL com RESUMABLE = ON não pode ser executado em uma transação explícita (não pode fazer parte do bloco TRAN . COMMIT ). Para retomar/anular uma recompilação/criação de índice, use a sintaxe ALTER INDEX T-SQL.
O comando DDL é executado até ser concluído, pausar ou falhar. Caso o comando pause, será emitido um erro indicando que a operação foi colocada em pausa e que a criação de índice não foi concluída. Para obter mais informações sobre o status atual do índice, veja sys.index resumable operations. Como antes, no caso de uma falha, um erro será emitido também.
Para indicar que a criação de um índice é executada como uma operação retomável e para verificar seu estado de execução atual, confira sys.index resumable operations.
WAIT AT LOW PRIORITY com operações de índice online.
Aplica-se a : essa sintaxe de CREATE INDEX atualmente só se aplica ao SQL Server 2022 (16.x), ao Banco de Dados SQL do Azure e à Instância Gerenciada de SQL do Azure. Para ALTER INDEX , essa sintaxe se aplica a SQL Server (começando com SQL Server 2014 (12.x)) e Banco de Dados SQL do Azure. Para saber mais, confira ALTER INDEX.
A sintaxe low priority lock wait permite especificar o comportamento WAIT AT LOW PRIORITY . WAIT AT LOW PRIORITY pode ser usado apenas com ONLINE=ON .
A opção WAIT AT LOW PRIORITY permite que os DBAs gerenciem o bloqueio Sch-S e os bloqueios Sch-M necessários à criação de índice online e que eles selecionem uma das três opções. Nos três casos, se, durante o tempo de espera MAX DURATION = n [minutes] , não houver nenhuma atividade de bloqueio, a recompilação de índice online será executada imediatamente sem aguardar e a instrução DDL será concluída.
WAIT AT LOW PRIORITY indica que a operação de criação do índice online aguardará bloqueios de baixa prioridade, permitindo que outras operações continuem enquanto a operação de build de índice online estiver aguardando. Omitir a opção WAIT AT LOW PRIORITY é equivalente a WAIT AT LOW PRIORITY (MAX DURATION = 0 minutes, ABORT AFTER WAIT = NONE) .
MAX DURATION = time [ MINUTES ]
O tempo de espera (um valor inteiro especificado em minutos) que a opção ou os bloqueios de criação de índice online deverão aguardar com baixa prioridade ao executar o comando DDL. Se a operação estiver bloqueada pelo tempo MAX DURATION , a ação ABORT AFTER WAIT especificada será executada. O tempo MAX DURATION está sempre em minutos e a palavra MINUTES pode ser omitida.
ABORT AFTER WAIT = [ NONE | SELF | BLOCKERS > ]
NONE Continue aguardando o bloqueio com prioridade normal (regular).
SELF Saia da operação DDL de criação de índice online em execução no momento sem realizar a ação. A opção SELF não pode ser usada com um MAX DURATION de 0.
BLOCKERS Elimina todas as transações de usuário que bloqueiam a operação DDL de recompilação de índice online para que a operação possa continuar. A opção BLOCKERS requer que o logon tenha a permissão ALTER ANY CONNECTION .
Opções de bloqueios de linha e de página.
Quando a opção for ALLOW ROW LOCKS = ON e ALLOW PAGE LOCK = ON , os bloqueios em nível de linha, página e tabela serão permitidos ao acessar o índice. O Mecanismo de Banco de Dados escolhe o bloqueio apropriado e pode escalar o bloqueio de uma linha ou página para um bloqueio de tabela.
Quando a opção for ALLOW ROW LOCKS = OFF e ALLOW PAGE LOCK = OFF , somente o bloqueio em nível de tabela será permitido ao acessar o índice.
Chaves sequenciais.
Aplica-se ao : SQL Server (Começando pelo SQL Server 2022 (15.x)) e Banco de Dados SQL do Azure.
A contenção de inserção de última página é um problema de desempenho comum que ocorre quando um grande número de threads simultâneos tenta inserir linhas em um índice com uma chave sequencial. Um índice é considerado sequencial quando a coluna de chave à esquerda contém valores que sempre aumentam (ou diminuem), como uma coluna de identidade ou uma data que assume como padrão a data/hora atual. Como as chaves que estão sendo inseridas são sequenciais, todas as novas linhas serão inseridas no final da estrutura do índice – em outras palavras, na mesma página. Isso leva à contenção da página na memória que pode ser observada como vários threads aguardando PAGELATCH EX para a página em questão.
Habilitar a opção de índice OPTIMIZE FOR SEQUENTIAL KEY permite uma otimização no mecanismo de banco de dados que ajuda a aprimorar a taxa de transferência para inserções de alta simultaneidade em um índice. Ele se destina aos índices que têm uma chave sequencial e, portanto, estão sujeitos à contenção de inserção de última página, mas também pode ajudar com os índices que têm pontos de acesso em outras áreas da estrutura de índice de árvore B.
A documentação do SQL Server usa o termo árvore B geralmente em referência a índices. Em índices de armazenamento de linha, o SQL Server implementa uma árvore B+. Isso não se aplica a índices columnstore ou armazenamentos de dados na memória. Saiba mais no Guia de design e arquitetura de índice do SQL Server.
Exibir informações de índice.
Para retornar informações sobre índices, é possível usar exibições do catálogo, funções do sistema e procedimentos armazenados do sistema.
Compactação de dados.
Compactação de dados é descrita no tópico Compactação de dados. A seguir estão os pontos-chave a serem considerados:
A compactação pode permitir que mais linhas sejam armazenadas em uma página, mas não altera o tamanho máximo da linha. Páginas não folha de um índice não são compactadas por página, mas podem ser compactadas por linha. Cada índice não clusterizado tem uma configuração de compactação individual e não herda a configuração de compactação da tabela subjacente. Quando um índice clusterizado é criado em um heap, ele herda o estado de compactação do heap, a menos que um estado de compactação alternativo seja especificado.
As restrições a seguir se aplicam a índices particionados:
Não será possível alterar a configuração de compactação de uma única partição se a tabela tiver índices não alinhados. A sintaxe ALTER INDEX . REBUILD PARTITION . recompila a partição especificada do índice. A sintaxe ALTER INDEX . REBUILD WITH . recompila todas as partições do índice.
Para avaliar como a alteração do estado de compactação afetará uma tabela, um índice ou uma partição, use o procedimento armazenado sp estimate data compression savings .
Compactação XML.
Aplica-se a : SQL Server 2022 (16.x) e versões posteriores e à versão prévia do Banco de Dados SQL do Azure.
Muitas das mesmas considerações de compactação de dados se aplicam à compactação XML. Considere também o seguinte:
Quando uma lista de partições é especificada, a compactação XML pode ser habilitada em partições individuais. Se a lista de partições não for especificada, todas as partições serão definidas para usar compactação a XML. Quando uma tabela ou um índice é criado, a compactação de dados XML é desabilitada, a menos que haja outra especificação. Quando uma tabela é modificada a compactação existente é preservada, a menos que especificada de outra maneira. Se for especificada uma lista de partições ou uma partição fora do intervalo, um erro será gerado. Quando um índice clusterizado é criado em um heap, ele herda o estado de compactação XML do heap, a menos que um estado de compactação alternativo seja especificado. A alteração da configuração de compactação XML de um heap exige que todos os índices não clusterizados na tabela sejam recriados a fim de conterem ponteiros para os novos locais de linha no heap. Você pode habilitar ou desabilitar a compactação online ou offline. A habilitação da compactação em um heap tem thread único para uma operação online. Para determinar o estado de compactação XML das partições em uma tabela particionada, consulte a coluna xml compression da exibição de catálogo sys.partitions .
Permissões.
Requer permissão ALTER na tabela, exibição ou associação na função de banco de dados fixa db ddladmin .
Limitações e restrições.
No Azure Synapse Analytics e no PDW (Analytics Platform System), não é possível criar:
Um índice rowstore clusterizado ou não clusterizado em uma tabela de data warehouse quando já existe um índice columnstore. Esse comportamento é diferente do SMP SQL Server, o que permite aos índices rowstore e columnstore coexistir na mesma tabela. Você não pode criar um índice em uma exibição.
Metadados.
Para exibir informações sobre índices existentes, você pode consultar a exibição do catálogo sys.indexes.
Notas de versão.
O Banco de Dados SQL não é compatível com as opções grupo de arquivos e fluxo de arquivos.
Exemplos: Todas as versões. Usa o banco de dados AdventureWorks.
a. Criar um índice rowstore não clusterizado simples.
Os exemplos a seguir criam um índice não clusterizado na coluna VendorID da tabela Purchasing.ProductVendor .
CREATE INDEX IX VendorID ON ProductVendor (VendorID); CREATE INDEX IX VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC); CREATE INDEX IX VendorID ON Purchasing..ProductVendor (VendorID);
B. Criar um índice composto rowstore não clusterizado simples.
O exemplo a seguir cria um índice composto não clusterizado nas colunas SalesQuota e SalesYTD da tabela Sales.SalesPerson .
CREATE NONCLUSTERED INDEX IX SalesPerson SalesQuota SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);
C. Criar um índice em uma tabela em outro banco de dados.
O exemplo a seguir cria um índice clusterizado na coluna VendorID da tabela ProductVendor no banco de dados Purchasing .
CREATE CLUSTERED INDEX IX ProductVendor VendorID ON Purchasing..ProductVendor (VendorID);
D. Adicionar uma coluna a um índice.
O exemplo a seguir cria o índice IX FF com duas colunas da tabela dbo.FactFinance. A próxima instrução recompila o índice com mais uma coluna e mantém o nome existente.
CREATE INDEX IX FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC); -- Rebuild and add the OrganizationKey CREATE INDEX IX FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC) WITH (DROP EXISTING = ON);
Exemplos: SQL Server, Banco de Dados SQL do Azure.
E. Criando um índice não clusterizado exclusivo.
O exemplo a seguir cria um índice não clusterizado exclusivo na coluna Name da tabela Production.UnitMeasure no banco de dados AdventureWorks2012 . O índice imporá a exclusividade dos dados inseridos na coluna Name .
CREATE UNIQUE INDEX AK UnitMeasure Name ON Production.UnitMeasure(Name);
A consulta a seguir testa a restrição de exclusividade tentando inserir uma linha com o mesmo valor que o de uma linha existente.
-- Verify the existing value. SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces'; GO INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate) VALUES ('OC', 'Ounces', GETDATE());
A mensagem de erro resultante é:
Server: Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK UnitMeasure Name'. The statement has been terminated.
F. Use a opção IGNORE DUP KEY.
O exemplo a seguir demonstra o efeito da opção IGNORE DUP KEY inserindo várias linhas em uma tabela temporária primeiro com a opção definida como ON e, em seguida, com a opção definida como OFF . Uma única linha é inserida na tabela #Test , causando intencionalmente um valor duplicado quando a segunda instrução de várias linhas INSERT for executada. Uma contagem de linhas na tabela retorna o número de linhas inseridas.
CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME); GO CREATE UNIQUE INDEX AK Index ON #Test (C2) WITH (IGNORE DUP KEY = ON); GO INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE()); INSERT INTO #Test SELECT * FROM Production.UnitMeasure; GO SELECT COUNT(*) AS [Number of rows] FROM #Test; GO DROP TABLE #Test; GO.
A seguir são apresentados os resultados da segunda instrução INSERT .
Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored. Number of rows -------------- 38.
Observe que as linhas inseridas da tabela Production.UnitMeasure que não violaram a restrição de exclusividade foram inseridas com êxito. Um aviso foi emitido e a linha duplicada ignorada, mas a transação inteira não foi revertida.
As mesmas instruções são executadas novamente, mas com IGNORE DUP KEY definido como OFF .
CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME); GO CREATE UNIQUE INDEX AK Index ON #Test (C2) WITH (IGNORE DUP KEY = OFF); GO INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE()); INSERT INTO #Test SELECT * FROM Production.UnitMeasure; GO SELECT COUNT(*) AS [Number of rows] FROM #Test; GO DROP TABLE #Test; GO.
A seguir são apresentados os resultados da segunda instrução INSERT .
Server: Msg 2601, Level 14, State 1, Line 5 Cannot insert duplicate key row in object '#Test' with unique index 'AK Index'. The statement has been terminated. Number of rows -------------- 1.
Observe que nenhuma linha da tabela Production.UnitMeasure foi inserida na tabela, embora somente uma linha violasse a restrição de índice UNIQUE .
G. Usando DROP EXISTING para descartar e recriar um índice.
O exemplo a seguir remove e recria um índice existente na coluna ProductID da tabela Production.WorkOrder no banco de dados AdventureWorks2012 usando a opção DROP EXISTING . As opções FILLFACTOR e PAD INDEX também são definidas.
CREATE NONCLUSTERED INDEX IX WorkOrder ProductID ON Production.WorkOrder(ProductID) WITH (FILLFACTOR = 80, PAD INDEX = ON, DROP EXISTING = ON); GO.
H. Criar um índice em um modo de exibição.
O exemplo a seguir cria uma exibição e um índice nessa exibição. Duas consultas que usam a exibição indexada são incluídas.
-- Set the options to support indexed views SET NUMERIC ROUNDABORT OFF; SET ANSI PADDING, ANSI WARNINGS, CONCAT NULL YIELDS NULL, ARITHABORT, QUOTED IDENTIFIER, ANSI NULLS ON; GO -- Create view with schemabinding IF OBJECT ID ('Sales.vOrders', 'view') IS NOT NULL DROP VIEW Sales.vOrders; GO CREATE VIEW Sales.vOrders WITH SCHEMABINDING AS SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue, OrderDate, ProductID, COUNT BIG(*) AS COUNT FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o WHERE od.SalesOrderID = o.SalesOrderID GROUP BY OrderDate, ProductID; GO -- Create an index on the view CREATE UNIQUE CLUSTERED INDEX IDX V1 ON Sales.vOrders (OrderDate, ProductID); GO -- This query can use the indexed view even though the view is -- not specified in the FROM clause. SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev, OrderDate, ProductID FROM Sales.SalesOrderDetail AS od JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID AND ProductID BETWEEN 700 AND 800 AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101) GROUP BY OrderDate, ProductID ORDER BY Rev DESC; GO -- This query can use the above indexed view SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev FROM Sales.SalesOrderDetail AS od JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID AND DATEPART(mm, OrderDate) = 3 AND DATEPART(yy, OrderDate) = 2002 GROUP BY OrderDate ORDER BY OrderDate ASC; GO.
I. Criando um índice com colunas incluídas (não chave)
O exemplo a seguir cria um índice não clusterizado com uma coluna de chave ( PostalCode ) e quatro colunas não chave ( AddressLine1 , AddressLine2 , City , StateProvinceID ). Uma consulta incluída pelo índice vem em seguida. Para exibir o índice que é selecionado pelo otimizador de consulta, no menu Consulta no SQL Server Management Studio, selecione Exibir Plano de Execução Real antes de executar a consulta.
CREATE NONCLUSTERED INDEX IX Address PostalCode ON Person.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID); GO SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode FROM Person.Address WHERE PostalCode BETWEEN N'98000' and N'99999'; GO.
J. Criar um índice particionado.
O exemplo a seguir cria um índice particionado não clusterizado em TransactionsPS1 , um esquema de partição existente no banco de dados AdventureWorks2012 . Este exemplo pressupõe que o exemplo de índice particionado tenha sido instalado.
CREATE NONCLUSTERED INDEX IX TransactionHistory ReferenceOrderID ON Production.TransactionHistory (ReferenceOrderID) ON TransactionsPS1 (TransactionDate); GO.
K. Criando um índice filtrado.
O exemplo a seguir cria um índice filtrado na tabela Production.BillOfMaterials do banco de dados AdventureWorks2012 . O predicado de filtro pode incluir colunas que não sejam de chave no índice filtrado. O predicado deste exemplo seleciona apenas as linhas em que EndDate é não NULL.
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate" ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS NOT NULL;
L. Criar um índice compactado.
O exemplo a seguir cria um índice em uma tabela não particionada usando a compactação de linha.
CREATE NONCLUSTERED INDEX IX INDEX 1 ON T1 (C2) WITH (DATA COMPRESSION = ROW); GO.
O exemplo a seguir cria um índice em uma tabela particionada usando a compactação de linha em todas as partições do índice.
CREATE CLUSTERED INDEX IX PartTab2Col1 ON PartitionTable1 (Col1) WITH (DATA COMPRESSION = ROW); GO.
O exemplo a seguir cria um índice em uma tabela particionada usando a compactação de página na partição 1 do índice e a compactação de linha nas partições 2 a 4 do índice.
CREATE CLUSTERED INDEX IX PartTab2Col1 ON PartitionTable1 (Col1) WITH ( DATA COMPRESSION = PAGE ON PARTITIONS(1), DATA COMPRESSION = ROW ON PARTITIONS (2 TO 4) ); GO.
M. Criar um índice com a compactação XML.
Aplica-se a : SQL Server 2022 (16.x) e versões posteriores e à versão prévia do Banco de Dados SQL do Azure.
O exemplo a seguir cria um índice em uma tabela não particionada usando a compactação XML. Pelo menos uma coluna no índice precisa ser do tipo de dados xml .
CREATE NONCLUSTERED INDEX IX INDEX 1 ON T1 (C2) WITH (XML COMPRESSION = ON); GO.
O exemplo a seguir cria um índice em uma tabela particionada usando a compactação XML em todas as partições do índice.
CREATE CLUSTERED INDEX IX PartTab2Col1 ON PartitionTable1 (Col1) WITH (XML COMPRESSION = ON); GO.
N. Criar, retomar, pausar e anular operações de índice retomável.
Aplica-se ao : SQL Server (Começando pelo SQL Server 2022 (15.x)) e Banco de Dados SQL do Azure.
-- Execute a resumable online index create statement with MAXDOP=1 CREATE INDEX test idx1 ON test table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON); -- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation. -- Execute a resumable online index creates operation with MAX DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused. CREATE INDEX test idx2 ON test table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX DURATION = 240); -- Pause a running resumable online index creation ALTER INDEX test idx1 ON test table PAUSE; ALTER INDEX test idx2 ON test table PAUSE; -- Resume a paused online index creation ALTER INDEX test idx1 ON test table RESUME; ALTER INDEX test idx2 ON test table RESUME; -- Abort resumable index create operation which is running or paused ALTER INDEX test idx1 ON test table ABORT; ALTER INDEX test idx2 ON test table ABORT;
O. CREATE INDEX com diferentes opções de bloqueio de baixa prioridade.
Os exemplos a seguir usam a opção WAIT AT LOW PRIORITY para especificar estratégias diferentes para lidar com o bloqueio.
--Kill this session after waiting 5 minutes CREATE CLUSTERED INDEX idx 1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT AT LOW PRIORITY (MAX DURATION = 5 MINUTES, ABORT AFTER WAIT = SELF))); GO.
--Kill blocker sessions CREATE CLUSTERED INDEX idx 1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT AT LOW PRIORITY (MAX DURATION = 5 MINUTES, ABORT AFTER WAIT = BLOCKERS))); GO.
O exemplo a seguir usa a opção RESUMABLE e especifica dois valores MAX DURATION , o primeiro aplica-se à opção ABORT AFTER WAIT e o segundo aplica-se à opção RESUMABLE .
--With resumable option; default locking behavior CREATE CLUSTERED INDEX idx 1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT AT LOW PRIORITY (MAX DURATION = 5 MINUTES, ABORT AFTER WAIT = NONE)), RESUMABLE = ON, MAX DURATION = 240 MINUTES);
Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)
P. Sintaxe básica.
Criar, retomar, pausar e anular operações de índice retomável.
Aplica-se ao : SQL Server (Começando pelo SQL Server 2022 (15.x)) e Banco de Dados SQL do Azure.
-- Execute a resumable online index create statement with MAXDOP=1 CREATE INDEX test idx ON test table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON); -- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation. -- Execute a resumable online index creates operation with MAX DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused. CREATE INDEX test idx ON test table WITH (ONLINE = ON, RESUMABLE = ON, MAX DURATION = 240); -- Pause a running resumable online index creation ALTER INDEX test idx ON test table PAUSE; -- Resume a paused online index creation ALTER INDEX test idx ON test table RESUME; -- Abort resumable index create operation which is running or paused ALTER INDEX test idx ON test table ABORT;
Q. Criar um índice não clusterizado em uma tabela no banco de dados atual.
O exemplo a seguir cria um índice não clusterizado na coluna VendorID da tabela ProductVendor .
CREATE INDEX IX ProductVendor VendorID ON ProductVendor (VendorID);
R. Criar um índice clusterizado em uma tabela em outro banco de dados.
O exemplo a seguir cria um índice não clusterizado na coluna VendorID da tabela ProductVendor no banco de dados Purchasing .
CREATE CLUSTERED INDEX IX ProductVendor VendorID ON Purchasing..ProductVendor (VendorID);
S. Criar um índice clusterizado ordenado em uma tabela.
O exemplo a seguir cria um índice clusterizado ordenado nas colunas c1 e c2 da tabela T1 no banco de dados MyDB .
CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 ORDER (c1, c2);
T. Converter um CCI em um índice clusterizado ordenado em uma tabela.
O exemplo a seguir converte o índice columnstore clusterizado existente em um índice columnstore clusterizado ordenado chamado MyOrderedCCI nas colunas c1 e c2 da tabela T2 no banco de dados MyDB .
CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2 ORDER (c1, c2) WITH (DROP EXISTING = ON);
Confira também.
Guia de arquitetura e design de índices do SQL Server Executar operações de índice online Índices e ALTER TABLE ALTER INDEX CREATE PARTITION FUNCTION CREATE PARTITION SCHEME CREATE SPATIAL INDEX CREATE STATISTICS CREATE TABLE CREATE XML INDEX Data Types DBCC SHOW STATISTICS DROP INDEX Índices XML (SQL Server) sys.indexes sys.index columns sys.xml indexes EVENTDATA.
