DROP INDEX (Transact-SQL)

L'istruzione DROP INDEX elimina gli indici dal database di SQL Server corrente. Per eliminare un indice utilizzando Esplora oggetti, vedere Procedura: Eliminazione di un indice (SQL Server Management Studio).

Non è possibile utilizzare l'istruzione DROP INDEX per rimuovere un indice che dispone di un vincolo PRIMARY KEY o UNIQUE. Per rimuovere il vincolo e quindi eliminare l'indice, utilizzare ALTER TABLE con la clausola DROP CONSTRAINT.

Per utilizzare l'istruzione DROP INDEX per eliminare un indice cluster e spostare la tabella risultante in un altro filegroup o schema di partizione, specificare l'opzione MOVE TO.

Importante

La sintassi definita in <drop_backward_compatible_index> verrà rimossa in una versione futura di SQL Server. Evitare di utilizzare questa sintassi per eliminare gli indici in un nuovo progetto di sviluppo e pianificare la modifica delle applicazioni che ne fanno uso. Utilizzare in alternativa la sintassi specificata in <drop_relational_or_xml_index>. Non è possibile eliminare indici XML utilizzando la sintassi compatibile con le versioni precedenti.

Convenzioni della sintassi Transact-SQL

Sintassi

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" } ]
}

Argomenti

• index_name
  Nome dell'indice che si desidera eliminare.

• database_name
  Nome del database.

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

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

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

• <drop_clustered_index_option>
  Controlla le opzioni degli indici cluster. Non è possibile utilizzare queste opzioni con altri tipi di indice.

• MAXDOP = max_degree_of_parallelism
  Ignora l'opzione di configurazione max degree of parallelism per la durata dell'operazione sull'indice. Per ulteriori informazioni, vedere Opzione max degree of parallelism. Utilizzare MAXDOP per limitare il numero di processori utilizzati in un'esecuzione del piano parallelo. Il valore massimo è 64 processori.

  Importante
  L'utilizzo di MAXDOP non è consentito per gli indici spaziali o XML.

  I valori possibili per max_degree_of_parallelism sono i seguenti:

  • 1
    Disattiva la generazione di piani paralleli.

  • >1
    Limita il numero massimo di processori utilizzati in un'operazione parallela sugli indici al numero specificato.

  • 0 (predefinito)
    Utilizza il numero effettivo di processori o un numero inferiore in base al carico di lavoro corrente del sistema.

  Per ulteriori informazioni, vedere Configurazione di operazioni a indici paralleli.

  [!NOTA]

  Le operazioni parallele sugli indici sono disponibili solo nelle edizioni Enterprise, Developer ed Evaluation di SQL Server.

• ONLINE = ON | OFF
  Specifica se le tabelle sottostanti e gli indici associati sono disponibili per le query e per la modifica dei dati durante l'operazione sull'indice. L'impostazione predefinita è OFF.

  • ON
    I blocchi di tabella a lungo termine non vengono mantenuti. Ciò consente il proseguimento di query o aggiornamenti per la tabella sottostante.

  • OFF
    Vengono applicati blocchi a livello di tabella e la tabella non è disponibile per la durata dell'operazione sull'indice.

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

  [!NOTA]

  Le operazioni sugli indici in linea sono disponibili solo nelle edizioni Enterprise, Developer ed Evaluation di SQL Server.

• MOVE TO { partition_scheme_name**(column_name)** | filegroup_name | "default"
  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 questi devono essere già esistenti. MOVE TO non è valido per le viste indicizzate o per gli indici non cluster. Se uno schema di partizione o filegroup non viene specificato, la tabella risultante verrà inserita 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 del database vengono ricostruiti, 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 base alla nuova posizione della tabella di base (heap). È pertanto possibile che anche gli indici non cluster allineati in precedenza con gli indici cluster non risultino più allineati con l'heap. Per ulteriori informazioni sull'allineamento degli indici partizionati, vedere Linee guida specifiche per gli indici partizionati.

  • nome_schema_partizione**(nome_colonna)**
    Specifica uno schema di partizione come posizione per la tabella risultante. È necessario che lo schema di partizione sia già stato creato tramite 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 di colonna nello schema non è limitato alle colonne nella definizione dell'indice. È possibile specificare qualsiasi colonna nella tabella di base.

  • filegroup_name
    Specifica un filegroup come posizione per la tabella risultante. Se non viene specificata una 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, ma 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. Questa è l'impostazione predefinita. Per ulteriori informazioni, vedere SET QUOTED_IDENTIFIER (Transact-SQL).

• FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }
  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 questi devono essere già esistenti. FILESTREAM ON non è valido per viste indicizzate o indici non cluster. Se non viene specificato uno schema di partizione, i dati verranno inclusi nello stesso schema di partizione definito per l'indice cluster.

  • nome_schema_partizione
    Specifica uno schema di partizione per i dati FILESTREAM. È necessario che lo schema di partizione sia già stato creato tramite 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 un schema di partizione per MOVE TO, è necessario utilizzare lo stesso schema di partizione per FILESTREAM ON.

  • filestream_filegroup_name
    Specifica un filegroup FILESTREAM per i dati FILESTREAM. Se non viene specificata una posizione 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, ma 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. Questa è l'impostazione predefinita. Per ulteriori informazioni, vedere SET QUOTED_IDENTIFIER (Transact-SQL).

Osservazioni

Quando si elimina 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 del 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.

Non è possibile eliminare un indice se il filegroup in cui si trova non è in linea oppure è in sola lettura.

Quando si elimina l'indice cluster di una vista indicizzata, tutte le statistiche create automaticamente e tutti gli indici non cluster della stessa vista vengono eliminati automaticamente. Le statistiche create manualmente non vengono eliminate.

La sintassi table_or_view_name**.**index_name è supportata per compatibilità con le versioni precedenti. Non è possibile eliminare un indice XML o spaziale tramite la sintassi compatibile con le versioni precedenti.

Quando vengono eliminati gli indici con 128 o più extent, il Motore di database posticipa le deallocazioni effettive delle pagine e dei relativi blocchi associati fino al termine dell'esecuzione del commit della transazione. Per ulteriori informazioni, vedere Eliminazione e ricostruzione di oggetti di grandi dimensioni.

Talvolta gli indici vengono eliminati e ricreati per riorganizzare o ricostruire l'indice, ad esempio per applicare un nuovo fattore di riempimento o riorganizzare i dati dopo un'operazione di caricamento bulk. Per eseguire questa operazione, l'utilizzo di ALTER INDEX risulta più efficace, specialmente per gli indici cluster. Per ALTER INDEX REBUILD sono disponibili ottimizzazioni che consentono di impedire la ricostruzione degli indici non cluster.

Utilizzo delle opzioni con DROP INDEX

Quando si elimina un indice cluster, è possibile impostare le opzioni di indice MAXDOP, ONLINE e MOVE TO.

Utilizzare l'opzione 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 bloccate dalla transazione DROP INDEX. È possibile eliminare un solo indice cluster in linea alla volta. Per una descrizione completa dell'opzione ONLINE, vedere CREATE INDEX (Transact-SQL).

Non è possibile eliminare un indice cluster in linea se l'indice è disattivato in una vista o include colonne di tipo text, ntext, image, varchar(max), nvarchar(max), varbinary(max) o xml nelle righe di dati a livello foglia.

Per l'utilizzo delle opzioni ONLINE = ON e MOVE TO è necessario spazio su disco temporaneo aggiuntivo. Per ulteriori informazioni, vedere Individuazione dei requisiti di spazio su disco per gli indici.

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

Nei computer multiprocessore in cui è in esecuzione SQL Server 2005 Enterprise Edition o versione successiva l'istruzione DROP INDEX, allo stesso modo di altre query, può utilizzare più processori per eseguire le operazioni di analisi e di ordinamento associate all'eliminazione dell'indice cluster. È possibile configurare manualmente il numero di processori utilizzati per eseguire l'istruzione DROP INDEX mediante l'opzione di indice MAXDOP. Per ulteriori informazioni, vedere Configurazione di operazioni a indici paralleli.

Quando un indice cluster viene eliminato, le partizioni di heap corrispondenti mantengono la loro impostazione di compressione dei dati, a meno che lo schema di partizione non venga modificato. In tal caso, tutte le partizioni vengono ricostruite in un stato non compresso (DATA_COMPRESSION = NONE). Per eliminare un indice cluster e modificare lo schema di partizione, effettuare i due passaggi seguenti:

1. Eliminare l'indice cluster.

2. Modificare la tabella utilizzando un'opzione ALTER TABLE ... REBUILD... specificando l'opzione di compressione.

Quando un indice cluster viene eliminato in modalità OFFLINE, vengono rimossi solo i livelli superiori degli indici cluster, pertanto l'operazione è piuttosto veloce. Quando un indice cluster viene eliminato in modalità in linea, SQL Server ricostruisce l'heap due volte, una volta per il passaggio 1 e una volta per il passaggio 2. Per ulteriori informazioni sulla compressione dei dati, vedere Creazione di tabelle e di indici compressi.

Indici XML

Quando si elimina un indice XML non è possibile specificare opzioni. Non è inoltre possibile utilizzare la sintassi table_or_view_name**.**index_name. Quando viene eliminato un indice XML primario, tutti gli indici XML secondari associati vengono eliminati automaticamente. Per ulteriori informazioni, vedere Indici su colonne con tipo di dati XML.

Indici spaziali

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

DROP INDEX spatial_index_name ON spatial_table_name;

Per ulteriori informazioni sugli indici spaziali, vedere Utilizzo degli indici spaziali (Motore di database).

Autorizzazioni

Per eseguire DROP INDEX è necessario disporre almeno dell'autorizzazione ALTER sulla tabella o sulla 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

A. Eliminazione di un indice

Nell'esempio seguente viene eliminato l'indice IX_ProductVendor_VendorID nella tabella ProductVendor.

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

B. Eliminazione di più indici

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

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

C. Eliminazione di un indice cluster in linea e impostazione dell'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 è archiviata nello stesso filegroup dell'indice.

[!NOTA]

Questo esempio può essere eseguito solo in SQL Server 2005 Enterprise Edition o versione successiva.

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

D. Eliminazione di un indice cluster in linea e spostamento della tabella in un nuovo filegroup

Nell'esempio seguente viene eliminato un indice cluster in linea 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.tables e sys.filegroups per verificare il posizionamento dell'indice e della tabella nei filegroup prima e dopo lo spostamento.

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. Eliminazione di un vincolo PRIMARY KEY in linea

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

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

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

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

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

G. Eliminazione di un indice cluster in una tabella FILESTREAM

Nell'esempio seguente viene eliminato un indice cluster in linea 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.

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

Vedere anche

Riferimento

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)

Concetti

Eliminazione di indici

Altre risorse

Individuazione dei requisiti di spazio su disco per gli indici