DROP INDEX (Transact-SQL)
Se aplica a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)
Quita uno o más índices XML, filtrados, espaciales o relacionales de la base de datos actual. Puede quitar un índice clúster y mover la tabla resultante a otro grupo de archivos o esquema de partición en una sola transacción especificando la opción MOVE TO.
La instrucción DROP INDEX no es aplicable a los índices creados mediante la definición de restricciones PRIMARY KEY y UNIQUE. Para quitar la restricción y el índice correspondiente, use ALTER TABLE con la cláusula DROP CONSTRAINT.
Importante
La sintaxis definida en <drop_backward_compatible_index> dejará de incluirse en una futura versión de Microsoft SQL Server. Evite utilizar esta sintaxis en nuevos trabajos de programación y tenga previsto modificar las aplicaciones que actualmente la utilizan. En su lugar, utilice la sintaxis especificada en <drop_relational_or_xml_or_spatial_index>. Los índices XML no se pueden quitar utilizando la sintaxis compatible con versiones anteriores.
Convenciones de sintaxis de Transact-SQL
Sintaxis
-- Syntax for SQL Server (All options except filegroup and filestream apply to 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 for 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 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 }
[;]
Nota:
Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.
Argumentos
IF EXISTS
Se aplica a: SQL Server (desde SQL Server 2016 (13.x) hasta la versión actual).
Quita el índice condicionalmente solo si ya existe.
index_name
Es el nombre del índice que se va a quitar.
database_name
Es el nombre de la base de datos.
schema_name
Es el nombre del esquema al que pertenece la tabla o la vista.
table_or_view_name
Es el nombre de la tabla o vista asociada al índice. Los índices espaciales solo se admiten en tablas.
Para mostrar un informe de los índices en un objeto, use la vista de catálogo sys.indexes.
Azure SQL Database admite el formato de nombre de tres partes nombre_basededatos.[nombre_esquema].nombre_objeto cuando nombre_basededatos es la base de datos actual o tempdb y nombre_objeto comienza con #.
<drop_clustered_index_option>
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores, y SQL Database.
Controla las opciones de los índices clúster. Estas opciones no se pueden utilizar con otros tipos de índices.
MAXDOP = max_degree_of_parallelism
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores, y SQL Database (solo niveles de rendimiento P2 y P3).
Reemplaza la opción de configuración de max_degree_of_parallelism mientras dure la operación de índice. Para obtener más información, vea Establecer la opción de configuración del servidor Grado máximo de paralelismo. Utilice MAXDOP para establecer un límite para el número de procesadores utilizados en la ejecución de un plan paralelo. El máximo es 64 procesadores.
Importante
MAXDOP no se admite en índices espaciales o índices XML.
max_degree_of_parallelism puede tener estos valores:
1
Suprime la generación de planes paralelos.
>1
Restringe el número máximo de procesadores utilizados en una operación de índice paralelo para el número especificado.
0 (predeterminado)
Usa el número real de procesadores o menos, según la carga de trabajo actual del sistema.
Para obtener más información, vea Configurar operaciones de índice en paralelo.
Nota:
Las operaciones de índices en paralelo no están disponibles en todas las ediciones de SQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL Server, consulte Ediciones y características admitidas de SQL Server 2022.
ONLINE = ON | OFF
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores, y Azure SQL Database.
Especifica si las tablas subyacentes y los índices asociados están disponibles para realizar consultas y modificar datos durante la operación de índice. El valor predeterminado es OFF.
ACTIVAR
No se mantienen los bloqueos de tabla a largo plazo. Esto permite que continúen las consultas o actualizaciones en la tabla subyacente.
Apagado
Se aplican bloqueos de tabla y la tabla deja de estar disponible mientras dure la operación con índices.
La opción ONLINE solo se puede especificar cuando se quitan índices clúster. Para obtener más información, vea la sección Comentarios.
Nota
Las operaciones de índices en línea no están disponibles en todas las ediciones de SQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL Server, consulte Ediciones y características admitidas de SQL Server 2022.
MOVE TO { partition_scheme_name(column_name) | filegroup_name | "default"
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores. SQL Database admite "default" como el nombre del grupo de archivos.
Especifica la ubicación donde se moverán las filas de datos que están actualmente en el nivel hoja del índice clúster. Los datos se mueven a la nueva ubicación en forma de montón. Se puede especificar un esquema de partición o un grupo de archivos como la nueva ubicación, pero es necesario que ya existan. MOVE TO no es válido para vistas indizadas o índices no clúster. Si no se especifica ningún esquema de partición o grupo de archivos, la tabla resultante se colocará en el mismo esquema de partición o grupo de archivos que se definió para el índice clúster.
Si se quita un índice clúster mediante MOVE TO, se vuelven a generar todos los índices no clúster de la tabla base, pero permanecen en sus grupos de archivos o esquemas de partición originales. Si la tabla base se mueve a un grupo de archivos o esquema de partición diferente, los índices no clúster no se mueven para hacerlos coincidir con la nueva ubicación de la tabla base (montón). Por lo tanto, aunque los índices no clúster estuvieran previamente alineados con el índice clúster, es posible que ya no lo estén con el montón. Para más información sobre la alineación de índices con particiones, vea Tablas e índices con particiones.
partition_scheme_name(column_name)
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores, y SQL Database.
Especifica un esquema de partición como la ubicación de la tabla resultante. Es necesario que el esquema de partición se haya creado previamente ejecutando CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. Si no se especifica ninguna ubicación y la tabla tiene particiones, la tabla se incluye en el mismo esquema de partición que el índice clúster existente.
El nombre de columna del esquema no se limita a las columnas de la definición del índice. Se puede especificar cualquier columna de la tabla base.
filegroup_name
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores.
Especifica un grupo de archivos como la ubicación de la tabla resultante. Si no se especifica ninguna ubicación y la tabla no tiene particiones, la tabla resultante se incluye en el mismo grupo de archivos que el índice clúster. El grupo de archivos debe existir previamente.
"default"
Especifica la ubicación predeterminada de la tabla resultante.
Nota
En este contexto, el valor predeterminado no es una palabra clave. Es un identificador para el grupo de archivos predeterminado y debe delimitarse, como en MOVE TO "default" o MOVE TO [default]. Si se especifica el parámetro "default", la opción QUOTED_IDENTIFIER debe establecerse en ON para la sesión actual. Esta es la configuración predeterminada. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).
FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores.
Especifica la ubicación donde se moverá la tabla FILESTREAM que está actualmente en el nivel hoja del índice clúster. Los datos se mueven a la nueva ubicación en forma de montón. Se puede especificar un esquema de partición o un grupo de archivos como la nueva ubicación, pero es necesario que ya existan. FILESTREAM ON no es válida para vistas indizadas ni índices no clúster. Si no se especifica un esquema de partición, los datos se ubicarán en el mismo esquema de partición que se definió para el índice clúster.
partition_scheme_name
Especifica un esquema de partición de los datos FILESTREAM. Es necesario que el esquema de partición se haya creado previamente ejecutando CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. Si no se especifica ninguna ubicación y la tabla tiene particiones, la tabla se incluye en el mismo esquema de partición que el índice clúster existente.
Si especifica un esquema de partición para MOVE TO, se debe utilizar el mismo esquema de partición para FILESTREAM ON.
filestream_filegroup_name
Especifica un grupo de archivos FILESTREAM de los datos FILESTREAM. Si no se especifica ninguna ubicación y la tabla no tiene particiones, los datos se incluyen en el grupo de archivos FILESTREAM predeterminado.
"default"
Especifica la ubicación predeterminada de los datos FILESTREAM.
Nota
En este contexto, el valor predeterminado no es una palabra clave. Es un identificador para el grupo de archivos predeterminado y debe delimitarse, como en MOVE TO "default" o MOVE TO [default]. Si se especifica "default", la opción QUOTED_IDENTIFIER debe tener el valor ON para la sesión actual. Esta es la configuración predeterminada. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).
Observaciones
Cuando se quita un índice no clúster, se quita la definición del índice de los metadatos y las páginas de datos del índice (árbol b) se quitan de los archivos de la base de datos. Cuando se quita un índice clúster, se quita la definición del índice de los metadatos y las filas de datos que se almacenaron en el nivel hoja del índice clúster se almacenan en la tabla resultante no ordenada, un montón. Se recuperará todo el espacio anteriormente ocupado por el índice. Después, se puede utilizar este espacio para cualquier objeto de base de datos.
Nota
La documentación de SQL Server utiliza el término árbol B generalmente en referencia a los índices. En los índices del almacén de filas, SQL Server implementa un árbol B+. Esto no se aplica a los índices de almacén de columnas ni a los almacenes de datos en memoria. Para obtener más información, consulte la guía de diseño y arquitectura de índices de SQL Server y Azure SQL.
Un índice no se puede quitar si el grupo de archivos en el que se encuentra está sin conexión o se ha definido como de solo lectura.
Cuando se quita el índice clúster de una vista indizada, automáticamente se quitan todos los índices no clúster y las estadísticas creadas automáticamente en la misma vista. Las estadísticas creadas manualmente no se quitan.
La sintaxis table_or_view_name.index_name se conserva por motivos de compatibilidad con versiones anteriores. Los índices XML o espaciales no se pueden quitar utilizando la sintaxis compatible con versiones anteriores.
Cuando se quitan índices con 128 extensiones o más, el Motor de base de datos aplaza las cancelaciones de asignación de página reales y los bloqueos asociados, hasta después de que se confirme la transacción.
A veces, los índices se quitan y se vuelven crear para reorganizarlos o volver a generarlos, por ejemplo, para aplicar un nuevo valor de factor de relleno o para reorganizar los datos después de una carga masiva. Para ello, el uso de ALTER INDEX es más eficaz, especialmente para los índices agrupados. ALTER INDEX REBUILD incluye optimizaciones para impedir la sobrecarga que representa la regeneración de los índices no clúster.
Usar opciones con DROP INDEX
Puede establecer las siguientes opciones de índice cuando quite un índice clúster: MAXDOP, ONLINE y MOVE TO.
Utilice MOVE TO para quitar el índice clúster y mover la tabla resultante a otro grupo de archivos o esquema de partición en una sola transacción.
Cuando se especifica ONLINE = ON, las consultas y modificaciones de los datos subyacentes e índices no clúster asociados no se bloquean con la transacción DROP INDEX. No se puede quitar más de un índice clúster en línea al mismo tiempo. Para obtener una descripción completa de la opción ONLINE, consulte CREATE INDEX (Transact-SQL).
No se puede quitar un índice clúster en línea si el índice está deshabilitado en una vista o contiene columnas de tipo text, ntext, image, varchar(max) , nvarchar(max) , varbinary(max) o xml en las filas de datos de nivel hoja.
Para utilizar las opciones ONLINE = ON y MOVE TO se requiere más espacio temporal en el disco.
Después de quitar un índice, el montón resultante aparece en la vista de catálogo sys.indexes con NULL en la columna name. Para ver el nombre de la tabla, combine sys.indexes con sys.tables en object_id. Dispone de una consulta de ejemplo en el ejemplo D.
En los equipos con varios procesadores que ejecutan SQL Server 2005 Enterprise Edition o posterior, DROP INDEX puede usar más procesadores para realizar las operaciones de recorrido y ordenación asociadas a la eliminación del índice clúster, al igual que hacen otras consultas. Puede configurar de forma manual el número de procesadores que se utilizan para ejecutar la instrucción DROP INDEX especificando la opción de índice MAXDOP. Para obtener más información, vea Configurar operaciones de índice en paralelo.
Cuando se quita un índice clúster, las particiones del montón correspondientes retienen su valor de compresión de datos, a menos que se modifique el esquema de partición. Si se cambia el esquema de la partición, todas las particiones se generaran en un estado sin comprimir (DATA_COMPRESSION = NONE). Para quitar un índice clúster y cambiar el esquema de la partición, es necesario llevar a cabo los dos pasos siguientes:
Quitar el índice clúster.
Modificar la tabla utilizando una opción ALTER TABLE... REBUILD... que especifique la opción de compresión.
Cuando se quita un índice clúster OFFLINE, solo se quitan los niveles superiores de índices clústeres, por lo que la operación es realmente rápida. Cuando se quita un índice agrupado ONLINE, SQL Server vuelve a generar el montón dos veces, una vez para el paso 1 y otra para el paso 2. Para más información sobre la compresión de datos, vea Compresión de datos.
Índices XML
No se pueden especificar opciones al quitar un índice XML. Tampoco se puede usar la sintaxis table_or_view_name.index_name. Cuando se quita un índice XML principal, todos los índices XML secundarios asociados se quitan automáticamente. Para obtener más información, consulte Índices XML (SQL Server).
Índices espaciales
Los índices espaciales solo se admiten en tablas. Al quitar un índice espacial, no se puede especificar ninguna opción ni usar .index_name. La sintaxis correcta es la siguiente:
DROP INDEX spatial_index_name ON spatial_table_name;
Para más información sobre los índices espaciales, vea Información general sobre los índices espaciales.
Permisos
Para ejecutar DROP INDEX, se requiere, como mínimo, el permiso ALTER en la tabla o vista. Este permiso se concede de forma predeterminada al rol fijo de servidor sysadmin y a los roles fijos de base de datos db_ddladmin y db_owner .
Ejemplos
A. Quitar un índice
En el ejemplo siguiente se elimina el índice IX_ProductVendor_VendorID en la tabla ProductVendor de la base de datos AdventureWorks2022.
DROP INDEX IX_ProductVendor_BusinessEntityID
ON Purchasing.ProductVendor;
GO
B. Quitar varios índices
En el ejemplo siguiente se eliminan dos índices en una única transacción de la base de datos AdventureWorks2022.
DROP INDEX
IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
IX_Address_StateProvinceID ON Person.Address;
GO
C. Quitar un índice clúster en línea y establecer la opción MAXDOP
En el ejemplo siguiente se elimina un índice clúster con la opción ONLINE establecida en ON y MAXDOP establecida en 8. Dado que no se ha especificado la opción MOVE TO, la tabla resultante se almacena en el mismo grupo de archivos que el índice. En estos ejemplos se usa la base de datos AdventureWorks2022.
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores, y SQL Database.
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);
GO
D. Quitar un índice clúster en línea y mover la tabla a un nuevo grupo de archivos
En el ejemplo siguiente se elimina un índice clúster en línea y se mueve la tabla resultante (montón) al grupo de archivos NewGroup mediante la cláusula MOVE TO . Las vistas de catálogo sys.indexes, sys.tablesy sys.filegroups se consultan para comprobar la ubicación del índice y la tabla en los grupos de archivos antes y después del desplazamiento. (A partir de SQL Server 2016 (13.x) se puede usar la sintaxis DROP INDEX IF EXISTS).
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores.
--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
E. Quitar una restricción PRIMARY KEY en línea
Los índices creados como resultado de la creación de restricciones PRIMARY KEY o UNIQUE no se pueden quitar mediante DROP INDEX. Se quitan con la instrucción ALTER TABLE DROP CONSTRAINT. Para más información, vea ALTER TABLE.
En el ejemplo siguiente se elimina un índice clúster con una restricción PRIMARY KEY al quitar la restricción. La tabla ProductCostHistory no tiene restricciones FOREIGN KEY. Si lo hiciera, sería necesario quitar esas restricciones primero.
-- 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. Quitar un índice XML
En el ejemplo siguiente se quita un índice XML en la tabla ProductModel de la base de datos AdventureWorks2022.
DROP INDEX PXML_ProductModel_CatalogDescription
ON Production.ProductModel;
G. Quitar un índice clúster en una tabla FILESTREAM
En el ejemplo siguiente se elimina un índice clúster en línea y se mueven la tabla resultante (montón) y los datos FILESTREAM al esquema de partición MyPartitionScheme mediante las cláusulas MOVE TO y FILESTREAM ON.
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores.
DROP INDEX PK_MyClusteredIndex
ON dbo.MyTable
WITH (MOVE TO MyPartitionScheme,
FILESTREAM_ON MyPartitionScheme);
GO
Consulte también
ALTER INDEX (Transact-SQL)
ALTER PARTITION SCHEME (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.indexes (Transact-SQL)
sys.tables (Transact-SQL)
sys.filegroups (Transact-SQL)
sp_spaceused (Transact-SQL)
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de