DROP INDEX (Transact-SQL)

A instrução DROP INDEX exclui índices do banco de dados atual do SQL Server. Para excluir um índice usando o Pesquisador de Objetos, consulte Como excluir um índice (SQL Server Management Studio).

Você não pode usar a instrução DROP INDEX para remover um índice que tem uma restrição PRIMARY KEY ou UNIQUE. Para remover a restrição e posteriormente o índice, use ALTER TABLE com a cláusula DROP CONSTRAINT.

Para usar a instrução DROP INDEX para excluir um índice clusterizado e mover a tabela resultante para outro grupo de arquivos ou esquema de partição, especifique a opção MOVE TO.

Importante

A sintaxe definida em <drop_backward_compatible_index> será removida em uma versão futura do SQL Server. Evite usar essa sintaxe para descartar índices em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que atualmente utilizam esse recurso. Em vez disso, use a sintaxe especificada em <drop_relational_or_xml_index>. Não é possível descartar índices XML usando a sintaxe compatível com versões anteriores.

Convenções de sintaxe Transact-SQL

Sintaxe

DROP INDEX
{ <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 ] . | schema_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" } ]
}

Argumentos

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

• <drop_clustered_index_option>
  Controla opções de índice clusterizado. Essas opções não podem ser usadas com outros tipos de índice.

• MAXDOP = max_degree_of_parallelism
  Substitui a opção de configuração max degree of parallelism pela duração da operação do índice. Para obter mais informações, consulte Opção max degree of parallelism. 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 nem XML.

  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.

  • 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 Configurando operações de índice paralelo.

  Observação
  As operações de índice paralelas estão disponíveis somente nas edições Enterprise, Developer e Evaluation do SQL Server.

• ONLINE = ON | OFF
  Especifica se as tabelas subjacentes e os índices associados estão disponíveis para consultas e modificação de dados durante a operação de índice. O padrão é OFF.

  • ON
    Bloqueios de tabela não são mantidos a longo prazo. 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 opção ONLINE pode ser especificada ao descartar índices clusterizados. Para obter mais informações, consulte a seção Comentários.

  Observação
  As operações de índice online estão disponíveis somente nas edições Enterprise, Developer e Evaluation do SQL Server.

• MOVE TO { partition_scheme_name**(column_name)** | filegroup_name | "default"
  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 na forma 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 estiver especificado, a tabela resultante estará localizada no mesmo esquema de partição ou grupo de arquivos definido para o índice clusterizado.

  Se um índice clusterizado for removido usando MOVE TO, todos os índice 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 alinhamento de índices particionados, consulte Diretrizes especiais para índices particionados.

  • partition_scheme_name**(column_name)**
    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 do índice clusterizado existente.

    O nome da coluna no esquema não é restringido às colunas na definição do índice. Qualquer coluna da tabela base pode ser especificada.

  • filegroup_name
    Especifica um grupo de arquivos como o local para a tabela resultante. Se nenhum local estiver especificado e a tabela não estiver 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 do grupo de arquivos padrão e deve ser delimitado, como em MOVE TO "default" ou MOVE TO [default]. Se "default" estiver especificado, a opção QUOTED_IDENTIFIER deverá ser definida como ON para a sessão atual. Essa é a configuração padrão. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).

• FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }
  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 na forma 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 nem índices não clusterizados. Se um esquema de partição não estiver 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 do índice clusterizado existente.

    Se você especificar um esquema de partição para MOVE TO, deverá usar o mesmo esquema de partição para FILESTREAM ON.

  • filestream_filegroup_name
    Especifica um grupo de arquivos FILESTREAM para dados FILESTREAM. Se nenhum local estiver especificado e a tabela não estiver 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 do grupo de arquivos padrão e deve ser delimitado, como em MOVE TO "default" ou MOVE TO [default]. Se "default" estiver especificado, a opção QUOTED_IDENTIFIER deverá estar definida como ON para a sessão atual. Essa é a configuração padrão. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).

Comentários

Quando um índice não clusterizado é removido, a definição do índice é removida dos metadados e as páginas de dados do índice (a árvore B) são removidas dos arquivos do banco de dados. Quando um índice clusterizado é removido, 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.

Um índice não poderá ser removido se o grupo de arquivos no qual está localizado estiver offline ou definido como somente leitura.

Quando o índice clusterizado de uma exibição indexada é removido, todos os índices não clusterizados e estatísticas criadas automaticamente na mesma exibição são removidos automaticamente. As estatísticas criadas manualmente não são removidas.

A sintaxe table_or_view_name**.**index_name é mantida para compatibilidade com versões anteriores. Um índice XML ou espacial não pode ser removido usando a sintaxe compatível com versões anteriores.

Quando índices com 128 extensões ou mais são removidos, o Mecanismo de Banco de Dados adia as desalocações de página atuais e seus bloqueios associados até depois da confirmação da transação. Para obter mais informações, consulte Descartando e recriando objetos grandes.

Algumas vezes, os índices são removidos 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, é mais eficiente usar ALTER INDEX, principalmente para índices clusterizados. ALTER INDEX REBUILD tem otimizações para evitar a sobrecarga da reconstrução de índices não clusterizados.

Usando opções com DROP INDEX

É possível definir as seguintes opções de índice ao remover um índice clusterizado: MAXDOP, ONLINE e MOVE TO.

Use MOVE TO para remover o índice clusterizado e mover a tabela resultante para outro grupo de arquivos ou esquema de partição em uma única transação.

Quando ONLINE = ON é especificado, as consultas e modificações nos dados subjacentes e os índices não clusterizados associados não são bloqueados pela transação DROP INDEX. Apenas um índice clusterizado pode ser removido online de cada vez. Para obter uma descrição completa da opção ONLINE, consulte CREATE INDEX (Transact-SQL).

Não será possível remover 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 em nível folha.

O uso das opções ONLINE = ON e MOVE TO requer espaço em disco temporário adicional. Para obter mais informações, consulte Determinando requisitos de espaço em disco de índice.

Após a remoção de um índice, o heap resultante aparece na exibição do catálogo sys.indexes com NULL na coluna name. Para exibir o nome da tabela, una sys.indexes com sys.tables em object_id. Para ver uma consulta de exemplo, consulte o exemplo D.

Em computadores com multiprocessadores que estão executando o SQL Server 2005 Enterprise Edition ou posterior, DROP INDEX pode usar mais processadores para executar operações de exame e de classificação associadas à remoção do índice clusterizado, exatamente como fazem outras consultas. É possível configurar manualmente o número de processadores usados para executar a instrução DROP INDEX especificando a opção de índice MAXDOP. Para obter mais informações, consulte Configurando operações de índice paralelo.

Quando um índice clusterizado é removido, as partições do heap correspondente 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 não compactado (DATA_COMPRESSION = NONE). As duas etapas a seguir são necessárias para remover um índice clusterizado e alterar o esquema de particionamento:

1. Descarte o índice clusterizado.

2. Modifique a tabela usando uma opção ALTER TABLE... REBUILD... especificando a opção de compactação.

Quando um índice clusterizado é removido OFFLINE, apenas os níveis superiores dos índices clusterizados são removidos, portanto, a operação é bastante rápida. Quando um índice clusterizado é descartado ONLINE, o SQL Server reconstrói 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 Criando tabelas e índices compactados.

Índices XML

Não é possível especificar opções ao remover umíndice XML. Além disso, não é possível usar a sintaxe table_or_view_nameindex_name. Quando um índice XML primário é removido, todos os índices XML secundários associados são removidos automaticamente. Para obter mais informações, consulte Índices em colunas de tipo de dados XML.

Índices espaciais

Índices espaciais têm suporte apenas em tabelas. Ao remover um índice espacial, você não pode especificar nenhuma opção nem 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 Trabalhando com índices espaciais (Mecanismo de Banco de Dados).

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

A. Descartando um índice

O exemplo a seguir exclui o índice IX_ProductVendor_VendorID da tabela ProductVendor.

USE AdventureWorks;
GO
DROP INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor;
GO

B. Removendo vários índices

O exemplo a seguir exclui dois índices em uma única transação.

USE AdventureWorks;
GO
DROP INDEX
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
    IX_VendorAddress_AddressID ON Purchasing.VendorAddress;
GO

C. Removendo um índice clusterizado online e definindo 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 opção MOVE TO não foi especificada, a tabela resultante é armazenada no mesmo grupo de arquivos que o índice.

Observação
Este exemplo pode ser executado apenas no SQL Server 2005 Enterprise Edition ou versão posterior.

USE AdventureWorks;
GO
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
    ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);
GO

D. Removendo um índice clusterizado online e movendo 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.

USE AdventureWorks;
GO
--Create a clustered index on the PRIMARY filegroup if it does not exist.
IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 
            N'AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate')
    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.
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the ALTER DATABASE statement 
IF NOT EXISTS (SELECT name FROM sys.filegroups
                WHERE name = N'NewGroup')
    BEGIN
    ALTER DATABASE AdventureWorks
        ADD FILEGROUP NewGroup;
    EXECUTE ('ALTER DATABASE AdventureWorks
        ADD FILE (NAME = File1,
            FILENAME = '''+ @data_path + '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. Removendo uma restrição PRIMARY KEY online

Índices criados em decorrência da criação de restrições PRIMARY KEY ou UNIQUE não podem ser removidos usando DROP INDEX. Eles são removidos usando a instrução ALTER TABLE DROP CONSTRAINT. Para obter mais informações, consulte ALTER TABLE.

O exemplo a seguir exclui um índice clusterizado com uma restrição PRIMARY KEY removendo a restrição. A tabela ProductCostHistory não tem nenhuma restrição FOREIGN KEY. Se tivesse, essas restrições precisariam ser removidas primeiro.

USE AdventureWorks;
GO
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
ALTER TABLE Production.ProductCostHistory
    DROP CONSTRAINT PK_ProductCostHistory_ProductID_StartDate
        WITH (ONLINE = ON);
GO

F. Descartando um índice XML

O exemplo a seguir remove um índice XML da tabela ProductModel.

USE AdventureWorks;
GO
DROP INDEX PXML_ProductModel_CatalogDescription 
    ON Production.ProductModel;
GO

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

USE MyDatabase;
GO
DROP INDEX PK_MyClusteredIndex 
    ON dbo.MyTable 
    MOVE TO MyPartitionScheme
    FILESTREAM_ON MyPartitionScheme;
GO

Consulte também

Referência

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 (Transact-SQL)

sys.tables (Transact-SQL)

sys.filegroups (Transact-SQL)

sp_spaceused (Transact-SQL)

Conceitos

Descartando índices

Outros recursos

Determinando requisitos de espaço em disco de índice