Share via

DROP INDEX (Transact-SQL)

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)

Rimuove uno o più indici relazionali, spaziali, filtrati o XML dal database corrente. È possibile eliminare un indice cluster e spostare la tabella risultante in un altro filegroup o schema di partizione in una singola transazione specificando l'opzione MOVE TO .

L'istruzione DROP INDEX non si applica agli indici creati definendo PRIMARY KEY o UNIQUE vincoli. Per rimuovere il vincolo e l'indice corrispondente, utilizzare ALTER TABLE con la DROP CONSTRAINT clausola .

Importante

La sintassi definita in <drop_backward_compatible_index> verrà rimossa in una versione futura di SQL Server. Evitare pertanto di utilizzarla in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Utilizzare in alternativa la sintassi specificata in <drop_relational_or_xml_or_spatial_index>. Gli indici XML non possono essere eliminati usando la sintassi compatibile con le versioni precedenti.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per SQL Server (tutte le opzioni tranne filegroup e filestream si applicano a database SQL di 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" } ]
}

Sintassi per database SQL di 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 }

Sintassi per 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 }
[ ; ]

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.

Argomenti

IF EXISTS

Si applica a: SQL Server 2016 (13.x) e versioni successive.

Rimuove in modo condizionale l'indice solo se esiste già.

index_name

Nome dell'indice da eliminare.

database_name

Nome del database.

schema_name

Nome dello schema a cui appartiene la tabella o la vista.

table_or_view_name

Nome della tabella o della vista associata all'indice. Gli indici spaziali sono supportati solo nelle tabelle.

Per visualizzare un report degli indici di un oggetto, usare la vista del catalogo sys.indexes.

database SQL di Azure supporta il formato del nome in tre parti: database_name.[schema_name].object_name quando database_name è il database corrente o il database_name è tempdb e il object_name inizia con #.

<drop_clustered_index_option>

Si applica a: SQL Server 2008 (10.0.x) e versioni successive, database SQL.

Controlla le opzioni degli indici cluster. Queste opzioni non possono essere usate con altri tipi di indice.

MAXDOP = max_degree_of_parallelism

Si applica a: SQL Server 2008 (10.0.x) e versioni successive, database SQL (solo livelli di prestazioni P2 e P3).

Sostituisce l'opzione di configurazione Massimo grado di parallelismo durante l'operazione sull'indice. Per altre informazioni, vedere Configurare il massimo grado di parallelismo (opzione di configurazione del server). Usare MAXDOP per limitare il numero di processori usati in un'esecuzione di piano parallela. Il valore massimo è 64 processori.

Importante

MAXDOP non è consentito per indici spaziali o indici XML.

max_degree_of_parallelism può essere uno dei valori seguenti.

Valore Descrizione
1 Elimina la generazione di piani paralleli
>1 Limita il numero massimo di processori utilizzati in un'operazione di indice parallela al numero specificato
0 (predefinito) Usa il numero effettivo di processori o meno in base al carico di lavoro del sistema corrente

Per altre informazioni, vedere Configurazione di operazioni parallele sugli indici.

Nota

Le operazioni parallele sugli indici non sono disponibili in tutte le edizioni di SQL Server. Per un elenco delle funzionalità supportate dalle varie edizioni di SQL Server, vedere Edizioni e funzionalità supportate di SQL Server 2017.

ONLINE = ON | OFF

Si applica a: SQL Server 2008 (10.0.x) e versioni successive, database SQL di Azure.

Specifica se le tabelle sottostanti e gli indici associati sono disponibili per le query e la modifica dei dati durante l'operazione sugli indici. Il valore predefinito è OFF.

  • ON: i blocchi di tabella a lungo termine non vengono mantenuti. Ciò consente il proseguimento delle query o degli aggiornamenti nella tabella sottostante.

  • OFF: vengono applicati blocchi di tabella e la tabella non è disponibile durante l'operazione sull'indice.

L'opzione ONLINE può essere specificata solo quando si rilasciano indici cluster. Per altre informazioni, vedere la sezione Osservazioni.

Nota

Le operazioni sugli indici online non sono disponibili in ogni edizione di SQL Server. Per un elenco delle funzionalità supportate dalle varie edizioni di SQL Server, vedere Edizioni e funzionalità supportate di SQL Server 2017.

MOVE TO { partition_scheme_name ( column_name ) | filegroup_name | "default" }

Si applica a: SQL Server 2008 (10.0.x) e versioni successive. database SQL supporta "default" come nome del filegroup.

Specifica una posizione in cui spostare le righe dei dati attualmente nel livello foglia dell'indice cluster. I dati vengono spostati nella nuova posizione sotto forma di heap. È possibile specificare come nuova posizione uno schema di partizione o un filegroup, ma questo deve essere già esistente. MOVE TO non è valido per le viste indicizzate o gli indici non cluster. Se non viene specificato uno schema di partizione o un filegroup, la tabella risultante si trova nello stesso schema di partizione o filegroup definito per l'indice cluster.

Se un indice cluster viene eliminato tramite MOVE TO, tutti gli indici non cluster nella tabella di base vengono ricompilati, ma rimangono nei filegroup o negli schemi di partizione originali. Se la tabella di base viene spostata in un filegroup o in uno schema di partizione diverso, gli indici non cluster non vengono spostati in modo che coincidano con la nuova posizione della tabella di base (heap). Pertanto, anche se in precedenza gli indici non cluster erano allineati con gli indici cluster, potrebbero non essere più allineati con l'heap. Per altre informazioni sull'allineamento degli indici partizionati, vedere Tabelle e indici partizionati.

partition_scheme_name ( column_name )

Si applica a: SQL Server 2008 (10.0.x) e versioni successive, database SQL.

Specifica uno schema di partizione come posizione per la tabella risultante. Lo schema di partizione deve essere già creato eseguendo CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. Se non viene specificata alcuna posizione e la tabella è partizionata, la tabella viene inclusa nello stesso schema di partizione dell'indice cluster esistente.

Il nome della colonna nello schema non è limitato alle colonne nella definizione dell'indice. È possibile specificare qualsiasi colonna nella tabella di base.

filegroup_name

Si applica a: SQL Server 2008 (10.0.x) e versioni successive.

Specifica un filegroup come posizione per la tabella risultante. Se non viene specificata alcuna posizione e la tabella non è partizionata, la tabella risultante viene inclusa nello stesso filegroup dell'indice cluster. Il filegroup deve essere già esistente.

"default"

Specifica la posizione predefinita per la tabella risultante.

Nota

In questo contesto default non è una parola chiave, Si tratta di un identificatore per il filegroup predefinito e deve essere delimitato, come in MOVE TO "default" o MOVE TO [default]. Se "default" viene specificato, l'opzione QUOTED_IDENTIFIER deve essere impostata ON per la sessione corrente. Si tratta dell'impostazione predefinita. Per altre informazioni, vedere SET QUOTED_IDENTIFIER.

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }

Si applica a: SQL Server 2008 (10.0.x) e versioni successive.

Specifica una posizione in cui spostare la tabella FILESTREAM che attualmente si trova al livello foglia dell'indice cluster. I dati vengono spostati nella nuova posizione sotto forma di heap. È possibile specificare come nuova posizione uno schema di partizione o un filegroup, ma questo deve essere già esistente. FILESTREAM ON non è valido per le viste indicizzate o gli indici non cluster. Se non viene specificato uno schema di partizione, i dati si trovano nello stesso schema di partizione definito per l'indice cluster.

partition_scheme_name

Specifica uno schema di partizione per i dati FILESTREAM. Lo schema di partizione deve essere già creato eseguendo CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. Se non viene specificata alcuna posizione e la tabella è partizionata, la tabella viene inclusa nello stesso schema di partizione dell'indice cluster esistente.

Se si specifica uno schema di partizione per MOVE TO, è necessario usare lo stesso schema di partizione per FILESTREAM ON.

filestream_filegroup_name

Specifica un filegroup FILESTREAM per i dati FILESTREAM. Se non viene specificato alcun percorso e la tabella non è partizionata, i dati vengono inclusi nel filegroup FILESTREAM predefinito.

"default"

Specifica la posizione predefinita dei dati FILESTREAM.

Nota

In questo contesto default non è una parola chiave, Si tratta di un identificatore per il filegroup predefinito e deve essere delimitato, come in MOVE TO "default" o MOVE TO [default]. Se si specifica "default", l'opzione QUOTED_IDENTIFIER deve essere impostata su ON per la sessione corrente. Si tratta dell'impostazione predefinita. Per altre informazioni, vedere SET QUOTED_IDENTIFIER.

Osservazioni:

Quando viene eliminato un indice non cluster, la definizione dell'indice viene rimossa dai metadati e le pagine dei dati dell'indice (albero B) vengono rimosse dai file di database. Quando viene eliminato un indice cluster, la definizione dell'indice viene rimossa dai metadati e le righe di dati precedentemente archiviate nel livello foglia dell'indice cluster vengono archiviate nella tabella non ordinata risultante, o heap. Tutto lo spazio occupato in precedenza dall'indice viene recuperato, e può essere quindi utilizzato per qualsiasi oggetto di database.

Nota

Nella documentazione di SQL Server viene usato in modo generico il termine albero B in riferimento agli indici. Negli indici rowstore, SQL Server implementa un albero B+. Ciò non si applica a indici columnstore o ad archivi dati in memoria. Per altre informazioni, vedere Architettura e guida per la progettazione degli indici SQL Server e Azure SQL.

Non è possibile eliminare un indice se il filegroup in cui si trova è offline o impostato su sola lettura.

Quando l'indice cluster di una vista indicizzata viene eliminato, vengono eliminati automaticamente tutti gli indici non cluster e le statistiche create automaticamente nella stessa vista. Le statistiche create manualmente non vengono eliminate.

La sintassi <table_or_view_name>.<index_name> viene mantenuta per la compatibilità con le versioni precedenti. Non è possibile eliminare un indice XML o un indice spaziale usando la sintassi compatibile con le versioni precedenti.

Quando vengono eliminati indici con un numero di extent pari o superiore a 128, tramite il motore di database vengono posticipate le effettive deallocazioni delle pagine e i blocchi associati fino al termine del commit della transazione.

Talvolta gli indici vengono eliminati e ricreati per riorganizzare o ricompilare l'indice, ad esempio per applicare un nuovo fattore di riempimento o riorganizzare i dati dopo un'operazione di caricamento bulk. A tale scopo, l'uso di ALTER INDEX è più efficiente, soprattutto per gli indici cluster. ALTER INDEX REBUILD include ottimizzazioni per evitare il sovraccarico della ricompilazione degli indici non cluster.

Usare le opzioni con DROP INDEX

È possibile impostare le opzioni di indice seguenti quando si rilascia un indice cluster: MAXDOP, ONLINEe MOVE TO.

Usare MOVE TO per eliminare l'indice cluster e spostare la tabella risultante in un altro filegroup o schema di partizione in una singola transazione.

Quando si specifica ONLINE = ON, le query e le modifiche ai dati sottostanti e agli indici non cluster associati non vengono bloccati dalla DROP INDEX transazione. È possibile eliminare un solo indice cluster online alla volta. Per una descrizione completa dell'opzione ONLINE , vedere CREATE INDEX.

Non è possibile eliminare un indice cluster online se l'indice è disabilitato in una vista o contiene testo, ntext, image, varchar(max), nvarchar(max), varbinary(max)o colonne xml nelle righe di dati a livello foglia.

L'uso delle ONLINE = ON opzioni e MOVE TO richiede più spazio su disco temporaneo.

Dopo l'eliminazione di un indice, l'heap risultante viene visualizzato nella sys.indexes vista del name catalogo con NULL nella colonna . Per visualizzare il nome della tabella, eseguire il join sys.indexes con sys.tables in object_id. Per una query di esempio, vedere l'esempio D.

Nei computer multiprocessore che eseguono SQL Server 2005 Enterprise Edition o versioni successive potrebbero DROP INDEX usare più processori per eseguire le operazioni di analisi e ordinamento associate all'eliminazione dell'indice cluster, proprio come le altre query. È possibile configurare manualmente il numero di processori usati per eseguire l'istruzione DROP INDEX specificando l'opzione MAXDOP index. Per altre informazioni, vedere Configurazione di operazioni parallele sugli indici.

Quando un indice cluster viene eliminato, le partizioni di heap corrispondenti mantengono l'impostazione di compressione dei dati, a meno che lo schema di partizione non venga modificato. Se lo schema di partizionamento viene modificato, tutte le partizioni vengono ricompilate in uno stato non compresso (DATA_COMPRESSION = NONE). Per eliminare un indice cluster e modificare lo schema di partizione, sono necessari i due passaggi seguenti:

  1. Eliminare l'indice cluster.

  2. Modificare la tabella usando un'opzione ALTER TABLE ... REBUILD ... che specifica l'opzione di compressione.

Quando viene eliminato OFFLINEun indice cluster, vengono rimossi solo i livelli superiori degli indici cluster, pertanto l'operazione è veloce. Quando un indice cluster viene eliminato ONLINE, SQL Server ricompila l'heap due volte, una volta per il passaggio 1 e una volta per il passaggio 2. Per altre informazioni sulla compressione dei dati, vedere Compressione dei dati.

Indici XML

Non è possibile specificare opzioni quando si rilascia un indiceXML. Inoltre, non è possibile usare la <table_or_view_name>.<index_name> sintassi . Quando viene eliminato un indice XML primario, tutti gli indici XML secondari associati vengono eliminati automaticamente. Per altre informazioni, vedere Indici XML (SQL Server).

Indici spaziali

Gli indici spaziali sono supportati solo nelle tabelle. Quando si rilascia un indice spaziale, non è possibile specificare alcuna opzione o usare .<index_name>. La sintassi corretta è la seguente:

DROP INDEX <spatial_index_name> ON <spatial_table_name>;

Per altre informazioni sugli indici spaziali, vedere Panoramica degli indici spaziali.

Autorizzazioni

Per eseguire DROP INDEX, è necessaria almeno l'autorizzazione ALTER per la tabella o la vista. Questa autorizzazione viene concessa per impostazione predefinita al ruolo predefinito del server sysadmin e ai ruoli predefiniti del database db_ddladmin e db_owner .

Esempi

Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.

R. Eliminare un indice

Nell'esempio seguente viene eliminato l'indice IX_ProductVendor_BusinessEntityID nella ProductVendor tabella del database AdventureWorks2022.

DROP INDEX IX_ProductVendor_BusinessEntityID
    ON Purchasing.ProductVendor;
GO

B. Eliminare più indici

Nell'esempio seguente vengono eliminati due indici in una singola transazione nel database AdventureWorks2022.

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

C. Eliminare un indice cluster online e impostare l'opzione MAXDOP

Nell'esempio seguente viene eliminato un indice cluster con l'opzione ONLINE impostata su ON e l'opzione MAXDOP impostata su 8. Poiché l'opzione MOVE TO non è stata specificata, la tabella risultante viene archiviata nello stesso filegroup dell'indice.

Si applica a: SQL Server 2008 (10.0.x) e versioni successive, database SQL.

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

D. Eliminare un indice cluster online e spostare la tabella in un nuovo filegroup

Nell'esempio seguente viene eliminato un indice cluster online e la tabella risultante (heap) viene spostata nel filegroup NewGroup tramite la clausola MOVE TO . Vengono eseguite query sulle viste del catalogo sys.indexes, sys.tablese sys.filegroups per verificare la posizione dell'indice e della tabella nei filegroup prima e dopo lo spostamento. A partire da SQL Server 2016 (13.x), è possibile usare la DROP INDEX IF EXISTS sintassi .

Si applica a: SQL Server 2008 (10.0.x) e versioni successive.

--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. Eliminare un vincolo PRIMARY KEY online

Gli indici creati come risultato della creazione PRIMARY KEY o UNIQUE dei vincoli non possono essere eliminati tramite DROP INDEX. Vengono eliminati usando l'istruzione ALTER TABLE DROP CONSTRAINT . Per altre informazioni, vedere ALTER TABLE.

Nell'esempio seguente viene eliminato un indice cluster con un PRIMARY KEY vincolo eliminando il vincolo . La ProductCostHistory tabella non FOREIGN KEY ha vincoli. In caso contrario, sarebbe stato necessario rimuovere prima i vincoli.

-- 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. Eliminare un indice XML

Nell'esempio seguente viene eliminato un indice XML nella ProductModel tabella del database AdventureWorks2022.

DROP INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel;

G. Eliminare un indice cluster in una tabella FILESTREAM

Nell'esempio seguente viene eliminato un indice cluster online e la tabella risultante (heap) e i dati FILESTREAM vengono spostati nello schema di partizione MyPartitionScheme utilizzando sia la clausola MOVE TO che la clausola FILESTREAM ON.

Si applica a: SQL Server 2008 (10.0.x) e versioni successive.

DROP INDEX PK_MyClusteredIndex
    ON dbo.MyTable
    WITH (MOVE TO MyPartitionScheme,
          FILESTREAM_ON MyPartitionScheme);
GO

Contenuto correlato