DROP INDEX (Transact-SQL)
Aplica-se a: SQL Server
Banco de Dados
SQL do Azure Instância Gerenciada de SQL
do Azure PDW (Sistema de Plataforma de Análise) do Azure Synapse Analytics
Remove um ou mais índices relacionais, espaciais, filtrados ou XML do banco de dados atual. Você pode descartar um índice clusterizado e mover a tabela resultante para outro grupo de arquivos ou esquema de partição em uma única transação especificando a MOVE TO
opção.
A DROP INDEX
instrução não se aplica a índices criados por meio de restrições or UNIQUE
PRIMARY KEY
definidoras. Para remover a restrição e o índice correspondente, use ALTER TABLE com a DROP CONSTRAINT
cláusula.
Importante
A sintaxe definida em <drop_backward_compatible_index>
será removida em uma versão futura do SQL Server. Evite usar essa sintaxe em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que usam atualmente o recurso. Em vez disso, use a sintaxe especificada em <drop_relational_or_xml_or_spatial_index>
. Os índices XML não podem ser descartados usando a sintaxe compatível com versões anteriores.
Convenções de sintaxe de Transact-SQL
Sintaxe
Sintaxe para SQL Server (todas as opções, exceto grupo de arquivos e fluxo de arquivos, se aplicam ao Banco de Dados SQL do Azure).
DROP INDEX [ IF EXISTS ]
{ <drop_relational_or_xml_or_spatial_index> [ , ...n ]
| <drop_backward_compatible_index> [ , ...n ]
}
<drop_relational_or_xml_or_spatial_index> ::=
index_name ON <object>
[ WITH ( <drop_clustered_index_option> [ , ...n ] ) ]
<drop_backward_compatible_index> ::=
[ owner_name. ] table_or_view_name.index_name
<object> ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
<drop_clustered_index_option> ::=
{
MAXDOP = max_degree_of_parallelism
| ONLINE = { ON | OFF }
| MOVE TO { partition_scheme_name ( column_name )
| filegroup_name
| "default"
}
[ FILESTREAM_ON { partition_scheme_name
| filestream_filegroup_name
| "default" } ]
}
Sintaxe para Banco de Dados SQL do Azure.
DROP INDEX
{ <drop_relational_or_xml_or_spatial_index> [ , ...n ]
}
<drop_relational_or_xml_or_spatial_index> ::=
index_name ON <object>
<object> ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
Sintaxe para Azure Synapse Analytics e PDW (Analytics Platform System).
DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]
Observação
Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.
Argumentos
IF EXISTS
Aplica-se a: SQL Server 2016 (13.x) e versões posteriores.
Remove condicionalmente o índice somente se ele já existe.
index_name
O nome do índice a ser descartado.
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 associada ao índice. Índices espaciais têm suporte apenas em tabelas.
Para exibir um relatório dos índices em um objeto, use a exibição do catálogo sys.indexes.
O Banco de Dados SQL do Azure dá suporte ao formato de nome de três partes: database_name.[schema_name].object_name
quando o database_name
é o banco de dados atual ou o database_name é tempdb
e o object_name começa com #
.
<drop_clustered_index_option>
Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Banco de Dados SQL.
Controla opções de índice clusterizado. Essas opções não podem ser usadas com outros tipos de índice.
MAXDOP = max_degree_of_parallelism
Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Banco de Dados SQL (somente Níveis de Desempenho P2 e P3).
Substitui a opção de configuração max degree of parallelism durante a operação do índice. Para obter mais informações, consulte Configurar o grau máximo de paralelismo (opção de configuração do servidor). Use MAXDOP
para limitar o número de processadores usados em uma execução de plano paralelo. O máximo é de 64 processadores.
Importante
MAXDOP
não é permitido para índices espaciais ou índices XML.
max_degree_of_parallelism pode ser um dos seguintes valores.
Valor | Descrição |
---|---|
1 |
Suprime a geração de planos paralelos |
>1 |
Restringe o número máximo de processadores usados em uma operação de índice paralelo ao número especificado |
0 (padrão) |
Usa o número real de processadores ou menos com base na carga de trabalho atual do sistema |
Para obter mais informações, consulte Configurar operações de índice paralelo.
Observação
As operações de índice paralelas não estão disponíveis em todas as edições do SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, confira Edições e recursos com suporte no SQL Server 2022.
ONLINE = ON | OFF
Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Banco de Dados SQL do Azure.
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
.
ON
: Bloqueios de mesa de longo prazo não são mantidos. Isso permite que consultas ou atualizações na tabela subjacente continuem.OFF
: Os bloqueios de tabela são aplicados e a tabela fica indisponível durante a operação de índice.
A ONLINE
opção só pode ser especificada quando você descarta índices clusterizados. Para obter mais informações, consulte a seção Comentários.
Observação
As operações de índice online não estão disponíveis em todas as edições de SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, confira Edições e recursos com suporte no SQL Server 2022.
MOVER PARA { partition_scheme_name ( column_name ) | filegroup_name | "padrão" }
Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores. O Banco de Dados SQL dá suporte "default"
como o nome do grupo de arquivos.
Especifica o local para onde mover as linhas de dados que atualmente estão no nível folha do índice clusterizado. Os dados são movidos para o novo local no formulário de um heap. É possível especificar um esquema de partição ou um grupo de arquivos como o novo local, mas o esquema de partição ou o grupo de arquivos deve existir. MOVE TO
não é válido para exibições indexadas ou índices não clusterizados. Se um esquema de partição ou grupo de arquivos não for especificado, a tabela resultante estará localizada no mesmo esquema de partição ou grupo de arquivos que foi definido para o índice clusterizado.
Se um índice clusterizado for descartado usando MOVE TO
, todos os índices não clusterizados na tabela-base serão recriados, mas permanecerão em seus grupos de arquivos ou esquemas de partição originais. Se a tabela base for movida 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 da tabela base (heap). Portanto, mesmo que os índices não clusterizados tenham sido previamente alinhados com o índice clusterizado, eles não poderão mais ser alinhados com o heap. Para obter mais informações sobre o alinhamento de índice particionado, consulte Tabelas e índices particionados.
partition_scheme_name ( column_name )
Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Banco de Dados SQL.
Especifica um esquema de partição como o local para a tabela resultante. O esquema de partição já deve ter sido criado, executando CREATE PARTITION SCHEME ou ALTER PARTITION SCHEME. Se nenhum local estiver especificado e a tabela estiver particionada, a tabela será incluída no mesmo esquema de partição que o índice clusterizado existente.
O nome da coluna no esquema não está restrito às colunas na definição do índice. Qualquer coluna da tabela base pode ser especificada.
filegroup_name
Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores.
Especifica um grupo de arquivos como o local para a tabela resultante. Se nenhum local for especificado e a tabela não for particionada, a tabela resultante será incluída no mesmo grupo de arquivos que o índice clusterizado. O grupo de arquivos já deve existir.
"default"
Especifica o local padrão para a tabela resultante.
Observação
Nesse contexto, default não é uma palavra-chave. É um identificador para o grupo de arquivos padrão e precisa ser delimitado, como em MOVE TO "default"
ou MOVE TO [default]
. Se "default"
for especificado, a QUOTED_IDENTIFIER
opção deverá ser definida ON
para a sessão atual. Essa é a configuração padrão. Para saber mais, confira SET QUOTED_IDENTIFIER.
FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "padrão" }
Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores.
Especifica o local para onde mover a tabela FILESTREAM que atualmente está no nível folha do índice clusterizado. Os dados são movidos para o novo local no formulário de um heap. É possível especificar um esquema de partição ou um grupo de arquivos como o novo local, mas o esquema de partição ou o grupo de arquivos deve existir. FILESTREAM ON
não é válido para exibições indexadas ou índices não clusterizados. Se um esquema de partição não for especificado, os dados estarão localizados no mesmo esquema de partição definido para o índice clusterizado.
partition_scheme_name
Especifica um esquema de partição para os dados FILESTREAM. O esquema de partição já deve ter sido criado, executando CREATE PARTITION SCHEME ou ALTER PARTITION SCHEME. Se nenhum local estiver especificado e a tabela estiver particionada, a tabela será incluída no mesmo esquema de partição que o índice clusterizado existente.
Se você especificar um esquema de partição para MOVE TO
o , deverá usar o mesmo esquema de partição para FILESTREAM ON
o .
filestream_filegroup_name
Especifica um grupo de arquivos FILESTREAM para dados FILESTREAM. Se nenhum local for especificado e a tabela não for particionada, os dados serão incluídos no grupo de arquivos FILESTREAM padrão.
"default"
Especifica o local padrão para os dados FILESTREAM.
Observação
Nesse contexto, default não é uma palavra-chave. É um identificador para o grupo de arquivos padrão e precisa ser delimitado, como em MOVE TO "default"
ou MOVE TO [default]
. Se "default" é especificado, a opção QUOTED_IDENTIFIER
deve ser definida como ON na sessão atual. Essa é a configuração padrão. Para saber mais, confira SET QUOTED_IDENTIFIER.
Comentários
Quando um índice não clusterizado é descartado, a definição de índice é removida dos metadados e as páginas de dados do índice (a árvore B) são removidas dos arquivos de banco de dados. Quando um índice clusterizado é descartado, a definição do índice é removida dos metadados e as linhas de dados armazenadas no nível folha do índice clusterizado são armazenadas na tabela não ordenada resultante, um heap. Todo o espaço ocupado anteriormente pelo índice é recuperado. Em seguida, esse espaço pode ser usado para qualquer objeto de banco de dados.
Observação
A documentação do SQL Server usa o termo árvore B geralmente em referência a índices. Em índices de rowstore, o SQL Server implementa uma árvore B+. Isso não se aplica a índices columnstore ou armazenamentos de dados na memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.
Um índice não poderá ser descartado se o grupo de arquivos no qual ele está localizado estiver offline ou definido como somente leitura.
Quando o índice clusterizado de uma exibição indexada é descartado, todos os índices não clusterizados e estatísticas criadas automaticamente na mesma exibição são descartados automaticamente. As estatísticas criadas manualmente não são descartadas.
A sintaxe <table_or_view_name>.<index_name>
é mantida para compatibilidade com versões anteriores. Um índice XML ou índice espacial não pode ser descartado usando a sintaxe compatível com versões anteriores.
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.
Algumas vezes, os índices são descartados e recriados para reorganizar ou reconstruir o índice, como para aplicar um novo fator de preenchimento ou para reorganizar dados após um carregamento em massa. Para fazer isso, usar ALTER INDEX é mais eficiente, especialmente para índices clusterizados. ALTER INDEX REBUILD
tem otimizações para evitar a sobrecarga de recriação dos índices não clusterizados.
Usar opções com DROP INDEX
Você pode definir as seguintes opções de índice ao descartar um índice clusterizado: MAXDOP
, ONLINE
e MOVE TO
.
Use MOVE TO
para descartar o índice clusterizado e mover a tabela resultante para outro grupo de arquivos ou esquema de partição em uma única transação.
Quando você especifica ONLINE = ON
, as consultas e modificações nos dados subjacentes e nos índices não clusterizados associados não são bloqueadas pela DROP INDEX
transação. Apenas um índice clusterizado pode ser descartado online de cada vez. Para obter uma descrição completa da ONLINE
opção, consulte CREATE INDEX.
Você não poderá descartar um índice clusterizado online se o índice estiver desabilitado em uma exibição ou contiver colunas text, ntext, image, varchar(max), nvarchar(max), varbinary(max) ou xml nas linhas de dados de nível folha.
Usar as ONLINE = ON
opções e MOVE TO
requer mais espaço em disco temporário.
Depois que um índice é descartado, o heap resultante aparece na exibição do sys.indexes
catálogo com NULL
na name
coluna. Para exibir o nome da tabela, junte-se sys.indexes
a sys.tables
on object_id
. Para ver uma consulta de exemplo, consulte o exemplo D.
Em computadores com vários processadores que executam o SQL Server 2005 Enterprise Edition ou posterior, DROP INDEX
o pode usar mais processadores para executar as operações de verificação e classificação associadas à remoção do índice clusterizado, assim como outras consultas. Você pode configurar manualmente o número de processadores usados para executar a DROP INDEX
instrução especificando a MAXDOP
opção index. Para obter mais informações, consulte Configurar operações de índice paralelo.
Quando um índice clusterizado é descartado, as partições de heap correspondentes mantêm sua configuração de compactação de dados, a menos que o esquema de particionamento seja modificado. Se o esquema de particionamento for alterado, todas as partições serão reconstruídas para um estado descompactado (DATA_COMPRESSION = NONE
). As duas etapas a seguir são necessárias para descartar um índice clusterizado e alterar o esquema de particionamento:
Descarte o índice clusterizado.
Modifique a tabela usando uma
ALTER TABLE ... REBUILD ...
opção que especifica a opção de compactação.
Quando um índice clusterizado é descartado OFFLINE
, somente os níveis superiores dos índices clusterizados são removidos; portanto, a operação é rápida. Quando um índice clusterizado é descartado ONLINE
, o SQL Server recria o heap duas vezes, uma para a etapa 1 e outra para a etapa 2. Para obter mais informações sobre compactação de dados, consulte Compactação de dados.
índices XML
As opções não podem ser especificadas quando você solta um índice XML. Além disso, você não pode usar a <table_or_view_name>.<index_name>
sintaxe. Quando um índice XML primário é descartado, todos os índices XML secundários associados são descartados automaticamente. Para obter mais informações, consulte Índices XML (SQL Server).
Índices espaciais
Índices espaciais têm suporte apenas em tabelas. Ao descartar um índice espacial, você não pode especificar nenhuma opção ou usar .<index_name>
. A sintaxe correta é a seguinte:
DROP INDEX <spatial_index_name> ON <spatial_table_name>;
Para obter mais informações sobre índices espaciais, consulte Visão geral de índices espaciais.
Permissões
Para executar DROP INDEX
, no mínimo, a permissão ALTER
na tabela ou exibição é necessária. Essa permissão é concedida por padrão à função de servidor fixa sysadmin e às funções de banco de dados fixas db_ddladmin e db_owner .
Exemplos
Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022
ou AdventureWorksDW2022
, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.
R. Descartar um índice
O exemplo a seguir exclui o índice IX_ProductVendor_BusinessEntityID
na tabela ProductVendor
do banco de dados AdventureWorks2022.
DROP INDEX IX_ProductVendor_BusinessEntityID
ON Purchasing.ProductVendor;
GO
B. Descartar vários índices
O exemplo a seguir exclui dois índices em uma única transação no banco de dados AdventureWorks2022.
DROP INDEX
IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
IX_Address_StateProvinceID ON Person.Address;
GO
C. Descartar um índice clusterizado online e definir a opção MAXDOP
O exemplo a seguir exclui um índice clusterizado com a opção ONLINE
definida como ON
e MAXDOP
definida como 8
. Como a MOVE TO
opção não foi especificada, a tabela resultante é armazenada no mesmo grupo de arquivos que o índice.
Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Banco de Dados SQL.
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);
GO
D. Soltar um índice clusterizado online e mover a tabela para um novo grupo de arquivos
O exemplo a seguir exclui um índice clusterizado online e move a tabela resultante (heap) para o grupo de arquivos NewGroup
usando a cláusula MOVE TO
. As exibições do catálogo sys.indexes
, sys.tables
e sys.filegroups
são consultadas para verificar o posicionamento do índice e da tabela nos grupos de arquivos antes e depois da movimentação. A partir do SQL Server 2016 (13.x), você pode usar a DROP INDEX IF EXISTS
sintaxe.
Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores.
--Create a clustered index on the PRIMARY filegroup if the index does not exist.
CREATE UNIQUE CLUSTERED INDEX
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ON Production.BillOfMaterials (ProductAssemblyID, ComponentID,
StartDate)
ON 'PRIMARY';
GO
-- Verify filegroup location of the clustered index.
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
i.data_space_id, f.name AS [Filegroup Name]
FROM sys.indexes AS i
JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
JOIN sys.tables as t ON i.object_id = t.object_id
AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U')
GO
--Create filegroup NewGroup if it does not exist.
IF NOT EXISTS (SELECT name FROM sys.filegroups
WHERE name = N'NewGroup')
BEGIN
ALTER DATABASE AdventureWorks2022
ADD FILEGROUP NewGroup;
ALTER DATABASE AdventureWorks2022
ADD FILE (NAME = File1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\File1.ndf')
TO FILEGROUP NewGroup;
END
GO
--Verify new filegroup
SELECT * from sys.filegroups;
GO
-- Drop the clustered index and move the BillOfMaterials table to
-- the Newgroup filegroup.
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ON Production.BillOfMaterials
WITH (ONLINE = ON, MOVE TO NewGroup);
GO
-- Verify filegroup location of the moved table.
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
i.data_space_id, f.name AS [Filegroup Name]
FROM sys.indexes AS i
JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
JOIN sys.tables as t ON i.object_id = t.object_id
AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U');
GO
E. Eliminar uma restrição PRIMARY KEY online
Os índices criados como resultado da criação PRIMARY KEY
de restrições ou UNIQUE
não podem ser descartados usando DROP INDEX
. Eles são descartados usando a ALTER TABLE DROP CONSTRAINT
declaração. Para obter mais informações, consulte ALTER TABLE.
O exemplo a seguir exclui um índice clusterizado com uma PRIMARY KEY
restrição descartando a restrição. A ProductCostHistory
tabela não FOREIGN KEY
tem restrições. Se tivesse, essas restrições precisariam ser removidas primeiro.
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
F. Descartar um índice XML
O exemplo a seguir exclui o índice XML na tabela ProductModel
do banco de dados AdventureWorks2022.
DROP INDEX PXML_ProductModel_CatalogDescription
ON Production.ProductModel;
G. Descartar um índice clusterizado em uma tabela FILESTREAM
O exemplo a seguir exclui um índice clusterizado online e move a tabela resultante (heap) e os dados FILESTREAM para o esquema de partição MyPartitionScheme
usando as cláusulas MOVE TO
e FILESTREAM ON
.
Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores.
DROP INDEX PK_MyClusteredIndex
ON dbo.MyTable
WITH (MOVE TO MyPartitionScheme,
FILESTREAM_ON MyPartitionScheme);
GO
Conteúdo relacionado
- ALTER INDEX (Transact-SQL)
- ALTER PARTITION SCHEME (Transact-SQL)
- ALTER TABLE (Transact-SQL)
- CREATE INDEX (Transact-SQL)
- CREATE PARTITION SCHEME (Transact-SQL)
- CREATE SPATIAL INDEX (Transact-SQL)
- CREATE XML INDEX (Transact-SQL)
- EVENTDATA (Transact-SQL)
- sys.indexes
- sys.tables
- sys.filegroups
- sp_spaceused