Delen via

DROPINDEX (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database in Microsoft Fabric

Verwijdert één of meer relationele, ruimtelijke, gefilterde of XML-indexen uit de huidige database. Je kunt een geclusterde index verwijderen en de resulterende tabel in één transactie naar een andere bestandsgroep of partitieschema verplaatsen door de MOVE TO optie te specificeren.

De DROP INDEX instructie is niet van toepassing op indexen die zijn gemaakt door definiëring PRIMARY KEY of UNIQUE beperkingen. Om de beperking en de bijbehorende index te verwijderen, gebruik ALTER TABLE met de DROP CONSTRAINT clausule.

Belangrijk

De syntaxis die in <drop_backward_compatible_index> is gedefinieerd, zal in een toekomstige versie van SQL Server worden verwijderd. Vermijd het gebruik van deze syntaxis in nieuw ontwikkelingswerk en plan om applicaties aan te passen die de functie momenteel gebruiken. Gebruik in plaats daarvan de hieronder <drop_relational_or_xml_or_spatial_index> gespecificeerde syntaxis. XML-indexen kunnen niet worden verwijderd met backward compatible syntax.

Transact-SQL syntaxis-conventies

Syntaxis

Syntax voor SQL Server (alle opties behalve filegroup en filestream zijn van toepassing op Azure SQL Database).

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

Syntaxis voor Azure SQL Database.

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 }

Syntaxis voor Azure Synapse Analytics en Analytics Platform System (PDW).

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

Arguments

ALS BESTAAT

Van toepassing op: SQL Server 2016 (13.x) en latere versies.

Voorwaardelijk laat de index alleen vallen als deze al bestaat.

index_name

De naam van de index die weggelaten moet worden.

database_name

De naam van de database.

schema_name

De naam van het schema waartoe de tabel of weergave behoort.

table_or_view_name

De naam van de tabel of weergave die bij de index hoort. Ruimtelijke indexen worden alleen op tabellen ondersteund.

Om de details van alle indexen in een database te zien, gebruik de katalogusweergave sys.indexes .

Azure SQL Database ondersteunt het driedelige naamformaat: database_name.schema_name.object_name wanneer database_name is de huidige database, of de database_name is tempdb en object_name begint met # of ##.

<drop_clustered_index_option>

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, SQL Database.

Beheert geclusterde indexopties. Deze opties kunnen niet worden gebruikt met andere indextypes.

MAXDOP = max_degree_of_parallelism

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, SQL Database (alleen prestatieniveaus P2 en P3).

Overschrijft de max degree of parallelism configuratieoptie tijdens de indexbewerking. Voor meer informatie, zie Configureer de maximale mate van parallelisme (serverconfiguratie-optie). Gebruik MAXDOP om het aantal processors dat wordt gebruikt in de indexbouwoperatie te beperken. Het maximum is 64 processoren.

Belangrijk

MAXDOP is niet toegestaan voor ruimtelijke indexen of XML-indexen.

max_degree_of_parallelism kan een van de volgende waarden zijn.

Waarde Description
1 Onderbreekt parallelle plangeneratie
>1 Beperkt het maximale aantal processors dat in een parallelle indexbouwoperatie wordt gebruikt tot het gespecificeerde aantal
0 (standaard) Gebruikt het werkelijke aantal processors of minder op basis van de huidige systeemwerklast

Zie Parallelle indexbewerkingen configurerenvoor meer informatie.

Opmerking

Parallelle indexbewerkingen zijn niet beschikbaar in elke editie van SQL Server. Zie -edities en ondersteunde functies van SQL Server 2022voor een lijst met functies die worden ondersteund door de edities van SQL Server.

ONLINE = OP | AF

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, Azure SQL Database.

Hiermee geeft u op of onderliggende tabellen en bijbehorende indexen beschikbaar zijn voor query's en gegevenswijziging tijdens de indexbewerking. De standaardwaarde is OFF.

  • ON: Langdurige tafelsloten worden niet vastgehouden. Dit maakt het mogelijk om queries of updates naar de onderliggende tabel voort te zetten.

  • OFF: Tabelvergrendelingen worden toegepast en de tabel is niet beschikbaar tijdens de indexoperatie.

De ONLINE optie kan alleen worden gespecificeerd als je geclusterde indexen weglaat. Zie de sectie Opmerkingen voor meer informatie.

Opmerking

Online indexbewerkingen zijn niet beschikbaar in elke editie van SQL Server. Zie -edities en ondersteunde functies van SQL Server 2022voor een lijst met functies die worden ondersteund door de edities van SQL Server.

VERPLAATS NAAR { partition_scheme_name ( column_name ) | filegroup_name | "default" }

van toepassing op: SQL Server 2008 (10.0.x) en latere versies. SQL Database ondersteunt "default" als bestandsgroepnaam.

Geeft een locatie aan om de datarijen te verplaatsen die zich momenteel op bladniveau van de geclusterde index bevinden. De gegevens worden in de vorm van een heap naar de nieuwe locatie verplaatst. Je kunt een partitieschema of bestandsgroep als nieuwe locatie specificeren, maar het partitieschema of de bestandsgroep moet al bestaan. MOVE TO is niet geldig voor geïndexeerde weergaven of niet-geclusterde indexen. Als een partitieschema of bestandsgroep niet is gespecificeerd, bevindt de resulterende tabel zich in hetzelfde partitieschema of bestandsgroep als gedefinieerd voor de geclusterde index.

Als een geclusterde index wordt verwijderd door gebruik te maken MOVE TOvan , worden alle niet-geclusterde indexen in de basistabel opnieuw opgebouwd, maar ze blijven in hun oorspronkelijke bestandsgroepen of partitieschema's. Als de basistabel naar een andere bestandsgroep of partitieschema wordt verplaatst, worden de niet-geclusterde indexen niet verplaatst om samen te vallen met de nieuwe locatie van de basistabel (heap). Daarom, zelfs als de niet-geclusterde indexen eerder uitgelijnd waren met de geclusterde index, kunnen ze niet langer uitgelijnd zijn met de heap. Zie Gepartitioneerde tabellen en indexen voor meer informatie over gepartitioneerde indexuitlijning.

partition_scheme_name ( column_name )

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, SQL Database.

Specificeert een partitieschema als locatie voor de resulterende tabel. Het partitieschema moet al zijn aangemaakt door CREATE PARTITION SCHEME of ALTER PARTITION SCHEME uit te voeren. Als er geen locatie is opgegeven en de tabel is gepartitioneerd, wordt de tabel opgenomen in hetzelfde partitieschema als de bestaande geclusterde index.

De kolomnaam in het schema is niet beperkt tot de kolommen in de indexdefinitie. Elke kolom in de basistabel kan worden gespecificeerd.

filegroup_name

van toepassing op: SQL Server 2008 (10.0.x) en latere versies.

Specificeert een bestandsgroep als locatie voor de resulterende tabel. Als er geen locatie is opgegeven en de tabel niet is gepartitioneerd, wordt de resulterende tabel opgenomen in dezelfde bestandsgroep als de geclusterde index. De bestandsgroep moet al bestaan.

[standaard]

Specificeert de standaardlocatie voor de resulterende tabel.

Opmerking

In deze context default is dit geen trefwoord. Het is een id voor de standaardbestandsgroep en moet worden gescheiden, zoals in MOVE TO "default" of MOVE TO [default]. Als "default" gespecificeerd is, moet de QUOTED_IDENTIFIER optie worden ingesteld ON voor de huidige sessie. Dit is de standaardinstelling. Zie SET QUOTED_IDENTIFIERvoor meer informatie.

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }

van toepassing op: SQL Server 2008 (10.0.x) en latere versies.

Geeft een locatie aan om de FILESTREAM-tabel te verplaatsen die zich momenteel op bladniveau van de geclusterde index bevindt. De gegevens worden in de vorm van een heap naar de nieuwe locatie verplaatst. Je kunt een partitieschema of bestandsgroep als nieuwe locatie specificeren, maar het partitieschema of de bestandsgroep moet al bestaan. FILESTREAM ON is niet geldig voor geïndexeerde weergaven of niet-geclusterde indexen. Als er geen partitieschema is gespecificeerd, bevindt de data zich in hetzelfde partitieschema als gedefinieerd voor de geclusterde index.

partition_scheme_name

Specificeert een partitieschema voor de FILESTREAM-gegevens. Het partitieschema moet al zijn aangemaakt door CREATE PARTITION SCHEME of ALTER PARTITION SCHEME uit te voeren. Als er geen locatie is opgegeven en de tabel is gepartitioneerd, wordt de tabel opgenomen in hetzelfde partitieschema als de bestaande geclusterde index.

Als je een partitieschema specificeert voor MOVE TO, moet je hetzelfde partitieschema gebruiken voor FILESTREAM ON.

filestream_filegroup_name

Specificeert een FILESTREAM-bestandsgroep voor FILESTREAM-gegevens. Als er geen locatie is opgegeven en de tabel niet is gepartitioneerd, wordt de data opgenomen in de standaard FILESTREAM-bestandsgroep.

[standaard]

Specificeert de standaardlocatie voor de FILESTREAM-gegevens.

Opmerking

In deze context default is dit geen trefwoord. Het is een id voor de standaardbestandsgroep en moet worden gescheiden, zoals in MOVE TO "default" of MOVE TO [default]. Als "default" dit is opgegeven, moet de QUOTED_IDENTIFIER optie voor de huidige sessie zijn ON . Dit is de standaardinstelling. Zie SET QUOTED_IDENTIFIERvoor meer informatie.

Opmerkingen

Wanneer een niet-geclusterde index wordt verwijderd, wordt de indexdefinitie uit de metadata verwijderd en worden de indexdatapagina's (de B-boom) uit de databasebestanden verwijderd. Wanneer een geclusterde index wordt weggelaten, wordt de indexdefinitie uit de metadata verwijderd en worden de datarijen die op bladniveau van de geclusterde index waren opgeslagen, opgeslagen in de resulterende ongeordende tabel, een heap. Alle ruimte die eerder door de index werd ingenomen, wordt teruggewonnen. Deze ruimte kan vervolgens worden gebruikt voor elk databaseobject.

Opmerking

Documentatie maakt gebruik van de term B-tree in het algemeen in verwijzing naar indexen. In rowstore-indexen implementeert de Database Engine een B+ tree. Dit geldt niet voor columnstore-indexen of indexen voor tabellen die zijn geoptimaliseerd voor geheugen. Zie de SQL Server- en Azure SQL-indexarchitectuur en ontwerphandleidingvoor meer informatie.

Een index kan niet worden verwijderd als de bestandsgroep waarin deze zich bevindt offline is of op alleen-lezen staat.

Wanneer de geclusterde index van een geïndexeerde weergave wordt verwijderd, worden alle niet-geclusterde indexen en automatisch gemaakte statistieken op dezelfde weergave automatisch verwijderd. Handmatig gemaakte statistieken worden niet weggelaten.

De syntaxis <table_or_view_name>.<index_name> wordt behouden voor achterwaartse compatibiliteit. Een XML-index of ruimtelijke index kan niet worden weggelaten door de backward compatible syntax te gebruiken.

Soms worden indexen verwijderd en opnieuw aangemaakt om de index te reorganiseren of opnieuw op te bouwen, bijvoorbeeld om een nieuwe vulfactor toe te passen of om data te reorganiseren na een bulkbelasting. Om dit te doen is het gebruik van ALTER INDEX efficiënter, vooral voor geclusterde indexen. ALTER INDEX REBUILD heeft optimalisaties om de overhead van het herbouwen van de niet-geclusterde indexen te voorkomen.

Uitgestelde deallocatie

Wanneer indexen met 128 extents of meer worden verwijderd, stelt de Database Engine de daadwerkelijke pagina-deallocations en bijbehorende sloten uit tot na de transactie die is gecommend. Indexen worden in twee afzonderlijke fasen gedropt: logisch en fysiek. In de logische fase worden de bestaande allocatie-eenheden die door de index worden gebruikt gemarkeerd voor deallocatie en vergrendeld totdat de transactie wordt gecomm. In de fysieke fase verwijdert een achtergrondproces de pagina's die voor deallocatie zijn gemarkeerd. Dit betekent dat de ruimte die door DROP INDEX wordt vrijgegeven, mogelijk niet direct beschikbaar is voor nieuwe toewijzingen.

Als versnelde databaseherstel is ingeschakeld, worden de afzonderlijke logische en fysieke fasen gebruikt, ongeacht het aantal extents.

Gebruik opties met DROP INDEX

Je kunt de volgende indexopties instellen wanneer je een geclusterde index laat vallen: MAXDOP, ONLINE, en MOVE TO.

Gebruik MOVE TO om de geclusterde index te verwijderen en de resulterende tabel in één transactie naar een andere bestandsgroep of partitieschema te verplaatsen.

Wanneer je specificeert ONLINE = ON, worden queries en aanpassingen aan de onderliggende data en bijbehorende niet-geclusterde indexen niet geblokkeerd door de DROP INDEX transactie. Er kan maar één geclusterde index tegelijk online worden gezet. Voor een volledige beschrijving van de ONLINE optie, zie CREATE INDEX.

Je kunt geen geclusterde index online zetten als de index is uitgeschakeld in een weergave, of tekst, ntext, afbeelding, varchar(max), nvarchar(max), varbinary(max) of xml-kolommen bevat in de bladniveau-datalijnen.

Het gebruik van de ONLINE = ON en-opties MOVE TO vereist meer tijdelijke schijfruimte.

Nadat een index is verwijderd, verschijnt de resulterende heap in de sys.indexes catalogusweergave met NULL in de name kolom. Om de tabelnaam te bekijken, sluit je aan sys.indexes met sys.tables op object_id. Voor een voorbeeldzoekopdracht, zie voorbeeld D.

Op multiprocessorcomputers die SQL Server 2005 Enterprise edition of later draaien, DROP INDEX kunnen meer processors worden gebruikt om de scan- en sorteerbewerkingen uit te voeren die horen bij het verwijderen van de geclusterde index, net als andere queries. Je kunt handmatig het aantal processors configureren dat gebruikt wordt om de DROP INDEX instructie uit te voeren door de MAXDOP indexoptie te specificeren. Zie Parallelle indexbewerkingen configurerenvoor meer informatie.

Wanneer een geclusterde index wordt verwijderd, behouden de bijbehorende heap-partities hun instelling voor gegevenscompressie, tenzij het partitioneringsschema wordt gewijzigd. Als het partitioneringsschema wordt gewijzigd, worden alle partities opnieuw opgebouwd naar een ongecomprimeerde toestand (DATA_COMPRESSION = NONE). Om een geclusterde index te verwijderen en het partitioneringsschema te wijzigen, zijn de volgende twee stappen nodig:

  1. Verwijder de geclusterde index.

  2. Wijzig de tabel door een ALTER TABLE ... REBUILD ... optie te gebruiken die de compressieoptie specificeert.

Wanneer een geclusterde index wordt weggelaten OFFLINE, worden alleen de bovenste niveaus van geclusterde indexen verwijderd; daarom is de operatie snel. Wanneer een geclusterde index wordt verwijderd ONLINE, bouwt SQL Server de heap twee keer opnieuw op, eenmaal voor stap 1 en eenmaal voor stap 2. Zie Gegevenscompressie voor meer informatie over gegevenscompressie.

XML-indexen

Opties kunnen niet worden gespecificeerd wanneer je een XML-index toevoegt. Ook kun je de <table_or_view_name>.<index_name> syntaxis niet gebruiken. Wanneer een primaire XML-index wordt verwijderd, worden alle bijbehorende secundaire XML-indexen automatisch verwijderd. Zie XML-indexen (SQL Server) voor meer informatie.

Ruimtelijke indexen

Ruimtelijke indexen worden alleen op tabellen ondersteund. Wanneer je een ruimtelijke index toevoegt, kun je geen opties specificeren of de syntaxis <table_or_view_name>..<index_name> gebruiken. De correcte syntaxis is als volgt:

DROP INDEX <spatial_index_name> ON <spatial_table_name>;

Voor meer informatie over ruimtelijke indexen, zie Overzicht van ruimtelijke indexen.

Permissions

Voor het uitvoeren van DROP INDEXis minimaal ALTER machtiging voor de tabel of weergave vereist. Deze toestemming wordt standaard verleend aan de sysadmin vaste serverrol en de db_ddladmin vaste db_owner databaserollen.

Voorbeelden

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2025 of AdventureWorksDW2025 voorbeelddatabase die u kunt downloaden van de startpagina van Microsoft SQL Server Samples en Community Projects .

Eén. Plaats een index

Het volgende voorbeeld verwijdert de index IX_ProductVendor_BusinessEntityID in de ProductVendor tabel in de AdventureWorks2025-database.

DROP INDEX IX_ProductVendor_BusinessEntityID
    ON Purchasing.ProductVendor;
GO

B. Verwijder meerdere indexen

Het volgende voorbeeld verwijdert twee indexen in één transactie in de AdventureWorks2025-database.

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

C. Zet een geclusterde index online en stel de MAXDOP-optie in

Het volgende voorbeeld verwijdert een geclusterde index met de ONLINE optie ingesteld op ON en MAXDOP gezet op 8. Omdat de MOVE TO optie niet was gespecificeerd, wordt de resulterende tabel opgeslagen in dezelfde bestandsgroep als de index.

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, SQL Database.

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

D. Zet een geclusterde index online en verplaats de tabel naar een nieuwe bestandsgroep

In het volgende voorbeeld wordt een geclusterde index online verwijderd en wordt de resulterende tabel (heap) verplaatst naar de bestandsgroep NewGroup met behulp van de MOVE TO-component. De sys.indexes-, sys.tables- en sys.filegroups catalogusweergaven worden opgevraagd om de plaatsing van de index en tabel in de bestandsgroepen vóór en na de verplaatsing te controleren. Vanaf SQL Server 2016 (13.x) kun je de DROP INDEX IF EXISTS syntaxis gebruiken.

van toepassing op: SQL Server 2008 (10.0.x) en latere versies.

--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. Verwijder online een PRIMAIRE SLEUTEL-beperking

Indexen die worden aangemaakt als gevolg van het creëren PRIMARY KEY van of UNIQUE constraints kunnen niet worden verwijderd door gebruik te maken DROP INDEXvan . Ze worden met de ALTER TABLE DROP CONSTRAINT verklaring gedropt. Zie ALTER TABLEvoor meer informatie.

Het volgende voorbeeld verwijdert een geclusterde index met een PRIMARY KEY beperking door de beperking weg te laten. De ProductCostHistory tabel heeft geen FOREIGN KEY beperkingen. Als dat zo was, zouden die beperkingen eerst verwijderd moeten worden.

-- 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. Plaats een XML-index

Het volgende voorbeeld plaatst een XML-index in de ProductModel tabel in de AdventureWorks2025-database.

DROP INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel;

G. Plaats een geclusterde index op een FILESTREAM-tabel

Het volgende voorbeeld verwijdert een geclusterde index online en verplaatst de resulterende tabel (heap) en FILESTREAM-gegevens naar het MyPartitionScheme partitieschema door zowel de MOVE TO clausule als de FILESTREAM ON clausule te gebruiken.

van toepassing op: SQL Server 2008 (10.0.x) en latere versies.

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

Verwante inhoud

  • Last updated on