Udostępnij za pomocą

DROP INDEX (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w Microsoft Fabric

Usuwa jeden lub więcej indeksów relacyjnych, przestrzennych, filtrowanych lub XML z bieżącej bazy danych. Możesz usunąć klastrowany indeks i przenieść powstałą tabelę do innej grupy plików lub schematu partycji w ramach jednej transakcji, określając MOVE TO tę opcję.

To DROP INDEX stwierdzenie nie dotyczy indeksów utworzonych przez definiowanie PRIMARY KEY lub UNIQUE ograniczenia. Aby usunąć ograniczenie i odpowiadający mu indeks, użyj ALTER TABLE z klauzulą DROP CONSTRAINT .

Ważne

Składnia zdefiniowana w zostanie <drop_backward_compatible_index> usunięta w przyszłej wersji SQL Server. Unikaj stosowania tej składni w nowych projektach deweloperskich i planuj modyfikację aplikacji, które obecnie korzystają z tej funkcji. Zamiast tego użyj składni podanej poniżej <drop_relational_or_xml_or_spatial_index> . Indeksów XML nie można usunąć za pomocą składni kompatybilnej wstecznie.

Transact-SQL konwencje składni

Składnia

Składnia dla SQL Server (wszystkie opcje oprócz grupy plików i strumienia plików dotyczą 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" } ]
}

Składnia usługi 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 }

Składnia dla usług Azure Synapse Analytics i Analytics Platform System (PDW).

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

Arguments

JEŚLI ISTNIEJE

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje.

Warunkowo usuwa indeks tylko wtedy, gdy już istnieje.

index_name

Nazwa indeksu, który ma zostać usunięty.

database_name

Nazwa bazy danych.

schema_name

Nazwa schematu, do którego należy tabela lub widok.

table_or_view_name

Nazwa tabeli lub widoku powiązanego z indeksem. Indeksy przestrzenne są obsługiwane tylko w tabelach.

Aby zobaczyć szczegóły dotyczące wszystkich indeksów w bazie danych, użyj widoku katalogu sys.indexes .

Azure SQL Database obsługuje trzyczęściowy format nazwy: database_name.schema_name.object_name gdy database_name jest aktualną bazą danych, lub database_name is tempdb i object_name zaczyna się od # lub ##.

<drop_clustered_index_option>

Dotyczy do: SQL Server 2008 (10.0.x) i późniejszych wersji, SQL Database.

Steruje klasterami opcji indeksu. Te opcje nie mogą być używane z innymi typami indeksów.

MAXDOP = max_degree_of_parallelism

Dotyczy do: SQL Server 2008 (10.0.x) i nowszych wersji, SQL Database (tylko poziomy wydajności P2 i P3).

Nadpisuje max degree of parallelism opcję konfiguracji podczas operacji indeksu. Więcej informacji można znaleźć w artykule Konfiguruj maksymalny stopień równoległości (opcja konfiguracji serwera). Używa się MAXDOP do ograniczenia liczby procesorów używanych w operacji budowy indeksu. Maksymalna wartość to 64 procesory.

Ważne

MAXDOP nie jest dozwolony dla indeksów przestrzennych ani XML.

max_degree_of_parallelism może być jedną z następujących wartości.

Wartość Description
1 Tłumi generowanie planów równoległych
>1 Ogranicza maksymalną liczbę procesorów używanych w równoległej operacji budowy indeksu do określonej liczby
0 (ustawienie domyślne) Wykorzystuje rzeczywistą liczbę procesorów lub mniej, zależną od aktualnego obciążenia systemu

Aby uzyskać więcej informacji, zobacz Konfigurowanie operacji indeksu równoległego.

Uwaga / Notatka

Operacje indeksowania równoległego nie są dostępne w każdej wersji programu SQL Server. Aby uzyskać listę funkcji obsługiwanych przez wersje programu SQL Server, zobacz Editions i obsługiwane funkcje programu SQL Server 2022.

ONLINE = WŁĄCZONE | OD

Dotyczy do: SQL Server 2008 (10.0.x) i późniejszych wersji, Azure SQL Database.

Określa, czy tabele bazowe i skojarzone indeksy są dostępne dla zapytań i modyfikacji danych podczas operacji indeksu. Wartość domyślna to OFF.

  • ON: Długoterminowe blokady stołów nie są utrzymywane przez długi czas. Pozwala to na kontynuowanie zapytań lub aktualizacji tabeli bazowej.

  • OFF: Stosuje się blokady tabeli i tabela jest niedostępna podczas operacji indeksu.

Opcja ta ONLINE może być wybrana tylko wtedy, gdy usuwasz indeksy klastrowane. Aby uzyskać więcej informacji, zobacz sekcję Uwagi.

Uwaga / Notatka

Operacje indeksowania online nie są dostępne w każdej wersji programu SQL Server. Aby uzyskać listę funkcji obsługiwanych przez wersje programu SQL Server, zobacz Editions i obsługiwane funkcje programu SQL Server 2022.

PRZENIEŚ DO { partition_scheme_name ( column_name ) | filegroup_name | "domyślne" }

Dotyczy: SQL Server 2008 (10.0.x) i nowsze wersje. SQL Database obsługuje "default" nazwę grupy plików.

Określa lokalizację do przeniesienia wierszy danych, które obecnie znajdują się na poziomie liścia klastrowanego indeksu. Dane są przenoszone do nowej lokalizacji w formie sterty. Możesz określić schemat partycji lub grupę plików jako nową lokalizację, ale schemat partycji lub grupa plików musi już istnieć. MOVE TO nie jest prawidłowy dla indeksowanych widoków ani indeksów nieklastrowanych. Jeśli schemat partycji lub grupa plików nie jest określona, powstała tabela znajduje się w tym samym schemacie partycjonowania lub grupie plików, jak zdefiniowano dla klastrowanego indeksu.

Jeśli klastrowany indeks zostanie usunięty przez użycie MOVE TO, wszystkie nieklastrowane indeksy w bazowej tabeli są odbudowywane, ale pozostają w swoich oryginalnych grupach plików lub schematach partycji. Jeśli tabela bazowa zostanie przeniesiona do innej grupy plików lub schematu partycji, indeksy nieklastrowane nie są przesuwane tak, aby pokrywały się z nową lokalizacją tabeli bazowej (kopca). Dlatego nawet jeśli indeksy nieklastrowane były wcześniej wyrównane z indeksem klastrowanym, mogą już nie być wyrównane z kopicą. Aby uzyskać więcej informacji na temat wyrównania indeksu partycjonowanego, zobacz Partycjonowane tabele i indeksy.

partition_scheme_name ( column_name )

Dotyczy do: SQL Server 2008 (10.0.x) i późniejszych wersji, SQL Database.

Określa schemat partycji jako lokalizację dla powstałej tabeli. Schemat partycji musi być już utworzony, wykonując CREATE PARTITION SCHEME lub ALTER PARTITION SCHEME. Jeśli nie podano lokalizacji, a tabela jest podzielona na partycje, tabela jest uwzględniana w tym samym schemacie partycjonowania co istniejący indeks klastrowany.

Nazwa kolumny w schemacie nie jest ograniczona do kolumn w definicji indeksu. Można określić dowolną kolumnę w tabeli bazowej.

filegroup_name

Dotyczy: SQL Server 2008 (10.0.x) i nowsze wersje.

Określa grupę plików jako lokalizację dla powstałej tabeli. Jeśli nie podano lokalizacji i tabela nie jest podzielona na particje, powstała tabela jest uwzględniana w tej samej grupie plików co indeks klastrowany. Grupa plików musi już istnieć.

[domyślne]

Określa domyślną lokalizację dla powstałej tabeli.

Uwaga / Notatka

W tym kontekście default nie jest słowem kluczowym. Jest to identyfikator domyślnej grupy plików i musi być rozdzielany, tak jak w pliku MOVE TO "default" lub MOVE TO [default]. Jeśli "default" jest to określone, opcja musi być ustawiona QUOTED_IDENTIFIERON na bieżącą sesję. Jest to ustawienie domyślne. Aby uzyskać więcej informacji, zobacz SET QUOTED_IDENTIFIER.

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "domyślne" }

Dotyczy: SQL Server 2008 (10.0.x) i nowsze wersje.

Określa lokalizację do przeniesienia tabeli FILESTREAM, która obecnie znajduje się na poziomie liścia indeksu klastrowego. Dane są przenoszone do nowej lokalizacji w formie sterty. Możesz określić schemat partycji lub grupę plików jako nową lokalizację, ale schemat partycji lub grupa plików musi już istnieć. FILESTREAM ON nie jest prawidłowy dla indeksowanych widoków ani indeksów nieklastrowanych. Jeśli schemat partycjonowania nie jest określony, dane znajdują się w tym samym schemacie partycji, który został zdefiniowany dla klastrowanego indeksu.

partition_scheme_name

Określa schemat partycji dla danych FILESTREAM. Schemat partycji musi być już utworzony, wykonując CREATE PARTITION SCHEME lub ALTER PARTITION SCHEME. Jeśli nie podano lokalizacji, a tabela jest podzielona na partycje, tabela jest uwzględniana w tym samym schemacie partycjonowania co istniejący indeks klastrowany.

Jeśli określimy schemat partycjonowania dla MOVE TO, musisz użyć tego samego schematu partycji dla FILESTREAM ON.

filestream_filegroup_name

Określa grupę plików FILESTREAM dla danych FILESTREAM. Jeśli nie podano lokalizacji i tabela nie jest podzielona na partycjonowanie, dane są włączane do domyślnej grupy plików FILESTREAM.

[domyślne]

Określa domyślną lokalizację danych FILESTREAM.

Uwaga / Notatka

W tym kontekście default nie jest słowem kluczowym. Jest to identyfikator domyślnej grupy plików i musi być rozdzielany, tak jak w pliku MOVE TO "default" lub MOVE TO [default]. Jeśli "default" jest określona QUOTED_IDENTIFIER , opcja musi być ON dla bieżącej sesji. Jest to ustawienie domyślne. Aby uzyskać więcej informacji, zobacz SET QUOTED_IDENTIFIER.

Uwagi

Gdy indeks nieklastrowany zostaje usunięty, definicja indeksu jest usuwana z metadanych, a strony danych indeksu (drzewo B) są usuwane z plików bazy danych. Gdy indeks klastrowany zostaje usunięty, definicja indeksu jest usuwana z metadanych, a wiersze danych przechowywane na poziomie liścia klastrowanego indeksu są przechowywane w powstałej nieuporządkowanej tabeli, zwanej stertą. Cała przestrzeń wcześniej zajmowana przez indeks zostaje odzyskana. Ta przestrzeń może być następnie wykorzystana dla dowolnego obiektu bazy danych.

Uwaga / Notatka

W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach typu rowstore silnik bazy danych implementuje drzewo B+. Nie dotyczy to indeksów magazynu kolumn ani indeksów w tabelach zoptymalizowanych pod kątem pamięci. Aby uzyskać więcej informacji, zobacz architekturę i przewodnik projektowania indeksu SQL Server i Azure SQL.

Indeks nie może zostać usunięty, jeśli grupa plików, w której się znajduje, jest offline lub ustawiona na tylko do odczytu.

Gdy indeks klastrowany w widoku indeksowanym zostaje usunięty, wszystkie indeksy nieklastrowane oraz automatycznie utworzone statystyki w tym samym widoku są automatycznie usuwane. Ręcznie tworzone statystyki nie są usuwane.

Składnia <table_or_view_name>.<index_name> jest zachowana ze względu na kompatybilność wsteczną. Indeksu XML ani indeksu przestrzennego nie można usunąć przy użyciu składni kompatybilnej wstecznie.

Czasami indeksy są usuwane i odtwarzane na nowo, aby zreorganizować lub odbudować indeks, na przykład aby zastosować nową wartość współczynnika wypełnienia lub zorganizować dane po załadunku zbiorczym. Aby to osiągnąć, bardziej efektywne jest użycie ALTER INDEX , zwłaszcza dla indeksów klastrowych. ALTER INDEX REBUILD posiada optymalizacje zapobiegające narzutowi związanemu z przebudową indeksów nieklastrowanych.

Odroczona delokacja

Gdy indeksy o 128 lub więcej zakresach zostają usunięte, silnik bazy danych odkłada rzeczywiste lokalizacje stron i powiązane blokady do momentu zatwierdzenia transakcji. Indeksy są wyrzucane w dwóch oddzielnych fazach: logicznej i fizycznej. W fazie logicznej istniejące jednostki alokacji używane przez indeks są oznaczane do delokacji i blokowane do czasu zatwierdzenia transakcji. W fazie fizycznej proces w tle usuwa strony oznaczone do deallocation. Oznacza to, że przestrzeń uwolniona przez może DROP INDEX nie być od razu dostępna na nowe przydziały.

Jeśli przyspieszone odzyskiwanie bazy danych jest włączone, stosuje się oddzielne fazy logiczne i fizyczne, niezależnie od liczby zakresów.

Używaj opcji z DROP INDEX

Możesz ustawić następujące opcje indeksu po upuszczeniu klastrowanego indeksu: MAXDOP, ONLINE, oraz MOVE TO.

Użyj do MOVE TO usunięcia klastrowanego indeksu i przeniesienia powstałej tabeli do innej grupy plików lub schematu partycji w ramach jednej transakcji.

Gdy określasz ONLINE = ON, zapytania i modyfikacje danych bazowych oraz powiązanych indeksów nieklastrowanych nie są blokowane przez transakcję DROP INDEX . Tylko jeden indeks klastrowany może być jednocześnie umieszczony online. Pełny opis ONLINE opcji można znaleźć w CREATE INDEX.

Nie możesz wrzucić klastrowanego indeksu online, jeśli indeks jest wyłączony w widoku lub zawiera tekst, ntext, obraz, varchar(max),nvarchar(max), nvarchar(max),varbinary(max) lub kolumny xml w wierszach danych na poziomie liści.

Użycie ONLINE = ON opcji i MOVE TO wymaga więcej tymczasowej przestrzeni na dysku.

Po upuszczeniu indeksu, powstały stos pojawia się w widoku sys.indexes katalogu z w NULL kolumnie name . Aby zobaczyć nazwę tabeli, połącz sys.indexes z sys.tables on object_id. Przykładowe zapytanie można znaleźć w przykładzie D.

Na komputerach wieloprocesorowych działających na SQL Server 2005 Enterprise lub nowszej DROP INDEX mogą używać większej liczby procesorów do wykonywania operacji skanowania i sortowania związanych z usuwaniem klastrowanego indeksu, podobnie jak inne zapytania. Możesz ręcznie skonfigurować liczbę procesorów używanych do uruchamiania DROP INDEX instrukcji, wybierając opcję indeksu MAXDOP . Aby uzyskać więcej informacji, zobacz Konfigurowanie operacji indeksu równoległego.

Po usunięciu indeksu klastrowanego, partycje stert odpowiadające indeksowi zachowują ustawienie kompresji danych, chyba że schemat podziału na partycje zostanie zmodyfikowany. Jeśli schemat partycjonowania zostanie zmieniony, wszystkie partycje są odbudowywane do stanu nieskompresowanego (DATA_COMPRESSION = NONE). Aby usunąć klastrowany indeks i zmienić schemat partycjonowania, potrzebne są następujące dwa kroki:

  1. Upuść indeks klastrowany.

  2. Zmodyfikuj tabelę, używając ALTER TABLE ... REBUILD ... opcji określającej opcję kompresji.

Gdy indeks klastrowany zostaje usunięty OFFLINE, usuwane są tylko górne poziomy indeksów klastrowanych; dlatego operacja jest szybka. Gdy indeks klastrowany zostaje usunięty ONLINE, SQL Server odbudowuje stos dwukrotnie, raz dla kroku 1 i raz dla kroku 2. Aby uzyskać więcej informacji na temat kompresji danych, zobacz Kompresja danych.

Indeksy XML

Opcji nie można określić po upuszczeniu indeksu XML. Poza tym nie możesz użyć składni.<table_or_view_name>.<index_name> Gdy główny indeks XML zostaje usunięty, wszystkie powiązane wtórne indeksy XML są automatycznie usuwane. Aby uzyskać więcej informacji, zobacz Indeksy XML (SQL Server).

Indeksy przestrzenne

Indeksy przestrzenne są obsługiwane tylko w tabelach. Gdy upuszczasz indeks przestrzenny, nie możesz określić żadnych opcji ani użyć składni.<table_or_view_name>..<index_name> Poprawna składnia jest następująca:

DROP INDEX <spatial_index_name> ON <spatial_table_name>;

Więcej informacji o indeksach przestrzennych można znaleźć w artykule Przegląd indeksów przestrzennych.

Permissions

Aby wykonać DROP INDEX, wymagane jest co najmniej uprawnienie ALTER do tabeli lub widoku. To uprawnienia są domyślnie przyznawane stałej sysadmin roli serwera oraz db_ddladmindb_owner stałym rolom bazy danych.

Przykłady

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2025 lub AdventureWorksDW2025, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

A. Usuń indeks

Poniższy przykład usuwa indeks IX_ProductVendor_BusinessEntityID w tabeli ProductVendor w bazie AdventureWorks2025.

DROP INDEX IX_ProductVendor_BusinessEntityID
    ON Purchasing.ProductVendor;
GO

B. Usuń wiele indeksów

Poniższy przykład usuwa dwa indeksy w jednej transakcji w bazie danych AdventureWorks2025.

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

C. Wrzuć klasterizowany indeks online i ustaw opcję MAXDOP

Poniższy przykład usuwa klastrowany indeks z opcją ustawioną ONLINE na i ON ustawioną MAXDOP na .8 Ponieważ MOVE TO opcja nie została określona, powstała tabela jest przechowywana w tej samej grupie plików co indeks.

Dotyczy do: SQL Server 2008 (10.0.x) i późniejszych wersji, SQL Database.

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

D. Wrzuć klasterizowany indeks online i przenieś tabelę do nowej grupy plików

Poniższy przykład usuwa indeks klastrowany w trybie online i przenosi tabelę wynikową (stertę) do grupy plików NewGroup przy użyciu klauzuli MOVE TO. Zapytania dotyczące widoków wykazu sys.indexes, sys.tablesi sys.filegroups są sprawdzane w celu zweryfikowania położenia indeksu i tabeli w grupach plików przed przeniesieniem i po nim. Począwszy od SQL Server 2016 (13.x), możesz korzystać ze składni.DROP INDEX IF EXISTS

Dotyczy: SQL Server 2008 (10.0.x) i nowsze wersje.

--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. Usuń ograniczenie KLUCZA GŁÓWNEGO online

Indeksy utworzone w wyniku tworzenia PRIMARY KEY lub UNIQUE ograniczeń nie mogą być usunięte przez użycie DROP INDEX. Są odrzucani na podstawie oświadczenia ALTER TABLE DROP CONSTRAINT . Aby uzyskać więcej informacji, zobacz ALTER TABLE.

Poniższy przykład usuwa klastrowany indeks z ograniczeniem PRIMARY KEY poprzez jego rezygnację. Tabela nie ProductCostHistory ma FOREIGN KEY żadnych ograniczeń. Gdyby tak było, te ograniczenia musiałyby zostać najpierw usunięte.

-- 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. Usuń indeks XML

Poniższy przykład umieszcza indeks XML w tabeli ProductModel w bazie AdventureWorks2025.

DROP INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel;

G. Wrzuć klastrowany indeks na tabelę FILESTREAM

Poniższy przykład usuwa klastrowany indeks online i przenosi powstałą tabelę (kopcę) oraz dane FILESTREAM do schematu MyPartitionScheme partycji, używając zarówno klauzuli MOVE TO , jak i klauzuli FILESTREAM ON .

Dotyczy: SQL Server 2008 (10.0.x) i nowsze wersje.

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

Treści powiązane

  • Last updated on