Partilhar via

ÍNDICE DE QUEDA (Transact-SQL)

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

Remove um ou mais índices relacionais, espaciais, filtrados ou XML da base de dados atual. Pode eliminar um índice agrupado e mover a tabela resultante para outro grupo de ficheiros ou esquema de partição numa única transação, especificando a MOVE TO opção.

A DROP INDEX instrução não se aplica a índices criados por restrições definidas PRIMARY KEY por OR UNIQUE . 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 numa versão futura do SQL Server. Evite usar esta sintaxe em novos trabalhos de desenvolvimento e planeie modificar as aplicações que atualmente utilizam a funcionalidade. Use a sintaxe especificada abaixo <drop_relational_or_xml_or_spatial_index> em vez disso. Índices XML não podem ser eliminados usando sintaxe retrocompatível.

Transact-SQL convenções de sintaxe

Sintaxe

Sintaxe para SQL Server (todas as opções, exceto grupo de ficheiros e fluxo de ficheiros, aplicam-se à base de dados Azure SQL).

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 do 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 o Azure Synapse Analytics and Analytics Platform System (PDW).

DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

Arguments

SE EXISTE

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores.

O índice só diminui condicionalmente se este já existir.

index_name

O nome do índice será eliminado.

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 vista associada ao índice. Índices espaciais são suportados apenas em tabelas.

Para ver os detalhes sobre todos os índices numa base de dados, utilize a vista de catálogo sys.indexes .

Azure SQL Database suporta o formato de nome em três partes: database_name.schema_name.object_name quando database_name é a base de dados atual, ou a database_name é tempdb e object_name começa com # ou ##.

<drop_clustered_index_option>

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, SQL Database.

Controla as opções do índice clusterizado. Estas 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, SQL Database (apenas níveis de desempenho P2 e P3).

Sobrepõe a opção de max degree of parallelism configuração durante a operação do índice. Para mais informações, veja Configurar o grau máximo de paralelismo (opção de configuração do servidor). Use MAXDOP para limitar o número de processadores usados na operação de construção do índice. O máximo é de 64 processadores.

Importante

MAXDOP não é permitido para índices espaciais ou XML.

max_degree_of_parallelism pode ser um dos seguintes valores.

Valor Description
1 Suprime a geração paralela de planos
>1 Restringe o número máximo de processadores usados numa operação de compilação de índice paralelo ao número especificado
0 (padrão) Utiliza 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 paralelo não estão disponíveis em todas as edições do SQL Server. Para obter uma lista de funcionalidades suportadas pelas edições do SQL Server, consulte Edições e funcionalidades suportadas do SQL Server 2022.

ONLINE = EM | DESLIGADO

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Azure SQL Database.

Especifica se as tabelas subjacentes e os índices associados estão disponíveis para consultas e modificação de dados durante a operação de índice. A predefinição é OFF.

  • ON: Fechaduras de mesa a longo prazo não são seguras. Isto permite que as consultas ou atualizações à tabela subjacente continuem.

  • OFF: Bloqueios de tabela são aplicados e a tabela não está disponível durante a operação de indexação.

A ONLINE opção só pode ser especificada quando eliminas índices agrupados. Para obter mais informações, consulte a seção Observações.

Observação

As operações de índice online não estão disponíveis em todas as edições do SQL Server. Para obter uma lista de funcionalidades suportadas pelas edições do SQL Server, consulte Edições e funcionalidades suportadas do SQL Server 2022.

MOVER PARA { partition_scheme_name ( column_name ) | filegroup_name | "default" }

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores. A base de dados SQL suporta "default" como nome do grupo de ficheiros.

Especifica uma localização para mover as linhas de dados que atualmente estão no nível da folha do índice agrupado. Os dados são movidos para a nova localização sob a forma de um heap. Pode especificar um esquema de partições ou grupo de ficheiros como nova localização, mas o esquema de partições ou grupo de ficheiros já deve existir. MOVE TO não é válido para exibições indexadas ou índices não clusterizados. Se um esquema de partições ou grupo de ficheiros não for especificado, a tabela resultante está localizada no mesmo esquema de partições ou grupo de ficheiros definido para o índice agrupado.

Se um índice agrupado for eliminado usando MOVE TO, quaisquer índices não agrupados na tabela base são reconstruídos, mas permanecem nos seus grupos de ficheiros ou esquemas de partições originais. Se a tabela base for movida para um grupo de ficheiros ou esquema de partição diferente, os índices não agrupados não são movidos para coincidir com a nova localização da tabela base (heap). Portanto, mesmo que os índices não agrupados estivessem previamente alinhados com o índice agrupado, podem deixar de estar alinhados com o heap. Para obter mais informações sobre 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, SQL Database.

Especifica um esquema de partição como localização para a tabela resultante. O esquema de partição já deve ser criado, executando ou CREATE PARTITION SCHEME ou ALTER PARTITION SCHEME. Se não for especificada a localização e a tabela for particionada, a tabela é incluída no mesmo esquema de partição do índice clusterizado existente.

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

filegroup_name

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores.

Especifica um grupo de ficheiros como local para a tabela resultante. Se não for especificada a localização e a tabela não estiver particionada, a tabela resultante é incluída no mesmo grupo de ficheiros que o índice agrupado. O grupo de arquivos já deve existir.

[padrão]

Especifica a localização padrão para a tabela resultante.

Observação

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

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores.

Especifica um local para mover a tabela FILESTREAM que está atualmente no nível folha do índice agrupado. Os dados são movidos para a nova localização sob a forma de um heap. Pode especificar um esquema de partições ou grupo de ficheiros como nova localização, mas o esquema de partições ou grupo de ficheiros já 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 estão localizados no mesmo esquema de partição definido para o índice agrupado.

partition_scheme_name

Especifica um esquema de partição para os dados do FILESTREAM. O esquema de partição já deve ser criado, executando ou CREATE PARTITION SCHEME ou ALTER PARTITION SCHEME. Se não for especificada a localização e a tabela for particionada, a tabela é incluída no mesmo esquema de partição do índice clusterizado existente.

Se especificar um esquema de partição para MOVE TO, deve usar o mesmo esquema de partição para FILESTREAM ON.

filestream_filegroup_name

Especifica um grupo de ficheiros FILESTREAM para dados FILESTREAM. Se não for especificada a localização e a tabela não estiver particionada, os dados são incluídos no grupo de ficheiros FILESTREAM predefinido.

[padrão]

Especifica a localização padrão para os dados do FILESTREAM.

Observação

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

Observações

Quando um índice não agrupado é eliminado, a definição do índice é removida dos metadados, e as páginas de dados do índice (a árvore B) são removidas dos ficheiros da base de dados. Quando um índice agrupado é eliminado, a definição do índice é removida dos metadados e as linhas de dados que estavam armazenadas no nível folha do índice agrupado são armazenadas na tabela não ordenada resultante, um heap. Todo o espaço anteriormente ocupado pelo índice é recuperado. Este espaço pode então ser usado para qualquer objeto de base de dados.

Observação

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

Um índice não pode ser eliminado se o grupo de ficheiros onde está está offline ou configurado para apenas leitura.

Quando o índice agrupado de uma vista indexada é eliminado, todos os índices não agrupados e estatísticas auto-criadas na mesma vista são automaticamente eliminados. Estatísticas criadas manualmente não são descartadas.

A sintaxe <table_or_view_name>.<index_name> é mantida para compatibilidade retroativa. Um índice XML ou um índice espacial não pode ser eliminado usando a sintaxe retrocompatível.

Por vezes, índices são eliminados e recriados para reorganizar ou reconstruir o índice, como para aplicar um novo valor de fator de preenchimento ou para reorganizar dados após um carregamento em massa. Para isso, usar o ALTER INDEX é mais eficiente, especialmente para índices agrupados. ALTER INDEX REBUILD tem otimizações para evitar a sobrecarga de reconstruir os índices não agrupados.

Realocação diferida

Quando índices com 128 extensões ou mais são eliminados, o Motor de Base de Dados adia as reais distribuições de páginas, e os bloqueios associados, até depois de a transação ser confirmada. Os índices são descartados em duas fases separadas: lógica e física. Na fase lógica, as unidades de alocação existentes usadas pelo índice são marcadas para desalocação e bloqueadas até que a transação seja comprometida. Na fase física, um processo em segundo plano remove as páginas marcadas para deslocação. Isto significa que o espaço libertado por DROP INDEX pode não estar disponível para novas alocações imediatamente.

Se a recuperação acelerada da base de dados estiver ativada, as fases lógicas e físicas separadas são usadas independentemente do número de extensões.

Use opções com DROP INDEX

Pode definir as seguintes opções de índice ao eliminar um índice agrupado: MAXDOP, ONLINE, e MOVE TO.

Use MOVE TO para eliminar o índice agrupado e mover a tabela resultante para outro grupo de ficheiros ou esquema de partição numa única transação.

Quando especificas ONLINE = ON, as consultas e modificações aos dados subjacentes e aos índices não agrupados associados não são bloqueados pela DROP INDEX transação. Apenas um índice agrupado pode ser eliminado online de cada vez. Para uma descrição completa da ONLINE opção, veja CRIAR ÍNDICE.

Não podes disponibilizar um índice clusterizado online se o índice estiver desativado numa visualização, ou se contiver texto, ntext, imagem, varchar(max),nvarchar(max), varbinary(max) ou colunas xml nas linhas de dados ao nível da folha.

Usar as ONLINE = ON opções e MOVE TO requer mais espaço temporário em disco.

Depois de um índice ser eliminado, o heap resultante aparece na sys.indexes vista de catálogo com NULL na name coluna. Para ver o nome da tabela, junte-se sys.indexes a sys.tables em object_id. Para uma consulta de exemplo, veja exemplo D.

Em computadores multiprocessador que executam SQL Server 2005 Enterprise edição ou posterior, DROP INDEX podem ser usados mais processadores para realizar as operações de varrimento e ordenação associadas à eliminação do índice clusterizado, tal como fazem outras consultas. Pode configurar manualmente o número de processadores usados para executar a DROP INDEX instrução especificando a MAXDOP opção de índice. 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 são reconstruídas para um estado não comprimido (DATA_COMPRESSION = NONE). Para eliminar um índice agrupado e alterar o esquema de particionamento são necessários os seguintes dois passos:

  1. Solte o índice clusterizado.

  2. Modifica a tabela usando uma ALTER TABLE ... REBUILD ... opção que especifique a opção de compressão.

Quando um índice agrupado é eliminado OFFLINE, apenas os níveis superiores dos índices agrupados são removidos; portanto, a operação é rápida. Quando um índice clusterizado é eliminado ONLINE, o SQL Server reconstrói o heap duas vezes, uma para o passo 1 e outra para o passo 2. Para obter mais informações sobre compactação de dados, consulte Compactação de dados.

Índices XML

Não se podem especificar opções quando se elimina um índice XML. Além disso, não podes usar a <table_or_view_name>.<index_name> sintaxe. Quando um índice XML primário é eliminado, todos os índices XML secundários associados são automaticamente eliminados. Para obter mais informações, consulte Índices XML (SQL Server).

Índices espaciais

Índices espaciais são suportados apenas em tabelas. Quando deixas cair um índice espacial, não podes especificar opções nem usar a <table_or_view_name>..<index_name> sintaxe. A sintaxe correta é a seguinte:

DROP INDEX <spatial_index_name> ON <spatial_table_name>;

Para mais informações sobre índices espaciais, consulte Visão Geral dos Índices Espaciais.

Permissions

Para executar DROP INDEX, no mínimo, é necessário possuir ALTER permissão na tabela ou vista. Esta permissão é concedida por defeito ao sysadmin papel fixo do servidor e aos db_ddladmindb_owner papéis fixos de base de dados.

Examples

Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2025 ou AdventureWorksDW2025, que pode ser descarregado da página inicial de Exemplos e Projetos da Comunidade do Microsoft SQL Server.

A. Eliminar um índice

O exemplo seguinte elimina o índice IX_ProductVendor_BusinessEntityID da ProductVendor tabela na base de dados AdventureWorks2025.

DROP INDEX IX_ProductVendor_BusinessEntityID
    ON Purchasing.ProductVendor;
GO

B. Eliminar múltiplos índices

O exemplo seguinte elimina dois índices numa única transação na base de dados AdventureWorks2025.

DROP INDEX
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
    IX_Address_StateProvinceID ON Person.Address;
GO

C. Coloque um índice clusterizado online e defina a opção MAXDOP

O exemplo seguinte elimina um índice agrupado com a ONLINE opção definida para ON e MAXDOP definida para 8. Como a MOVE TO opção não foi especificada, a tabela resultante é armazenada no mesmo grupo de ficheiros que o índice.

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, SQL Database.

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

D. Coloque um índice clusterizado online e mude a tabela para um novo grupo de ficheiros

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 de catálogo sys.indexes, sys.tablese sys.filegroups são consultadas para verificar o índice e o posicionamento da tabela nos grupos de arquivos antes e depois da mudança. A partir do SQL Server 2016 (13.x), podes 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];

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

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

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

E. Eliminar uma restrição de CHAVE PRIMÁRIA online

Índices criados como resultado da criação PRIMARY KEY de restrições ou UNIQUE não podem ser eliminados usando DROP INDEX. São retirados com base na ALTER TABLE DROP CONSTRAINT declaração. Para obter mais informações, consulte ALTER TABLE.

O exemplo seguinte elimina um índice clusterizado com uma PRIMARY KEY restrição ao eliminar a restrição. A ProductCostHistory tabela não tem restrições FOREIGN KEY . Se acontecesse, essas restrições teriam de 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. Eliminar um índice XML

O exemplo seguinte coloca um índice XML na ProductModel tabela da base de dados AdventureWorks2025.

DROP INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel;

G. Coloque um índice clusterizado numa tabela FILESTREAM

O exemplo seguinte elimina um índice clusterizado online e move os dados resultantes da tabela (heap) e do FILESTREAM para o MyPartitionScheme esquema de partições, usando tanto a MOVE TO cláusula como a FILESTREAM ON cláusula.

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

Conteúdo relacionado

  • Last updated on