Dela via

DROPPINDEX (Transact-SQL)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-databas i Microsoft Fabric

Tar bort ett eller flera relations-, rumsliga, filtrerade eller XML-index från den aktuella databasen. Du kan ta bort ett klustrat index och flytta den resulterande tabellen till en annan filgrupp eller partitionsschema i en enda transaktion genom att ange alternativet MOVE TO .

Satsen DROP INDEX gäller inte index som skapas genom att definiera PRIMARY KEY eller UNIQUE begränsa dem. För att ta bort begränsningen och motsvarande index, använd ALTER TABLE med klausulen DROP CONSTRAINT .

Viktigt!

Syntaxen som definieras i <drop_backward_compatible_index> kommer att tas bort i en framtida version av SQL Server. Undvik att använda denna syntax i nytt utvecklingsarbete och planera att modifiera applikationer som för närvarande använder funktionen. Använd syntaxen som anges nedan <drop_relational_or_xml_or_spatial_index> istället. XML-index kan inte tas bort med bakåtkompatibel syntax.

Transact-SQL syntaxkonventioner

Syntax

Syntax för SQL Server (alla alternativ utom filgrupp och filström gäller för 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" } ]
}

Syntax för 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 }

Syntax för 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

OM DET FINNS

Gäller för: SQL Server 2016 (13.x) och senare versioner.

Indexet tas villkorligt bort endast om det redan existerar.

index_name

Namnet på indexet som ska tas bort.

database_name

Namnet på databasen.

schema_name

Namnet på schemat som tabellen eller vyn tillhör.

table_or_view_name

Namnet på tabellen eller vyn som är kopplad till indexet. Rumsliga index stöds endast på tabeller.

För att se detaljer om alla index i en databas, använd katalogvyn sys.indexes .

Azure SQL Database stöder det tredelade namnformatet: database_name.schema_name.object_name när database_name är den aktuella databasen, eller database_name är tempdb och object_name börjar med # eller ##.

<drop_clustered_index_option>

Gäller för: SQL Server 2008 (10.0.x) och senare versioner, SQL Database.

Kontrollerar klustrade indexalternativ. Dessa alternativ kan inte användas med andra indextyper.

MAXDOP = max_degree_of_parallelism

Gäller för: SQL Server 2008 (10.0.x) och senare versioner, SQL Database (endast prestandanivåer P2 och P3).

Åsidosätter konfigurationsalternativet max degree of parallelism under indexoperationen. För mer information, se Konfigurera maxgraden av parallellism (serverkonfigurationsalternativ). Använd MAXDOP för att begränsa antalet processorer som används i indexbyggoperationen. Maximalt är 64 processorer.

Viktigt!

MAXDOP är inte tillåtet för rumsliga index eller XML-index.

max_degree_of_parallelism kan vara något av följande värden.

Värde Description
1 Undertrycker parallell plangenerering
>1 Begränsar det maximala antalet processorer som används i en parallell indexbyggoperation till det angivna antalet
0 (standardinställning) Använder det faktiska antalet processorer eller färre baserat på den aktuella systemarbetsbelastningen

Mer information finns i Konfigurera parallella indexåtgärder.

Anmärkning

Parallella indexåtgärder är inte tillgängliga i varje version av SQL Server. En lista över funktioner som stöds av versionerna av SQL Server finns i Utgåvor och funktioner som stöds i SQL Server 2022.

ONLINE = PÅ | BORT

Gäller för: SQL Server 2008 (10.0.x) och senare versioner, Azure SQL Database.

Anger om underliggande tabeller och associerade index är tillgängliga för frågor och dataändringar under indexåtgärden. Standardvärdet är OFF.

  • ON: Långsiktiga bordlås hålls inte. Detta tillåter att frågor eller uppdateringar till den underliggande tabellen kan fortsätta.

  • OFF: Tabelllås tillämpas och tabellen är otillgänglig under indexoperationen.

Alternativet ONLINE kan bara specificeras när du tar bort klustrade index. Mer information finns i avsnittet Kommentarer.

Anmärkning

Onlineindexåtgärder är inte tillgängliga i varje version av SQL Server. En lista över funktioner som stöds av versionerna av SQL Server finns i Utgåvor och funktioner som stöds i SQL Server 2022.

FLYTTA TILL { partition_scheme_name ( column_name ) | filegroup_name | "default" }

gäller för: SQL Server 2008 (10.0.x) och senare versioner. SQL Database stöder "default" som filgruppsnamn.

Anger en plats för att flytta dataraderna som för närvarande finns på bladnivån i det klustrade indexet. Datan flyttas till den nya platsen i form av en heap. Du kan ange antingen ett partitionsschema eller filgrupp som den nya platsen, men partitionsschemat eller filgruppen måste redan finnas. MOVE TO är inte giltigt för indexerade vyer eller icke-grupperade index. Om ett partitionsschema eller filgrupp inte specificeras, finns den resulterande tabellen i samma partitionsschema eller filgrupp som definierades för det klustrade indexet.

Om ett klustrat index tas bort genom att använda MOVE TO, återuppbyggs alla icke-klustrade index i bastabellen, men de behåller sina ursprungliga filgrupper eller partitionsscheman. Om bastabellen flyttas till en annan filgrupp eller partitionsschema flyttas de icke-klustrade indexen inte för att sammanfalla med den nya platsen för bastabellen (heap). Därför, även om de icke-klustrade indexen tidigare var justerade med det klustrade indexet, kan de inte längre vara justerade med heapen. Mer information om partitionerad indexjustering finns i Partitionerade tabeller och index.

partition_scheme_name ( column_name )

Gäller för: SQL Server 2008 (10.0.x) och senare versioner, SQL Database.

Specificerar ett partitionsschema som plats för den resulterande tabellen. Partitionsschemat måste redan skapas genom att köra antingen CREATE PARTITION SCHEME eller ALTER PARTITION SCHEME. Om ingen plats anges och tabellen är uppdelad, inkluderas tabellen i samma partitionsschema som det befintliga klustrade indexet.

Kolumnnamnet i schemat är inte begränsat till kolumnerna i indexdefinitionen. Vilken kolumn som helst i bastabellen kan specificeras.

filegroup_name

gäller för: SQL Server 2008 (10.0.x) och senare versioner.

Anger en filgrupp som plats för den resulterande tabellen. Om ingen plats anges och tabellen inte är partitionerad, inkluderas den resulterande tabellen i samma filgrupp som det klustrade indexet. Filgruppen måste redan finnas.

[standard]

Specificerar standardplatsen för den resulterande tabellen.

Anmärkning

I det här sammanhanget default är det inte ett nyckelord. Det är en identifierare för standardfilgruppen och måste avgränsas, som i MOVE TO "default" eller MOVE TO [default]. Om "default" anges QUOTED_IDENTIFIER måste alternativet ställas ON in för den aktuella sessionen. Det här är standardinställningen. Mer information finns i SET QUOTED_IDENTIFIER.

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }

gäller för: SQL Server 2008 (10.0.x) och senare versioner.

Specificerar en plats för att flytta FILESTREAM-tabellen som för närvarande finns på bladnivån i det klustrade indexet. Datan flyttas till den nya platsen i form av en heap. Du kan ange antingen ett partitionsschema eller filgrupp som den nya platsen, men partitionsschemat eller filgruppen måste redan finnas. FILESTREAM ON är inte giltigt för indexerade vyer eller icke-grupperade index. Om ett partitionsschema inte specificeras finns datan i samma partitionsschema som definierades för det klustrade indexet.

partition_scheme_name

Specificerar ett partitionsschema för FILESTREAM-datan. Partitionsschemat måste redan skapas genom att köra antingen CREATE PARTITION SCHEME eller ALTER PARTITION SCHEME. Om ingen plats anges och tabellen är uppdelad, inkluderas tabellen i samma partitionsschema som det befintliga klustrade indexet.

Om du specificerar ett partitionschema för MOVE TO, måste du använda samma partitionsschema för FILESTREAM ON.

filestream_filegroup_name

Specificerar en FILESTREAM-filgrupp för FILESTREAM-data. Om ingen plats anges och tabellen inte är partitionerad, inkluderas datan i standardfilgruppen FILESTREAM.

[standard]

Specificerar standardplatsen för FILESTREAM-datan.

Anmärkning

I det här sammanhanget default är det inte ett nyckelord. Det är en identifierare för standardfilgruppen och måste avgränsas, som i MOVE TO "default" eller MOVE TO [default]. Om "default" anges måste alternativet QUOTED_IDENTIFIER vara ON för den aktuella sessionen. Det här är standardinställningen. Mer information finns i SET QUOTED_IDENTIFIER.

Anmärkningar

När ett icke-klustrat index tas bort tas indexdefinitionen bort från metadata och indexdatasidorna (B-trädet) tas bort från databasfilerna. När ett klustrat index tas bort tas indexdefinitionen bort från metadata och de datarader som lagrades på bladnivån i det klustrade indexet lagras i den resulterande oordnade tabellen, en heap. Allt utrymme som tidigare upptogs av indexet återvinns. Detta utrymme kan sedan användas för vilket databasobjekt som helst.

Anmärkning

I dokumentationen används termen B-träd vanligtvis som referens till index. I radlagringsindex implementerar databasmotorn ett B+-träd. Detta gäller inte för kolumnlagringsindex eller index i minnesoptimerade tabeller. Mer information finns i arkitekturen och designguiden för SQL Server och Azure SQL-index.

Ett index kan inte tas bort om filgruppen där det finns är offline eller inställt på skrivskyddat.

När det klustrade indexet för en indexerad vy tas bort, tas alla icke-klustrade index och autoskapade statistik på samma vy automatiskt bort. Manuellt skapade statistik släpps inte.

Syntaxen <table_or_view_name>.<index_name> bibehålls för bakåtkompatibilitet. Ett XML-index eller rumsligt index kan inte tas bort genom att använda bakåtkompatibel syntax.

Ibland tas index bort och skapas om för att omorganisera eller bygga om indexet, till exempel för att tillämpa ett nytt fyllnadsfaktorvärde eller för att omorganisera data efter en bulk-last. För att göra detta är användningen av ALTER INDEX mer effektiv, särskilt för klustrade index. ALTER INDEX REBUILD har optimeringar för att förhindra överhead för att bygga om de icke-klustrade indexen.

Uppskjuten avallokering

När index med 128 extents eller fler tas bort, skjuter databasmotorn upp de faktiska siddelningarna och deras tillhörande lås tills transaktionen har genomförts. Index tas bort i två separata faser: logiska och fysiska. I den logiska fasen markeras de befintliga allokeringsenheterna som används av indexet för avallokering och låses tills transaktionen genomförs. I den fysiska fasen tar en bakgrundsprocess bort sidorna som är markerade för överlämning. Detta innebär att det utrymme som frigörs DROP INDEX kanske inte är tillgängligt för nya tilldelningar omedelbart.

Om accelererad databasåterställning är aktiverad används de separata logiska och fysiska faserna oavsett antalet utsträckningar.

Använd alternativ med DROP INDEX

Du kan ställa in följande indexalternativ när du släpper ett klustrat index: MAXDOP, ONLINE, och MOVE TO.

Använd MOVE TO för att ta bort det klustrade indexet och flytta den resulterande tabellen till en annan filgrupp eller partitionsschema i en enda transaktion.

När du specificerar ONLINE = ON, blockeras inte frågor och ändringar i underliggande data och tillhörande icke-klustrade index av transaktionen DROP INDEX . Endast ett klustrat index kan läggas ut online åt gången. För en fullständig beskrivning av alternativetONLINE, se SKAPA INDEX.

Du kan inte lägga ner ett klustrat index online om indexet är inaktiverat i en vy, eller innehåller text, ntext, bild, varchar(max),nvarchar(max), varbinary(max) eller xml-kolumner i bladnivådataraderna.

Att använda och-alternativen ONLINE = ONMOVE TO kräver mer tillfälligt diskutrymme.

Efter att ett index har tagits bort visas den resulterande heapen i sys.indexes katalogvyn med NULL i kolumnen name . För att se tabellnamnet, gå med på sys.indexessys.tables .object_id För ett exempelfråga, se exempel D.

På multiprocessordatorer som kör SQL Server 2005 Enterprise edition eller senare kan man DROP INDEX använda fler processorer för att utföra skannings- och sorteringsoperationer kopplade till att ta bort det klustrade indexet, precis som andra frågor gör. Du kan manuellt konfigurera antalet processorer som används för att köra satsen DROP INDEX genom att ange MAXDOP indexalternativet. Mer information finns i Konfigurera parallella indexåtgärder.

När ett klustrat index tas bort behåller motsvarande heappartitioner sin datakomprimeringsinställning om inte partitioneringsschemat ändras. Om partitioneringsschemat ändras byggs alla partitioner om till ett okomprimerat tillstånd (DATA_COMPRESSION = NONE). För att ta bort ett klustrat index och ändra partitioneringsschemat krävs följande två steg:

  1. Släpp det klustrade indexet.

  2. Modifiera tabellen genom att använda ett ALTER TABLE ... REBUILD ... alternativ som specificerar komprimeringsalternativet.

När ett klustrat index tas bort OFFLINEtas endast de övre nivåerna av klustrade index bort; därför är operationen snabb. När ett klustrat index tas bort ONLINEbygger SQL Server om heapen två gånger, en gång för steg 1 och en gång för steg 2. Mer information om datakomprimering finns i Datakomprimering.

XML-index

Alternativ kan inte specificeras när du lägger in ett XML-index. Du kan inte heller använda syntaxen <table_or_view_name>.<index_name> . När ett primärt XML-index tas bort tas alla associerade sekundära XML-index automatiskt bort. Mer information finns i XML-index (SQL Server).

Spatiala index

Rumsliga index stöds endast på tabeller. När du släpper ett rumsligt index kan du inte ange några alternativ eller använda syntaxen <table_or_view_name>..<index_name> . Den korrekta syntaxen är följande:

DROP INDEX <spatial_index_name> ON <spatial_table_name>;

För mer information om rumsliga index, se Översikt av rumsliga index.

Permissions

Om du vill köra DROP INDEXkrävs minst ALTER behörighet på tabellen eller vyn. Denna behörighet ges som standard till sysadmin den fasta serverrollen och db_ddladmin de fasta db_owner databasrollerna.

Examples

Kodexemplen i den här artikeln använder AdventureWorks2025- eller AdventureWorksDW2025-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects.

A. Släpp ett index

Följande exempel tar bort indexet IX_ProductVendor_BusinessEntityID i ProductVendor tabellen i AdventureWorks2025-databasen.

DROP INDEX IX_ProductVendor_BusinessEntityID
    ON Purchasing.ProductVendor;
GO

B. Ta bort flera index

Följande exempel tar bort två index i en och samma transaktion i AdventureWorks2025-databasen.

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

C. Lägg upp ett klustrat index online och ställ in MAXDOP-alternativet

Följande exempel tar bort ett klustrat index med ONLINE alternativet satt till ON och MAXDOP satt till 8. Eftersom MOVE TO alternativet inte specificerades lagras den resulterande tabellen i samma filgrupp som indexet.

Gäller för: SQL Server 2008 (10.0.x) och senare versioner, SQL Database.

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

D. Släpp ett klustrat index online och flytta tabellen till en ny filgrupp

I följande exempel tas ett grupperat index bort online och den resulterande tabellen (heap) flyttas till filgruppens NewGroup med hjälp av MOVE TO-satsen. Katalogvyerna sys.indexes, sys.tablesoch sys.filegroups efterfrågas för att verifiera index- och tabellplaceringen i filgrupperna före och efter flytten. Från och med SQL Server 2016 (13.x) kan du använda syntaxen DROP INDEX IF EXISTS .

gäller för: SQL Server 2008 (10.0.x) och senare versioner.

--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. Släpp en PRIMÄRNYCKEL-begränsning online

Index som skapas som ett resultat av skapandet PRIMARY KEY av eller UNIQUE begränsningar kan inte tas bort genom att använda DROP INDEX. De släpps med uttalandet ALTER TABLE DROP CONSTRAINT . Mer information finns i ALTER TABLE.

Följande exempel tar bort ett klustrat index med en PRIMARY KEY begränsning genom att ta bort begränsningen. Tabellen ProductCostHistory har inga FOREIGN KEY begränsningar. Om det gjorde det, måste dessa begränsningar tas bort först.

-- 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. Släpp ett XML-index

Följande exempel lägger ner ett XML-index i ProductModel tabellen i AdventureWorks2025-databasen.

DROP INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel;

G. Lägg ett klustrat index på en FILESTREAM-tabell

Följande exempel tar bort ett klustrat index online och flyttar den resulterande tabellen (heapen) och FILESTREAM-data till MyPartitionScheme partitionsschemat genom att använda både klausulen MOVE TO och klausulen FILESTREAM ON .

gäller för: SQL Server 2008 (10.0.x) och senare versioner.

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

Relaterat innehåll

  • Last updated on