Crear índices filtradosCreate Filtered Indexes

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

En este tema se describe cómo crear un índice filtrado en SQL Server 2017SQL Server 2017 mediante SQL Server Management StudioSQL Server Management Studio o Transact-SQLTransact-SQL.This topic describes how to create a filtered index in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. Un índice filtrado es un índice no clúster optimizado, especialmente indicado para atender consultas que realizan selecciones a partir un subconjunto bien definido de datos.A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. Utiliza un predicado de filtro para indizar una parte de las filas de la tabla.It uses a filter predicate to index a portion of rows in the table. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, así como reducir los costos de almacenamiento y mantenimiento del índice en comparación con los índices de tabla completa.A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

Los índices filtrados pueden proporcionar las siguientes ventajas respecto a los índices de tabla completa:Filtered indexes can provide the following advantages over full-table indexes:

  • Mejor rendimiento de las consultas y mayor calidad del planImproved query performance and plan quality

    Un índice filtrado bien diseñado mejora el rendimiento de las consultas y la calidad del plan de ejecución porque es menor que un índice no clúster de tabla completa y tiene estadísticas filtradas.A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. Las estadísticas filtradas son más precisas que las de tabla completa porque corresponden solamente a las filas del índice filtrado.The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • Menor costo de mantenimiento de índicesReduced index maintenance costs

    El mantenimiento de un índice se realiza únicamente cuando las instrucciones de lenguaje de manipulación de datos (DML) afectan a los datos en el índice.An index is maintained only when data manipulation language (DML) statements affect the data in the index. Un índice filtrado reduce los costos de mantenimiento del índice en comparación con un índice no clúster de tabla completa, ya que es menor y el mantenimiento se realiza únicamente cuando cambian los datos del índice.A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is changed. Se puede disponer de una gran cantidad de índices filtrados, sobre todo cuando contienen datos que cambian con poca frecuencia.It is possible to have a large number of filtered indexes, especially when they contain data that is changed infrequently. De igual forma, si un índice filtrado contiene únicamente datos que se modifican a menudo, el tamaño menor del índice reduce el costo de actualización de las estadísticas.Similarly, if a filtered index contains only the frequently modified data, the smaller size of the index reduces the cost of updating the statistics.

  • Costos reducidos de almacenamiento de índicesReduced index storage costs

    La creación de un índice filtrado puede reducir la cantidad de almacenamiento en disco de índices no clúster, cuando no sea necesario un índice de tabla completa.Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. Puede reemplazar un índice no clúster de tabla completa con varios índices filtrados sin aumentar de forma considerable los requisitos de almacenamiento.You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

En este temaIn This Topic

Antes de comenzarBefore You Begin

Consideraciones de diseñoDesign Considerations

  • Cuando una columna solamente tiene un número pequeño de valores pertinentes para las consultas, puede crear un índice filtrado en el subconjunto de valores.When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. Por ejemplo, cuando los valores en una columna son principalmente NULL y la consulta solamente selecciona entre valores distintos de NULL, puede crear un índice filtrado para las filas de datos distintos de NULL.For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. El índice resultante será menor y tendrá costos de mantenimiento más reducidos que los de un índice no clúster de tabla completa definido en las mismas columnas de clave.The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

  • Cuando una tabla tiene filas de datos heterogéneos, se puede crear un índice filtrado para una o varias categorías de datos.When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data. Esto puede mejorar el rendimiento de las consultas de estas filas de datos estrechando el foco de una consulta a un área concreta de la tabla.This can improve the performance of queries on these data rows by narrowing the focus of a query to a specific area of the table. También en este caso, el índice resultante será menor y tendrá costos de mantenimiento más reducidos que los de un índice no clúster de tabla completa.Again, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index.

Limitaciones y restriccionesLimitations and Restrictions

  • No se puede crear un índice filtrado en una vista.You cannot create a filtered index on a view. Sin embargo, el optimizador de consultas puede aprovechar un índice filtrado definido en una tabla a la que se hace referencia en una vista.However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. El optimizador de consultas tiene en cuenta un índice filtrado para una consulta que selecciona en una vista si los resultados de la consulta serán correctos.The query optimizer considers a filtered index for a query that selects from a view if the query results will be correct.

  • No se puede crear un índice filtrado en una tabla cuando la columna a la que se obtiene acceso en la expresión de filtro es de un tipo de datos CLR.You cannot create a filtered index on a table when the column accessed in the filter expression is of a CLR data type.

  • Los índices filtrados presentan las ventajas siguientes con respecto a las vistas indizadas:Filtered indexes have the following advantages over indexed views:

    • Menor costo de mantenimiento de índices.Reduced index maintenance costs. Por ejemplo, el procesador de consultas utiliza menos recursos de CPU para actualizar un índice filtrado que una vista indizada.For example, the query processor uses fewer CPU resources to update a filtered index than an indexed view.

    • Calidad del plan mejorada.Improved plan quality. Por ejemplo, durante la compilación de la consulta, el optimizador de consultas considera la posibilidad de usar un índice filtrado en más situaciones que la vista indizada equivalente.For example, during query compilation, the query optimizer considers using a filtered index in more situations than the equivalent indexed view.

    • El índice en línea se vuelve a generar.Online index rebuilds. Puede volver a generar los índices filtrados mientras están disponibles para las consultas.You can rebuild filtered indexes while they are available for queries. Esto no es posible en el caso de las vistas indizadas.Online index rebuilds are not supported for indexed views. Para obtener más información, vea la opción REBUILD de ALTER INDEX (Transact-SQL).For more information, see the REBUILD option for ALTER INDEX (Transact-SQL).

    • Índices no únicos.Non-unique indexes. Los índices filtrados pueden ser no únicos, mientras que las vistas indizadas deben ser únicas.Filtered indexes can be non-unique, whereas indexed views must be unique.

  • Los índices filtrados se definen en una tabla y solamente admiten operadores de comparación simples.Filtered indexes are defined on one table and only support simple comparison operators. Cuando necesite una expresión de filtro que haga referencia a varias tablas o que tenga lógica compleja, deberá crear una vista.If you need a filter expression that references multiple tables or has complex logic, you should create a view.

  • Una columna de la expresión del índice filtrado no tiene por qué ser una columna incluida o de clave en la definición del índice filtrado cuando la expresión del índice filtrado es equivalente al predicado de la consulta y la consulta no devuelve la columna de la expresión del índice filtrado con los resultados de la consulta.A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results.

  • Una columna de la expresión del índice filtrado debe ser una columna incluida o de clave de la definición del índice filtrado cuando el predicado de la consulta usa la columna en una comparación no equivalente a la expresión del índice filtrado.A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.

  • Una columna de la expresión del índice filtrado debe ser una columna incluida o de clave en la definición del índice filtrado si la columna está en el conjunto de resultados de la consulta.A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.

  • La clave de índice clúster de la tabla no tiene por qué ser una columna incluida o de clave de la definición del índice filtrado.The clustered index key of the table does not need to be a key or included column in the filtered index definition. La clave de índice cluster se incluye de forma automática en todos los índices no clúster, incluidos los índices filtrados.The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

  • Si el operador de comparación especificado en la expresión del índice filtrado del índice filtrado produce una conversión de datos implícita o explícita, se producirá un error cuando la conversión se realice en el lado izquierdo de un operador de comparación.If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. Una posible solución es escribir la expresión del índice filtrado con el operador de conversión de datos (CAST o CONVERT) en el lado derecho del operador de comparación.A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

  • Revise las opciones SET necesarias para la creación del índice filtrado en la sintaxis CREATE INDEX (Transact-SQL).Review the required SET options for filtered index creation in CREATE INDEX (Transact-SQL) syntax

SeguridadSecurity

PermisosPermissions

Requiere el permiso ALTER en la tabla o la vista.Requires ALTER permission on the table or view. El usuario debe ser miembro del rol fijo de servidor sysadmin o de los roles fijos de base de datos db_ddladmin y db_owner .User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles. Para modificar la expresión de índice filtrado, utilice CREATE INDEX WITH DROP_EXISTING.To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.

Uso de SQL Server Management StudioUsing SQL Server Management Studio

Para crear un índice filtradoTo create a filtered index

  1. En el Explorador de objetos, haga clic en el signo más para expandir la base de datos que contiene la tabla en la que desea crear un índice filtrado.In Object Explorer, click the plus sign to expand the database that contains the table on which you want to create a filtered index.

  2. Haga clic en el signo más para expandir la carpeta Tablas .Click the plus sign to expand the Tables folder.

  3. Haga clic en el signo más para expandir la tabla en la que desea crear un índice filtrado.Click the plus sign to expand the table on which you want to create a filtered index.

  4. Haga clic con el botón derecho en la carpeta Índices, seleccione Nuevo índice y, luego, Índice no agrupado... .Right-click the Indexes folder, point to New Index, and select Non-Clustered Index....

  5. En el cuadro de diálogo Nuevo índice , en la página General , escriba el nombre del nuevo índice en el cuadro Nombre de índice .In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

  6. Debajo de Columnas de clave de índice, haga clic en Agregar... .Under Index key columns, click Add....

  7. En el cuadro de diálogo Seleccionar columnas denombre_tabla, active las casillas de las columnas de tabla que se van a agregar al índice único.In the Select Columns fromtable_name dialog box, select the check box or check boxes of the table column or columns to be added to the unique index.

  8. Haga clic en Aceptar.Click OK.

  9. En la página Filtro, debajo de Expresión de filtro, escriba la expresión SQL que usará para crear el índice filtrado.On the Filter page, under Filter Expression, enter SQL expression that you'll use to create the filtered index.

  10. Haga clic en Aceptar.Click OK.

Le escuchamos: Si encuentra algo obsoleto o incorrecto en este artículo, como un paso o un ejemplo de código, indíquenoslo.We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. Puede hacer clic en el botón Esta página situado en la sección Comentarios en la parte inferior de esta página.You can click the This page button in the Feedback section at the bottom of this page. Leemos todos los elementos de comentarios sobre SQL, normalmente el día siguiente.We read every item of feedback about SQL, typically the next day. Gracias.Thanks.

Usar Transact-SQLUsing Transact-SQL

Para crear un índice filtradoTo create a filtered index

  1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datosDatabase Engine.In Object Explorer, connect to an instance of Motor de base de datosDatabase Engine.

  2. En la barra de Estándar, haga clic en Nueva consulta.On the Standard bar, click New Query.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    -- Looks for an existing filtered index named "FIBillOfMaterialsWithEndDate"  
    -- and deletes it from the table Production.BillOfMaterials if found.   
    IF EXISTS (SELECT name FROM sys.indexes  
        WHERE name = N'FIBillOfMaterialsWithEndDate'  
        AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))  
    DROP INDEX FIBillOfMaterialsWithEndDate  
        ON Production.BillOfMaterials  
    GO  
    -- Creates a filtered index "FIBillOfMaterialsWithEndDate"  
    -- on the table Production.BillOfMaterials   
    -- using the columms ComponentID and StartDate.  
    
    CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
        ON Production.BillOfMaterials (ComponentID, StartDate)  
        WHERE EndDate IS NOT NULL ;  
    GO  
    

    El índice filtrado anterior es válido para la consulta siguiente.The filtered index above is valid for the following query. Puede mostrar el plan de ejecución de consultas para determinar si el optimizador de consultas ha utilizado el índice filtrado.You can display the query execution plan to determine if the query optimizer used the filtered index.

    USE AdventureWorks2012;  
    GO  
    SELECT ProductAssemblyID, ComponentID, StartDate   
    FROM Production.BillOfMaterials  
    WHERE EndDate IS NOT NULL   
        AND ComponentID = 5   
        AND StartDate > '01/01/2008' ;  
    GO  
    

Para asegurarse de que un índice filtrado se usa en una consulta SQLTo ensure that a filtered index is used in a SQL query

  1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datosDatabase Engine.In Object Explorer, connect to an instance of Motor de base de datosDatabase Engine.

  2. En la barra de Estándar, haga clic en Nueva consulta.On the Standard bar, click New Query.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
        WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )   
    WHERE EndDate IN ('20000825', '20000908', '20000918');   
    GO  
    

Para obtener más información, vea CREATE INDEX(Transact-SQL).For more information, see CREATE INDEX (Transact-SQL).