Compartir vía

DROP INDEX (Transact-SQL)

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Quita uno o más índices XML, filtrados, espaciales o relacionales de la base de datos actual. Puede quitar un índice agrupado y mover la tabla resultante a otro grupo de archivos o esquema de partición en una sola transacción especificando la MOVE TO opción .

La DROP INDEX instrucción no se aplica a los índices creados mediante la definición PRIMARY KEY de restricciones o UNIQUE . Para quitar la restricción y el índice correspondiente, use ALTER TABLE con la DROP CONSTRAINT cláusula .

Importante

La sintaxis definida en <drop_backward_compatible_index> se quitará en una versión futura de 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 mediante la sintaxis compatible con versiones anteriores.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis para SQL Server (todas las opciones excepto el grupo de archivos y la secuencia de archivos se aplican a 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" } ]
}

Sintaxis de 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 }

Sintaxis de 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 }
[ ; ]

Argumentos

IF EXISTS

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores.

Quita el índice condicionalmente solo si ya existe.

index_name

Nombre del índice que se va a quitar.

database_name

El nombre de la base de datos.

schema_name

Nombre del esquema al que pertenece la tabla o la vista.

table_or_view_name

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: database_name.[schema_name].object_name cuando database_name es la base de datos actual o el database_name es tempdb y el object_name comienza con #.

<drop_clustered_index_option>

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores, SQL Database.

Controla las opciones de los índices clúster. Estas opciones no se pueden usar con otros tipos de índice.

MAXDOP = max_degree_of_parallelism

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores, SQL Database (solo niveles de rendimiento P2 y P3).

Reemplaza la opción de configuración de max_degree_of_parallelism durante la operación de índice. Para obtener más información, consulte Configuración del grado máximo de paralelismo (opción de configuración del servidor). Use MAXDOP para limitar el número de procesadores usados en una ejecución de plan paralelo. El máximo es 64 procesadores.

Importante

MAXDOP no se permite para índices espaciales ni índices XML.

max_degree_of_parallelism puede ser uno de los siguientes valores.

Valor Descripción
1 Suprime la generación de planes paralelos
>1 Restringe el número máximo de procesadores usados en una operación de índice paralela al número especificado.
0 (valor predeterminado) Usa el número real de procesadores o menos en función de la carga de trabajo del sistema actual.

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

  • ON: no se mantienen bloqueos de tabla a largo plazo. Esto permite que continúen las consultas o actualizaciones en la tabla subyacente.

  • OFF: se aplican bloqueos de tabla y la tabla no está disponible durante la operación de índice.

La ONLINE opción solo se puede especificar al quitar índices agrupados. Para obtener más información, vea la sección Notas.

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.

MOVER A { 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 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 indexadas o índices no agrupados. Si no se especifica un esquema de partición o un grupo de archivos, la tabla resultante se encuentra en el mismo esquema de partición o grupo de archivos que se definió para el índice agrupado.

Si se quita un índice agrupado mediante MOVE TO, se vuelven a generar los índices no agrupados de la tabla base, pero permanecen en sus grupos de archivos originales o esquemas de partición. Si la tabla base se mueve a otro grupo de archivos o esquema de partición, los índices no agrupados no se mueven para que coincidan 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 obtener más información sobre la alineación de índices con particiones, consulte Tablas e índices con particiones.

partition_scheme_name ( column_name )

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores, SQL Database.

Especifica un esquema de partición como la ubicación de la tabla resultante. El esquema de partición ya debe crearse 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 está restringido a las columnas de la definición de í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 agrupado. 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 en MOVE TO [default]. Si "default" se especifica , la QUOTED_IDENTIFIER opción debe establecerse ON para la sesión actual. Esta es la configuración predeterminada. Para más información, consulte SET QUOTED_IDENTIFIER.

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álido para vistas indexadas o índices no agrupados. Si no se especifica un esquema de partición, los datos se encuentran en el mismo esquema de partición que se definió para el índice agrupado.

partition_scheme_name

Especifica un esquema de partición de los datos FILESTREAM. El esquema de partición ya debe crearse 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, debe usar 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 en 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 más información, consulte SET QUOTED_IDENTIFIER.

Comentarios

Cuando se quita un índice no clúster, la definición del índice se quita de los metadatos y las páginas de datos de índice (árbol B) se quitan de los archivos de 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 usa el término árbol B generalmente en referencia a índices. En los índices de almacén de filas, el Motor de base de datos implementa un árbol B+. Esto no se aplica a índices o índices de almacén de columnas en tablas optimizadas para memoria. Para obtener más información, consulte la guía de diseño y arquitectura de índices de SQL Server y Azure SQL.

No se puede quitar un índice si el grupo de archivos en el que se encuentra está sin conexión o se establece en de solo lectura.

Cuando se quita el índice agrupado de una vista indexada, todos los índices no clúster y las estadísticas autocreadas en la misma vista se quitan automáticamente. No se quitan las estadísticas creadas manualmente.

La sintaxis <table_or_view_name>.<index_name> se mantiene por motivos de compatibilidad con versiones anteriores. No se puede quitar un índice XML o un índice espacial mediante 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 tiene optimizaciones para evitar la sobrecarga de volver a generar los índices no clúster.

Uso de opciones con DROP INDEX

Puede establecer las siguientes opciones de índice al quitar un índice agrupado: MAXDOP, ONLINEy MOVE TO.

Use MOVE TO para quitar el índice agrupado y mover la tabla resultante a otro grupo de archivos o esquema de partición en una sola transacción.

Al especificar ONLINE = ON, las consultas y las modificaciones en los datos subyacentes y los índices no clúster asociados no están bloqueados por la DROP INDEX transacción. 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 ONLINE opción, consulte CREATE INDEX.

No se puede quitar un índice agrupado en línea si el índice está deshabilitado en una vista o contiene columnas text, ntext, image, varchar(max), nvarchar(max), varbinary(max)o xml en las filas de datos de nivel hoja.

El uso de las ONLINE = ON opciones y MOVE TO requiere más espacio en disco temporal.

Después de quitar un índice, el montón resultante aparece en la vista de sys.indexes catálogo con NULL en la name columna . Para ver el nombre de la tabla, únase sys.indexes a sys.tables en object_id. Dispone de una consulta de ejemplo en el ejemplo D.

En equipos de varios procesadores que ejecutan SQL Server 2005 Enterprise Edition o versiones posteriores, DROP INDEX puede usar más procesadores para realizar las operaciones de examen y ordenación asociadas con la eliminación del índice agrupado, al igual que otras consultas. Puede configurar manualmente el número de procesadores que se usan para ejecutar la DROP INDEX instrucción especificando la MAXDOP opción de índice. 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 partición, todas las particiones se vuelven a generar 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:

  1. Quitar el índice clúster.

  2. Modifique la tabla mediante una ALTER TABLE ... REBUILD ... opción que especifique la opción de compresión.

Cuando se quita OFFLINEun índice agrupado, solo se quitan los niveles superiores de índices agrupados; por lo tanto, la operación es rápida. Cuando se quita ONLINEun índice agrupado, SQL Server vuelve a generar el montón dos veces, una vez para el paso 1 y una vez para el paso 2. Para obtener más información sobre la compresión de datos, consulte Compresión de datos.

índices XML

No se pueden especificar opciones al quitar un índiceXML. Además, no puede usar la <table_or_view_name>.<index_name> sintaxis . Cuando se quita un índice XML principal, todos los índices XML secundarios asociados se quitan automáticamente. Para obtener más información, vea Índices XML (SQL Server).

Índices espaciales

Los índices espaciales solo se admiten en tablas. Al quitar un índice espacial, no se pueden especificar opciones 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 necesita, como mínimo, el permiso ALTER en la tabla o en la 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

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

A Quitar un índice

En el ejemplo siguiente se elimina el índice IX_ProductVendor_BusinessEntityID 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 especificó la MOVE TO opción, la tabla resultante se almacena en el mismo grupo de archivos que el índice.

Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores, SQL Database.

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

D. Quitar un índice agrupado 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), puede usar la DROP INDEX IF EXISTS sintaxis .

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 que se crean como resultado de la creación PRIMARY KEY o UNIQUE las restricciones no se pueden quitar mediante DROP INDEX. Se quitan mediante la ALTER TABLE DROP CONSTRAINT instrucción . Para más información, vea ALTER TABLE.

En el ejemplo siguiente se elimina un índice agrupado con una PRIMARY KEY restricción quitando la restricción . La ProductCostHistory tabla no FOREIGN KEY tiene restricciones. 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 agrupado 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

Contenido relacionado