Как удалить индекс sql

SQL-Ex blog

Удалить сразу все избыточные индексы в каждой базе данных

Добавил Sergey Moiseenko on Среда, 5 июля. 2023

Избыточные индексы в SQL Server — это явление значительно более общее, чем мне бы хотелось. Я встречал это довольно часто. Это означает, что данное сообщение в блоге все еще будет иметь значительную целевую аудиторию!

Статья Brent Ozar дает исчерпывающую информацию об избыточных/дублирующих индексах, что они означают, почему это плохо, и что нужно с этим делать.

Несколько лет назад Guy Glantser также опубликовал статью об удалении избыточных индексов. Она весьма полезна для нахождения всех избыточных индексов во всех таблицах в заданной базе данных.

Но вот чего не хватает в этих статьях, так это возможности легко генерировать команды Drop/Disable для этих избыточных индексов.

Кроме того, что если имеются «похожие» индексы, которые только «частично» избыточны, и, следовательно, недостаточно просто удалить один из них? Иначе это может негативно сказаться на производительности некоторых запросов.

Есть ли способ учесть все эти проблемы?

Каждый из них? Повсюду? Сразу?

• Полностью дублируемые индексы
• Избыточные индексы на основе ключевых столбцов + включенных столбцов
• Частично избыточные индексы только на основе ключевых столбцов
• Все таблицы
• Все таблицы с минимальным числом строк
• В конкретной базе данных
• Во всех доступных базах данных

ВАЖНОЕ ЗАМЕЧАНИЕ:
Важно отметить, что вам следует удалять только действительно избыточные индексы. Перед удалением индекса следует убедиться, что он явно не используется какими-либо запросами или хранимыми процедурами в вашей базе данных посредством табличного хинта или хинта запроса.

Давайте поговорим о случаях использования

Есть несколько разных случаев использования, когда будут иметь место избыточные индексы.

• Ключевые столбцы
• Включенные столбцы
• Определение фильтрации
• Другие свойства (сжатие данных, опции блокировки индекса, коэффициент заполнения, файловая группа)

• Если два индекса имеют различные определения фильтра, они НЕ являются дубликатами или в чем-то избыточными относительно друг друга.
• Кластеризованные индексы никогда не считаются избыточными, даже если их ключевые столбцы «содержатся» внутри другого индекса.
• Первичные и уникальные ключи никогда не считаются избыточными, даже если их ключевые столбцы «содержатся» внутри другого индекса.
• Некоторые разнообразные свойства индекса игнорируются при этой проверке (сжатие данных, опции блокировки индекса, коэффициент заполнения, файловая группа).

Полностью дублируемый индекс

Полностью дублируемым индексом считается такой, у которого идентичны все ключевые и все включенные столбцы.

Пример:

• ProductID (int, Primary Key)
• ProductName (nvarchar(50))
• CategoryID (int)
• Price (money)

CREATE NONCLUSTERED INDEX IX_Products_CategoryID 
ON Products(CategoryID) 
INCLUDE(ProductName, Price);

CREATE NONCLUSTERED INDEX IX_Products_CategoryID_2 
ON Products(CategoryID) 
INCLUDE(ProductName, Price);

Оба индекса имеют один и тот же ключевой столбец CategoryID и одинаковые включенные столбцы — ProductName и Price. Следовательно индекс 2 является полным дубликатом индекса 1.

Исправление:
В данном случае мы можем удалить индекс 2, поскольку он дублирует индекс 1 и не дает никаких дополнительных преимуществ. Удаление избыточного индекса может помочь улучшить производительность запросов и уменьшить использование места в хранилище.

Полностью покрывающий избыточный индекс

Полностью покрывающий индекс будет избыточным, если все его ключевые столбцы являются также первыми ключевыми столбцам другого индекса. Другой (содержащий) индекс может потенциально иметь дополнительные ключевые столбцы и/или может иметь больше включенных столбцов, помимо столбцов в избыточном индексе.

Важно, что содержащий индекс имеет все, что есть в избыточном индексе, и, возможно, что-то дополнительно.

Пример:

• OrderID (int, Primary Key)
• OrderDate (datetime)
• CustomerID (int)
• EmployeeID (int)
• ProductID (int)
• Quantity (int)
• TotalPrice (money)

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID 
ON Orders(CustomerID) 
INCLUDE(OrderDate, TotalPrice);

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_EmployeeID 
ON Orders(CustomerID, EmployeeID) 
INCLUDE(OrderDate, ProductID, Quantity, TotalPrice);

В этом случае индекс 2 включает все столбцы в индексе 1 и содержит дополнительный ключевой столбец EmployeeID. Кроме того, индекс 2 содержит дополнительные включенные столбцы ProductID и Quantity. Следовательно, индекс 2 является более объемлющим, чем индекс 1, и покрывает все запросы, которые покрывает индекс 1, плюс дополнительные запросы. Индекс 1 является избыточным в данном сценарии, поскольку он не дает дополнительного преимущества.

Исправление:

Мы можем удалить индекс 1 без влияния на производительность любых запросов.

Частично избыточные индексы

Частично избыточный индекс — это индекс, у которого ключевые столбцы являются также первыми ключевыми столбцами другого индекса. Но включенные столбцы могут полностью различаться. Это означает, что содержащий индекс способен выполнять ту же операцию поиска (seek), что и избыточный индекс, но может потребоваться добавить в него несколько включенных столбцов, чтобы полностью обеспечить аналогичную функциональность. В противном случае, индекс либо не будет использоваться, либо потребуется дополнительная операция поиска ключа (key lookup).

Пример:

• CustomerID (int, Primary Key)
• FirstName (nvarchar(50))
• LastName (nvarchar(50))
• City (nvarchar(50))
• State (nvarchar(50))
• ZipCode (nvarchar(10))
• Email (nvarchar(100))
• Phone (nvarchar(20))

CREATE NONCLUSTERED INDEX IX_Customers_City_State_ZipCode 
ON Customers(City, State, ZipCode) 
INCLUDE(FirstName, LastName);

CREATE NONCLUSTERED INDEX IX_Customers_City 
ON Customers(City) 
INCLUDE(FirstName, LastName, Email, Phone);

В этом случае индекс 2 содержит ключевой столбец City, который также является первым ключевым столбцом в индексе 1. Однако индекс 2 имеет дополнительные включенные столбцы Email и Phone, которые не включены в индекс 1.

Хотя индекс 2 покрывает некоторые дополнительные запросы, которые не покрывает индекс 1, он является частично избыточным, поскольку ключевой столбец City уже покрывается индексом 1. Это означает, что индекс 1 уже может выполнять те же операции поиска, что и индекс 2, при условии, что запросы выполняют фильтрацию только по столбцу City.

Исправления:

Чтобы полностью обеспечить функциональность индекса 2, мы должны дополнительно добавить в индекс 1 включенные столбцы Email и Phone. Это устранит необходимость в избыточном индексе 2 и будет гарантировать оптимальное использование индекса для запросов, которые выполняют фильтрацию по столбцу City.

Обновленный синтаксис для индекса 1:

CREATE NONCLUSTERED INDEX IX_Customers_City_State_ZipCode 
ON Customers(City, State, ZipCode) 
INCLUDE(FirstName, LastName, Email, Phone);

Удаляя избыточный индекс и обновляя содержащий индекс дополнительными включенными столбцами, мы можем уменьшить использование пространства хранилища и сделать базу данных более эффективной.

Иногда при попытке создать «все покрывающий» индекс для замены всех его избыточных/частично-избыточных индексов вы рискуете создать «широкие» индексы, которые имеют очень длинный ключ или списки включенных столбцов.

Хотя это все еще «выгодно» с точки зрения использования пространства хранилища сравнительно с присутствием избыточных индексов, имеется риск падения производительности. Это обусловлено тем, что чем больше столбцов находится в индексе, тем больше данных требуется сканировать, читать и парсить во время операций поиска или сканирования индекса.

Поэтому будьте осторожны, чтобы не получить огромные списки столбцов в ваших индексах, иначе вы можете ухудшить производительность некоторых запросов.

Похоже, что тут много работы

Вы правы, работы много. Но, к счастью, возможно «автоматизировать» ее большую часть, написав скрипт T-SQL!

Используйте этот скрипт из нашего Madeira Toolbox:

По умолчанию этот скрипт возвращает два различных результирующих набора.

Первый — это «подробный» результирующий набор, показывающий все избыточные индексы и индексы, которые их «содержат». Он имеет множество полезных деталей, но, возможно, будет включать дублирующуюся информацию (если тот же самый индекс «содержится» более чем в одном индексе).

Второй результирующий набор является «сводным», он показывает только избыточные индексы, и каждый избыточный индекс показан только один раз. Информация не дублируется, но некоторые детали опущены (которые можно найти в первом результирующем наборе). Вы можете использовать этот второй набор как единственный источник информации об избыточных индексах, которые должны быть удалены/отключены.

Обратите внимание на столбцы «*_index_seeks», «*_index_scans» и «*_index_updates», чтобы получить представление о «популярности» избыточных индексов по сравнению с их содержащими конкурентами.

Также примите к сведению, что столбец «redundant_index_pages» указывает на текущий размер индекса. Поделите это числа на 128, чтобы получить эквивалентный размер в Мб (деление числа страниц на 128 эквивалентно умножению на 8 для получения значения в Кб, а затем деления на 1024, чтобы получить значение в Мб). Замечание. Во втором (суммарном) результирующем наборе redundant_index_mb уже вычислен для вас.

Столбец «DisableCmd» может использоваться для быстрого получения соответствующих команд ALTER INDEX .. DISABLE для выключения избыточных индексов. Вы можете также использовать столбец «DisableIfActiveCmd» для «идемпотентной» альтернативы (отключает индекс, только если он существует включен).

Столбец «DropCmd» может использоваться для быстрого получения соответствующих команд DROP INDEX для удаления избыточных индексов. Есть также идемпотентная альтернатива (удаляет индекс, только если он существует).

• Вместо обнаружения избыточных индексов путем сравнения как ключевых столбцов, так и включенных, скрипт будет сравнивать только ключевые столбцы. Вероятно, это вернет даже больше «избыточных» индексов, но вам следует быть осторожным, т.к. эти индексы не обязательно «полностью» избыточные.
• Будет выводиться 3-й результирующий набор именно для этой цели.
  • Он содержит столбец «ExpandIndexCommand», который будет содержать команды CREATE INDEX для «полностью покрывающих индексов», чтобы заместить все избыточные индексы на базе ключевых столбцов. Другими словами, это предполагает индекс с большинством ключевых столбцов, и добавлением в него всех включенных столбцов из всех избыточных конкурентов.
  • Этот 3-й результирующий набор также содержит два дополнительных столбца «DisableRedundantIndexes» и «DropRedundantIndexes», которые могут использоваться, чтобы легче было избавиться от всех избыточных индексов, которые могут быть заменены новым покрывающим индексом.

  С этой целью вы можете использовать следующий скрипт:

  Кроме того, независимо от того, используется ли индекс в силу явного хинта, или просто потому, что оптимизатор SQL выбрал его, все равно полезно знать, что имеются запросы, которые используют определенный индекс, прежде чем вы решите удалить его.

  Используйте запрос ниже, чтобы обнаружить использование индекса в кэше планов SQL:

  DECLARE 
  @IndexName sysname = 'UQ_CountryCodes' 
  ,@TableName sysname = 'CountryCodes' 
  DECLARE 
  @IndexNameWithBrackets sysname = QUOTENAME(@IndexName) 
  ,@TableNameWithBrackets sysname = QUOTENAME(@TableName) 
  ;WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan', N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p) 
  SELECT 
  cp.objtype, 
  cp.usecounts, 
  st.text, 
  qp.query_plan 
  FROM sys.dm_exec_cached_plans cp 
  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st 
  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 
  WHERE st.text LIKE '%' + @TableName + '%' 
  AND qp.query_plan.exist('//Object[@Index=sql:variable("@IndexNameWithBrackets") and @Table=sql:variable("@TableNameWithBrackets")]') = 1

  Аналогично запрос ниже может использоваться для обнаружения использования индекса в хранилище запросов (Query Store):

  DECLARE 
  @IndexName sysname = 'UQ_CountryCodes' 
  ,@TableName sysname = 'CountryCodes' 
  DECLARE 
  @IndexNameWithBrackets sysname = QUOTENAME(@IndexName) 
  ,@TableNameWithBrackets sysname = QUOTENAME(@TableName) 
  ;WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan', N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p) 
  SELECT 
  q.query_id, 
  q.query_text_id, 
  qt.query_sql_text, 
  qp.query_plan_xml 
  FROM sys.query_store_query AS q 
  INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id 
  INNER JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id 
  OUTER APPLY (SELECT CAST(p.query_plan AS XML) AS query_plan_xml) AS qp 
  WHERE qt.query_sql_text LIKE '%' + @TableName + '%' 
  AND qp.query_plan_xml.exist('//Object[@Index=sql:variable("@IndexNameWithBrackets") and @Table=sql:variable("@TableNameWithBrackets")]') = 1

  Заметим, что при ссылках в плане запроса XML имена таблиц и индексов должны быть заключены в квадратные скобки.

  Дополнительное предупреждение

  Отключение индексов перед их удалением может стать полезным способом избежать влияния запроса на прозводительность (как будто он больше не существует), сохраняя при этом его определение с возможностью легкого восстановления (включения).

  Для повторного включения отключенного индекса вам нужно просто перестроть его (команда REBUILD).

  После отключения индексов, когда вы готовы выполнить следующее действие, то можете использовать скрипт ниже для простой генерации команд DROP либо REBUILD для всех отключенных индексов:

  Заключение

  Удаление избыточных индексов в SQL Server может значительно улучшить производительность и эффективность вашей базы данных. Избыточные индексы могут занимать ценное пространство в хранилище, замедлять ваши запросы и команды модификации данных, а также увеличивать использование процессора. Обнаружив и удалив избыточные индексы, вы можете освободить пространство, уменьшить накладные расходы при выполнении запросов и гарантировать, что для каждого запроса используется оптимальный индекс. Помните, что не все избыточные индексы очевидны, поэтому важно регулярно следить за использованием ваших индексов и удалять те из них, которые больше не нужны. Выполняя эту работу, вы сможете гарантировать, что ваша база SQL Server функционирует на максимуме производительности и эффективности.

  Как удалить индекс sql

  DROP INDEX удаляет существующий индекс из базы данных. Выполнить эту команду может только владелец индекса.

  Параметры

  CONCURRENTLY

  С этим указанием индекс удаляется, не блокируя одновременные операции выборки, добавления, изменения и удаления данных в таблице индекса. Обычный оператор DROP INDEX запрашивает блокировку ACCESS EXCLUSIVE для таблицы, не допуская другие обращения к ней до завершения удаления. Если же добавлено это указание, команда, напротив, будет ждать завершения конфликтующих транзакций.

  Применяя это указание, надо учитывать несколько особенностей. В частности, при этом можно задать имя только одного индекса, а параметр CASCADE не поддерживается. (Таким образом, индекс, поддерживающий ограничение UNIQUE или PRIMARY KEY , так удалить нельзя.) Кроме того, обычную команду DROP INDEX можно выполнить в блоке транзакции, а DROP INDEX CONCURRENTLY — нет.

  Для временных таблиц DROP INDEX всегда выполняется более простым, неблокирующим способом, так как они не могут использоваться никакими другими сеансами. IF EXISTS

  Не считать ошибкой, если индекс не существует. В этом случае будет выдано замечание. имя

  Имя (возможно, дополненное схемой) индекса, подлежащего удалению. CASCADE

  Автоматически удалять объекты, зависящие от данного индекса, и, в свою очередь, все зависящие от них объекты (см. Раздел 5.13). RESTRICT

  Отказать в удалении индекса, если от него зависят какие-либо объекты. Это поведение по умолчанию.

  Примеры

  Эта команда удалит индекс title_idx :

  DROP INDEX title_idx;

  Совместимость

  DROP INDEX является языковым расширением PostgreSQL . Средства обеспечения индексов в стандарте SQL не описаны.

  См. также

  Пред.	Наверх	След.
  DROP GROUP	Начало	DROP LANGUAGE

  Удаление индекса

  В этом разделе описано удаление индекса в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL.

  В этом разделе

  • Перед началом: ОграниченияБезопасность
  • Удаление индекса с помощьюСреда SQL Server Management StudioTransact-SQL

  Перед началом

  Ограничения

  Индексы, созданные с помощью ограничений уникальности и первичных ключей, нельзя удалить этим способом. Вместо этого следует удалять сами ограничения. Для удаления ограничения и соответствующего индекса используйте инструкцию ALTER TABLE с предложением DROP CONSTRAINT на языке Transact-SQL. Дополнительные сведения см. в статье Delete Primary Keys.

  Безопасность

  Разрешения

  Необходимо разрешение ALTER для таблицы или представления. По умолчанию это разрешение предоставляется предопределенной роли сервера sysadmin и предопределенным ролям базы данных db_ddladmin и db_owner .

  Использование среды SQL Server Management Studio

  Удаление индекса в обозревателе объектов

  1. В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо удалить индекс.
  2. Разверните папку Таблицы.
  3. Разверните таблицу, содержащую индекс, который нужно удалить.
  4. Разверните папку Индексы.
  5. Щелкните правой кнопкой мыши индекс, который необходимо удалить, и выберите пункт Удалить.
  6. В диалоговом окне Удаление объекта убедитесь, что в сетке Объекты для удаления указан нужный индекс, и нажмите кнопку ОК.

  Удаление индекса при помощи конструктора таблиц

  1. В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо удалить индекс.
  2. Разверните папку Таблицы.
  3. Правой кнопкой мыши щелкните таблицу, содержащую индекс, который необходимо удалить, и выберите «Конструктор».
  4. В меню Конструктор таблиц выберите пункт Индексы и ключи.
  5. В диалоговом окне Индексы и ключи выберите индекс, который хотите удалить.
  6. Нажмите Удалить.
  7. Нажмите кнопку Закрыть.
  8. В меню Файл выберите пункт Сохранитьимя_таблицы.

  Использование Transact-SQL

  Удаление индекса

  1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.
  2. На стандартной панели выберите пункт Создать запрос.
  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

  USE AdventureWorks2022; GO -- delete the IX_ProductVendor_BusinessEntityID index -- from the Purchasing.ProductVendor table DROP INDEX IX_ProductVendor_BusinessEntityID ON Purchasing.ProductVendor; GO 

  Дополнительные сведения см. в статье DROP INDEX (Transact-SQL).

  DROP INDEX (Transact-SQL)

  Удаляет один или несколько реляционных, пространственных, фильтруемых или XML-индексов из текущей базы данных. Можно удалить кластеризованный индекс и переместить полученную в результате таблицу в другую файловую группу или схему секционирования в одной транзакции, указав параметр MOVE TO.

  Инструкция DROP INDEX неприменима к индексам, созданным при указании ограничений параметров PRIMARY KEY и UNIQUE. Для удаления ограничения и соответствующего индекса используется инструкция ALTER TABLE с предложением DROP CONSTRAINT.

  Синтаксис, определяемый в , не будет поддерживаться в будущих версиях Microsoft SQL Server. Избегайте использования этого синтаксиса в новых разработках и учитывайте необходимость изменения в будущем приложений, использующих эти функции сейчас. Используйте синтаксис, описанный в . XML-индексы нельзя удалить с использованием обратно совместимого синтаксиса.

  Синтаксис

  -- Syntax for SQL Server (All options except filegroup and filestream apply to Azure SQL Database.) DROP INDEX [ IF EXISTS ] < [ . n ] | [ . n ] > ::= index_name ON [ WITH ( [ . n ] ) ] ::= [ owner_name. ] table_or_view_name.index_name ::= < database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name > ::= < 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 for Azure SQL Database DROP INDEX < [ . n ] > ::= index_name ON  ::=

  -- Syntax for Azure Synapse Analytics and Parallel Data Warehouse DROP INDEX index_name ON < database_name.schema_name.table_name | schema_name.table_name | table_name >[;] 

  Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

  Аргументы

  IF EXISTS
  Применимо к: SQL Server (SQL Server 2016 (13.x) до текущей версии.

  Условное удаление индекса только в том случае, если он уже существует.

  index_name
  Имя индекса, который необходимо удалить.

  database_name
  Имя базы данных.

  schema_name
  Имя схемы, которой принадлежит таблица или представление.

  table_or_view_name
  Имя таблицы или представления, связанного с индексом. Пространственные индексы поддерживаются только для таблиц.

  Чтобы отобразить отчет по индексам объекта, следует воспользоваться представлением каталога sys.indexes.

  База данных SQL Azure поддерживает формат трехкомпонентного имени имя_базы_данных.[имя_схемы].имя_объекта, если имя_базы_данных — это текущая база данных или tempdb, а имя_объекта начинается с символа #.

  Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL.

  Управляет параметрами кластеризованного индекса. Эти параметры неприменимы к другим типам индексов.

  MAXDOP = max_degree_of_parallelism
  Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL (только уровни производительности P2 и P3).

  Переопределяет параметр конфигурации max degree of parallelism на время выполнения операции с индексами. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism. MAXDOP можно использовать для ограничения числа процессоров, используемых при параллельном выполнении планов. Максимальное число процессоров — 64.

  Параметр MAXDOP нельзя использовать для пространственных или XML-индексов.

  Параметр max_degree_of_parallelism может иметь одно из следующих значений:

  1
  Подавляет формирование параллельных планов.

  >1
  Ограничивает указанным значением максимальное число процессоров, используемых для параллельных операций с индексами.

  0 (по умолчанию)
  В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

  Параллельные операции индексов недоступны в каждом выпуске SQL Server. Список функций, поддерживаемых выпусками SQL Server, см. в выпусках и поддерживаемых функциях SQL Server 2022.

  ONLINE = ON | OFF
  Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL Azure.

  Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.

  DNS
  Не устанавливаются долгосрочные блокировки таблицы. Это позволяет продолжать выполнение запросов и обновлений базовых таблиц.

  ВЫКЛ.
  Применяются блокировки таблиц, при этом таблицы становятся недоступны на время выполнения индексирования.

  Параметр ONLINE можно указать только при удалении кластеризованных индексов. Дополнительные сведения см. в разделе с примечаниями.

  Операции с индексами в сети недоступны в каждом выпуске SQL Server. Список функций, поддерживаемых выпусками SQL Server, см. в выпусках и поддерживаемых функциях SQL Server 2022.

  MOVE TO < partition_scheme_name(column_name) | filegroup_name | «default«
  Применимо: SQL Server 2008 (10.0.x) и более поздних версий. База данных SQL поддерживает «default» в качестве имени файловой группы.

  Определяет размещение, куда будут перемещаться строки данных, находящиеся на конечном уровне кластеризованного индекса. Данные перемещаются в новое расположение со структурой типа куча. В качестве нового расположения можно указать файловую группу или схему секционирования, но они должны уже существовать. Параметр MOVE TO недопустим для индексированных представлений и некластеризованных индексов. Если ни схема секционирования, ни файловая группа не указаны, результирующая таблица помещается в схему секционирования или файловую группу, которая определена для кластеризованного индекса.

  Если кластеризованный индекс удаляется с помощью параметра MOVE TO, то все некластеризованные индексы базовых таблиц создаются заново, но остаются в исходных файловых группах или схемах секционирования. Если базовая таблица перемещается в другую файловую группу или схему секционирования, некластеризованные индексы не перемещаются для совпадения с новым расположением базовой таблицы (кучи). Поэтому некластеризованные индексы могут потерять выравнивание с кучей, даже если ранее они были выровнены с кластеризованным индексом. Дополнительные сведения о выравнивании секционированных индексов см. в разделе Секционированные таблицы и индексы.

  partition_scheme_name(column_name)
  Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL.

  Указывает схему секционирования, в которой будет размещена результирующая таблица. Схема секционирования должна быть создана заранее выполнением инструкции CREATE PARTITION SCHEME или ALTER PARTITION SCHEME. Если размещение не указано и таблица секционирована, таблица включается в ту же схему секционирования, где размещен существующий кластеризованный индекс.

  Имя столбца в схеме не обязательно должно соответствовать столбцам из определения индекса. Можно указать любой столбец базовой таблицы.

  filegroup_name
  Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

  Указывает файловую группу, в которую будет помещена результирующая таблица. Если размещение не указано и таблица не секционирована, тогда результирующая таблица включается в ту файловую группу, где размещен существующий кластеризованный индекс. Файловая группа должна существовать.

  «default«
  Указывает размещение по умолчанию для результирующей таблицы.

  В этом контексте default не является ключевым словом. Это идентификатор файловой группы по умолчанию, и поэтому он должен быть заключен в разделители, например: MOVE TO «default« или MOVE TO [default]. Если указывается параметр «default«, то параметр QUOTED_IDENTIFIER для текущего сеанса должен иметь значение ON. Этот параметр принимается по умолчанию. Дополнительные сведения см. в статье SET QUOTED_IDENTIFIER (Transact-SQL).

  FILESTREAM_ON < partition_scheme_name | filestream_filegroup_name | «default« >
  Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

  Определяет папку, в которую будет перемещаться таблица FILESTREAM, находящаяся на конечном уровне кластеризованного индекса. Данные перемещаются в новое расположение со структурой типа куча. В качестве нового расположения можно указать файловую группу или схему секционирования, но они должны уже существовать. Параметр FILESTREAM ON недопустим для индексированных представлений или некластеризованных индексов. Если не указана схема секционирования, то данные будут размещены в той же схеме секционирования или файловой группе, которая была определена для кластеризованного индекса.

  partition_scheme_name
  Указывает схему секционирования для данных FILESTREAM. Схема секционирования должна быть создана заранее выполнением инструкции CREATE PARTITION SCHEME или ALTER PARTITION SCHEME. Если размещение не указано и таблица секционирована, таблица включается в ту же схему секционирования, где размещен существующий кластеризованный индекс.

  При указании схемы секционирования для инструкции MOVE TO необходимо использовать ту же схему секционирования, что и для инструкции FILESTREAM ON.

  filestream_filegroup_name
  Указывает файловую группу FILESTREAM для данных FILESTREAM. Если расположение не указано, а таблица не секционирована, данные включаются в файловую группу FILESTREAM по умолчанию.

  «default«
  Указывает расположение по умолчанию для данных FILESTREAM.

  В этом контексте default не является ключевым словом. Это идентификатор файловой группы по умолчанию, и поэтому он должен быть заключен в разделители, например: MOVE TO «default« или MOVE TO [default]. Если указано значение «default» (по умолчанию), параметр QUOTED_IDENTIFIER должен иметь значение ON для текущего сеанса. Этот параметр принимается по умолчанию. Дополнительные сведения см. в статье SET QUOTED_IDENTIFIER (Transact-SQL).

  Замечания

  При удалении некластеризованного индекса его определение удаляется из метаданных, а страницы данных сбалансированного дерева индекса удаляются из файлов базы данных. При удалении кластеризованного индекса определение индекса удаляется из метаданных, а строки данных, которые хранились на конечном уровне кластеризованного индекса, сохраняются в результирующей неупорядоченной таблице — куче. Все пространство, ранее занимаемое индексом, освобождается. Оно может быть впоследствии использовано любым объектом базы данных.

  В документации по SQL Server термин «сбалансированное дерево» обычно используется в отношении индексов. В индексах rowstore SQL Server реализует B+-дерево. Это не относится к индексам columnstore или хранилищам данных в памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

  Индекс невозможно удалить, если файловая группа, в которой он размещен, находится в режиме вне сети или доступна только для чтения.

  Если удален кластеризованный индекс индексированного представления, то все некластеризованные индексы и автоматически создаваемые статистики в этом представлении автоматически удаляются. Статистики, созданные вручную, не удаляются.

  Синтаксис table_or_view_name.index_name сохраняется для обратной совместимости. Пространственный или XML-индекс нельзя удалить с использованием синтаксиса обратной совместимости.

  Если удаляются индексы со 128 или более экстентами, компонент ядра СУБД откладывает действительное освобождение страниц и связанных с ними блокировок до фиксации транзакции.

  Иногда индексы удаляются и пересоздаются для реорганизации или перестроения индекса, например чтобы применить новое значение коэффициента заполнения, или для реорганизации данных после массовой загрузки. Для этих задач более эффективно использование инструкции ALTER INDEX, особенно для кластеризованных индексов. Инструкция ALTER INDEX REBUILD выполняется с оптимизациями, предотвращающими дополнительные издержки на перестройку некластеризованных индексов.

  Использование параметров инструкции DROP INDEX

  При удалении кластеризованного индекса можно установить следующие параметры: MAXDOP, ONLINE и MOVE TO.

  Используйте параметр MOVE TO, чтобы удалить кластеризованный индекс и переместить результирующую таблицу в другую файловую группу или схему секционирования в одной транзакции.

  При присвоении параметру ONLINE значения ON запросы и изменения базовых данных и связанных некластеризованных индексов не блокируются во время выполнения транзакции DROP INDEX. В режиме в сети одновременно может удаляться только один кластеризованный индекс. Полное описание параметра ONLINE см. в разделе Инструкция CREATE INDEX (Transact-SQL).

  Кластеризованный индекс нельзя удалить в режиме в сети, если индекс недоступен в представлении или содержит столбцы типа text, ntext, image, varchar(max), nvarchar(max), varbinary(max) или xml в строках данных конечного уровня.

  Использование параметров ONLINE = ON и MOVE TO требует дополнительного временного места на диске.

  После удаления индекса результирующая куча появляется в представлении каталога sys.indexes со значением NULL в столбце name. Для просмотра имени таблицы выполните соединение sys.indexes с sys.tables по object_id. Пример запроса см. в примере Г.

  На компьютерах с несколькими обработчиками, на которых запущен выпуск SQL Server 2005 Enterprise или более поздней версии, DROP INDEX может использовать больше процессоров для выполнения операций сканирования и сортировки, связанных с удалением кластеризованного индекса, как и другие запросы. Можно вручную настроить число процессоров, применяемых для запуска инструкции DROP INDEX, указав параметр индекса MAXDOP. Дополнительные сведения см. в статье Настройка параллельных операций с индексами.

  При удалении кластеризованного индекса соответствующие секции кучи сохраняют настройки сжатия данных, если только не была изменена схема секционирования. Если схема секционирования подверглась изменениям, все секции перестраиваются в распакованное состояние (DATA_COMPRESSION = NONE). Чтобы удалить кластеризованный индекс и изменить схему секционирования, необходимо выполнить следующие шаги.

  1. Удалить кластеризованный индекс.
  2. Изменение таблицы с помощью ALTER TABLE . ПЕРЕСТРОИТЬ. параметр, указывающий параметр сжатия.

  При удалении кластеризованного индекса в режиме не в сети удаляются только верхние уровни кластеризованных индексов, следовательно, операция выполняется довольно быстро. При удалении кластеризованного индекса в режиме «в сети» SQL Server перестраивает кучу дважды: один раз для первого шага, один для второго. Дополнительную информацию о сжатии данных см. в разделе Сжатие данных.

  XML-индексы

  При удалении XML-индекса нельзя указывать параметры. Кроме того, нельзя использовать синтаксис table_or_view_name.index_name. При удалении первичного XML-индекса все связанные вторичные XML-индексы удаляются автоматически. Дополнительные сведения см в разделе XML-индексы (SQL Server).

  Пространственные индексы

  Пространственные индексы поддерживаются только для таблиц. При удалении пространственного индекса нельзя указывать любые параметры или использовать .index_name. Правильный синтаксис:

  DROP INDEX spatial_index_name ON spatial_table_name;

  Дополнительные сведения о пространственных индексах см. в разделе Общие сведения о пространственных индексах.

  Разрешения

  Для выполнения инструкции DROP INDEX как минимум требуется разрешение ALTER для таблицы или представления. По умолчанию это разрешение предоставляется предопределенной роли сервера sysadmin и предопределенным ролям базы данных db_ddladmin и db_owner .

  Примеры

  А. Удаление индекса

  В следующем примере индекс IX_ProductVendor_VendorID ProductVendor в таблице удаляется в базе данных AdventureWorks2022.

  DROP INDEX IX_ProductVendor_BusinessEntityID ON Purchasing.ProductVendor; GO 

  B. Удаление нескольких индексов

  В следующем примере удаляются два индекса в одной транзакции в базе данных AdventureWorks2022.

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

  C. Удаление кластеризованного индекса в режиме в сети и установка параметра MAXDOP

  В следующем примере удаляется кластеризованный индекс с параметром ONLINE , установленным в значение ON и параметром MAXDOP , установленным в значение 8 . Поскольку параметр MOVE TO не был указан, результирующая таблица сохраняется в той же файловой группе, что и индекс. В этом примере используется база данных AdventureWorks2022

  Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL.

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

  D. Удаление кластеризованного индекса в режиме в сети и перемещение таблицы в другую файловую группу

  В следующем примере кластеризованный индекс удаляется в режиме в сети и результирующая таблица (куча) перемещается в файловую группу NewGroup с использованием предложения MOVE TO . Представления каталога sys.indexes , sys.tables и sys.filegroups запрашиваются для проверки размещения индекса и таблицы в файловых группах до и после перемещения. (Начиная с версии SQL Server 2016 (13.x) можно использовать синтаксис DROP INDEX IF EXISTS.)

  Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

  --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 

  Д. Удаление ограничения PRIMARY KEY в режиме в сети

  Индексы, созданные в результате создания ограничений параметров PRIMARY KEY или UNIQUE, нельзя удалить с помощью инструкции DROP INDEX. Они удаляются с помощью инструкции ALTER TABLE DROP CONSTRAINT. Дополнительные сведения см. в разделе ALTER TABLE.

  Следующий пример иллюстрирует удаление кластеризованного индекса с ограничением PRIMARY KEY путем удаления ограничения. У таблицы ProductCostHistory нет ограничений FOREIGN KEY. Если бы они были, необходимо было бы сначала удалить их.

  -- 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); 

  Е. Удаление XML-индекса

  Следующий пример удаляет XML-индекс в ProductModel таблице в базе данных AdventureWorks2022.

  DROP INDEX PXML_ProductModel_CatalogDescription ON Production.ProductModel; 

  G. Удаление кластеризованного индекса для таблицы FILESTREAM

  В следующем примере кластеризованный индекс удаляется в режиме в сети и результирующая таблица (куча) вместе с данными FILESTREAM перемещается в схему секционирования MyPartitionScheme с использованием предложений MOVE TO и FILESTREAM ON .

  Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

  Читать:

  Почему значок вай фай с восклицательным знаком