DROP INDEX (Transact-SQL)

Importante:

La sintaxis definida en <drop_backward_compatible_index> dejará de incluirse en futuras versiones 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_index>. Los índices XML no se pueden quitar utilizando la sintaxis compatible con versiones anteriores.

Quita uno o más índices XML o relacionales de la base de datos actual. En SQL Server 2005, se 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 al especificar 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.

Convenciones de sintaxis de Transact-SQL

Sintaxis

DROP INDEX
{ <drop_relational_or_xml_index> [ ,...n ] 
| <drop_backward_compatible_index> [ ,...n ]
}

<drop_relational_or_xml_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 ] . | schema_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" 
            }
}

Argumentos

• 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 vista.

• table_or_view_name
  Es el nombre de la tabla o vista asociada al índice. Para mostrar un informe de los índices en un objeto, utilice la vista de catálogo sys.indexes.

• <drop_clustered_index_option>
  Controla las opciones de los índices agrupados. Estas opciones no se pueden utilizar con otros tipos de índices.

• MAXDOP = max_degree_of_parallelism
  Reemplaza la opción de configuración max degree of parallelism mientras dure la operación con índices. Para obtener más información, vea max degree of parallelism (opción). Use MAXDOP para limitar el número de procesadores utilizados en una ejecución de planes paralelos. El número máximo son 64 procesadores.

  max_degree_of_parallelism puede ser:

  • 1
    Suprime la generación de planes paralelos.
  • >1
    Limita al número especificado el número máximo de procesadores utilizados en una operación con índices en paralelo.
  • 0 (valor predeterminado)
    Utiliza 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 con índices en paralelo sólo están disponibles en SQL Server 2005 Enterprise Edition.

• ONLINE = ON | OFF
  Especifica si hay tablas subyacentes e índices asociados disponibles para las consultas y la modificación de datos durante la operación con índices. El valor predeterminado es OFF.

  • ON
    No se mantienen los 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 deja de estar disponible mientras dure la operación con índices.

  La opción ONLINE sólo se puede especificar cuando se quitan índices agrupados. Para obtener más información, vea la sección Notas.

  [!NOTA] Las operaciones con índices en línea sólo están disponibles en SQL Server 2005 Enterprise Edition.

• MOVE TO
  Especifica la ubicación donde se moverán las filas de datos que están actualmente en el nivel hoja del índice agrupado. 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 éstos deben existir previamente. MOVE TO no es válido para vistas indizadas o índices no agrupados. 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 agrupado.

  Si se quita un índice agrupado mediante MOVE TO, se vuelven a generar todos los índices no agrupados 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 agrupados no se mueven para hacerlos coincidir con la nueva ubicación de la tabla base (montón). Por tanto, aunque los índices no agrupados estuvieran antes alineados con el índice agrupado, es posible que ya no lo estén con el montón. Para obtener más información acerca de la alineación de índices con particiones, vea Directrices especiales para índices con particiones.

• partition_scheme_name ( column_name )
  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 mediante la ejecución de CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. Si no se especifica ninguna ubicación y la tabla tiene particiones, ésta se incluye en el mismo esquema de partición que el índice agrupado 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
  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] Default, en este contexto, no es una palabra clave. Es un identificador del 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 ser ON para la sesión actual. Éste es el valor predeterminado. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).

Notas

Cuando se quita un índice no agrupado, 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 agrupado, se quita la definición del índice de los metadatos y las filas de datos que se almacenaron en el nivel hoja del índice agrupado 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.

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 sólo lectura.

Cuando se quita el índice agrupado de una vista indizada, automáticamente se quitan todos los índices no agrupados y las estadísticas creadas automáticamente en la misma vista. Las estadísticas creadas manualmente no se quitan.

La sintaxis index_name ON { table_or_view_name } es nueva en SQL Server 2005. La sintaxis table_or_view_name**.**index_name se conserva para compatibilidad con versiones anteriores. Si se combinan las dos opciones en una misma transacción, la instrucción genera un error. Los índices XML no se pueden quitar utilizando la sintaxis compatible con versiones anteriores.

Cuando se quitan índices con 128 extensiones o más, el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) difiere las cancelaciones de asignación de páginas reales y sus bloqueos asociados hasta que se confirma una transacción. Para obtener más información, vea Quitar y volver a generar objetos grandes.

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 es más eficaz utilizar ALTER INDEX, especialmente en el caso de los índices agrupados. ALTER INDEX REBUILD incluye optimizaciones para impedir la sobrecarga que representa la regeneración de los índices no agrupados.

Usar opciones con DROP INDEX

En SQL Server 2005, cuando se quita un índice agrupado, se pueden establecer las opciones de índices MAXDOP, ONLINE y MOVE TO.

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

Cuando se especifica ONLINE = ON, las consultas y modificaciones de los datos subyacentes e índices no agrupados asociados no se bloquean con la transacción DROP INDEX. No se puede quitar más de un índice agrupado en línea al mismo tiempo. Para obtener una descripción completa de la opción ONLINE, vea CREATE INDEX (Transact-SQL).

No se puede quitar un índice agrupado 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. Para obtener más información, vea Determinar requisitos de espacio en disco del índice.

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 equipos con varios procesadores que ejecutan SQL Server 2005 Enterprise Edition, DROP INDEX puede utilizar más procesadores para realizar las operaciones de recorrido y ordenación asociadas a la acción de quitar el índice agrupado, igual que sucede con otras consultas. Se puede configurar manualmente el número de procesadores utilizados para ejecutar la instrucción DROP INDEX al especificar la opción de índices MAXDOP. Para obtener más información, vea Configurar operaciones de índice en paralelo.

Índices XML

No se pueden especificar opciones al quitar un índice XML. 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 en columnas de tipo de datos xml.

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 a la función fija de servidor sysadmin y a las funciones fijas 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 de la tabla ProductVendor.

USE AdventureWorks;
GO
DROP INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor;
GO

B. Quitar varios índices

En el ejemplo siguiente se eliminan dos índices en una sola transacción.

USE AdventureWorks;
GO
DROP INDEX
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
    IX_VendorAddress_AddressID ON Purchasing.VendorAddress;
GO

C. Quitar un índice agrupado en línea y establecer la opción MAXDOP

En el ejemplo siguiente se elimina un índice agrupado 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.

[!NOTA] Este ejemplo sólo se puede ejecutar en SQL Server 2005 Enterprise Edition.

USE AdventureWorks;
GO
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 agrupado 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.tables y 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.

USE AdventureWorks;
GO
--Create a clustered index on the PRIMARY filegroup if it does not exist.
IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 
            N'AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate')
    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.
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the ALTER DATABASE statement 
IF NOT EXISTS (SELECT name FROM sys.filegroups
                WHERE name = N'NewGroup')
    BEGIN
    ALTER DATABASE AdventureWorks
        ADD FILEGROUP NewGroup;
    EXECUTE ('ALTER DATABASE AdventureWorks
        ADD FILE (NAME = File1,
            FILENAME = '''+ @data_path + '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 obtener más información, vea ALTER TABLE.

En el ejemplo siguiente se elimina un índice agrupado 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.

USE AdventureWorks;
GO
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
ALTER TABLE Production.ProductCostHistory
    DROP CONSTRAINT PK_ProductCostHistory_ProductID_StartDate
        WITH (ONLINE = ON);
GO

F. Quitar un índice XML

En el ejemplo siguiente se quita un índice XML de la tabla ProductModel.

USE AdventureWorks;
GO
DROP INDEX PXML_ProductModel_CatalogDescription 
    ON Production.ProductModel;
GO

Vea también

Referencia

ALTER PARTITION SCHEME (Transact-SQL)
ALTER INDEX (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.indexes (Transact-SQL)
sys.tables (Transact-SQL)
sys.filegroups (Transact-SQL)
sp_spaceused (Transact-SQL)

Otros recursos

Determinar requisitos de espacio en disco del índice
Quitar índices

Ayuda e información

Obtener ayuda sobre SQL Server 2005