Crear vistas indizadasCreate Indexed Views

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

En este artículo se describe cómo crear índices en una vista.This article describes how to create indexes on a view. El primer índice creado en una vista debe ser un índice clúster único.The first index created on a view must be a unique clustered index. Después de haber creado el índice clúster único, puede crear más índices no clúster.After the unique clustered index has been created, you can create more nonclustered indexes. La creación de un índice clúster único en una vista mejora el rendimiento de la consulta porque la vista se almacena en la base de datos de la misma manera que se almacena una tabla con un índice clúster.Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. El optimizador de consultas puede utilizar vistas indizadas para acelerar la ejecución de las consultas.The query optimizer may use indexed views to speed up the query execution. No es necesario hacer referencia a la vista en la consulta para que el optimizador tenga en cuenta esa vista al hacer una sustitución.The view does not have to be referenced in the query for the optimizer to consider that view for a substitution.

Antes de comenzarBefore You Begin

Para crear una vista indizada, es necesario seguir los pasos descritos a continuación, que son fundamentales para la correcta implementación de la vista indizada:The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:

  1. Compruebe que las opciones SET sean correctas para todas las tablas existentes a las que se hará referencia en la vista.Verify the SET options are correct for all existing tables that will be referenced in the view.
  2. Compruebe que las opciones SET de la sesión estén establecidas correctamente antes de crear cualquier tabla y la vista.Verify that the SET options for the session are set correctly before you create any tables and the view.
  3. Compruebe que la definición de vista sea determinista.Verify that the view definition is deterministic.
  4. Cree la vista con la opción WITH SCHEMABINDING.Create the view by using the WITH SCHEMABINDING option.
  5. Cree el índice clúster único en la vista.Create the unique clustered index on the view.

Importante

Al ejecutar DML1 en una tabla a la que hace referencia un gran número de vistas indexadas, o bien menos vistas indexadas pero muy complejas, dichas vistas indexadas a las que se hace referencia deberán actualizarse igualmente.When executing DML1 on a table referenced by a large number of indexed views, or fewer but very complex indexed views, those referenced indexed views will have to be updated as well. Como resultado, el rendimiento de la consulta DML se puede degradar notablemente o, en algunos casos, puede que tampoco se genere un plan de consulta.As a result, DML query performance can degrade significantly, or in some cases, a query plan cannot even be produced. En estos casos, pruebe las consultas DML antes de usarlas en entornos de producción, analice el plan de consulta y ajuste o simplifique la instrucción DML.In such scenarios, test your DML queries before production use, analyze the query plan and tune/simplify the DML statement.

1 Como las operaciones UPDATE, DELETE o INSERT.1 Such as UPDATE, DELETE or INSERT operations.

Opciones SET requeridas para vistas indizadasRequired SET Options for Indexed Views

La evaluación de la misma expresión puede producir resultados diferentes en el Motor de base de datosDatabase Engine cuando hay diferentes opciones SET activas cuando se ejecuta la consulta.Evaluating the same expression can produce different results in the Motor de base de datosDatabase Engine when different SET options are active when the query is executed. Por ejemplo, después de establecer la opción SET CONCAT_NULL_YIELDS_NULL en ON, la expresión 'abc' + NULL devuelve el valor NULL NULL,For example, after the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc' + NULL returns the value NULL. aunque al establecer CONCAT_NULL_YIELDS_NULL en OFF, la misma expresión genera 'abc'.However, after CONCAT_NULL_YIELDS_NULL is set to OFF, the same expression produces 'abc'.

Para asegurar el correcto mantenimiento de las vistas y la generación de resultados coherentes, las vistas indizadas requieren valores fijos para varias opciones SET.To make sure that the views can be maintained correctly and return consistent results, indexed views require fixed values for several SET options. Las opciones SET de la tabla siguiente se deben establecer en los valores que se muestran en la columna Valor requerido siempre que se den las siguientes condiciones:The SET options in the following table must be set to the values shown in the Required Value column whenever the following conditions occur:

  • Se crean la vista y los índices siguientes en la vista.The view and subsequent indexes on the view are created.
  • Las tablas base a las que se hace referencia en la vista cuando se crea la tabla.The base tables referenced in the view at the time the table is created.
  • Se realiza una operación de inserción, actualización o eliminación en cualquier tabla que participa en la vista indizada.There is any insert, update, or delete operation performed on any table that participates in the indexed view. Este requisito incluye operaciones como copia masiva, replicación y consultas distribuidas.This requirement includes operations such as bulk copy, replication, and distributed queries.
  • El optimizador de consultas utiliza la vista indizada para producir el plan de consulta.The indexed view is used by the query optimizer to produce the query plan.
Opciones SETSET options Valor requeridoRequired value Valor de servidor predeterminadoDefault server value Valor predeterminadoDefault

Valor de OLE DB y ODBCOLE DB and ODBC value
Valor predeterminadoDefault

predeterminadoDB-Library value
ANSI_NULLSANSI_NULLS ONON ONON ONON OFFOFF
ANSI_PADDINGANSI_PADDING ONON ONON ONON OFFOFF
ANSI_WARNINGS1ANSI_WARNINGS1 ONON ONON ONON OFFOFF
ARITHABORTARITHABORT ONON ONON OFFOFF OFFOFF
CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL ONON ONON ONON OFFOFF
NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF OFFOFF OFFOFF OFFOFF
QUOTED_IDENTIFIERQUOTED_IDENTIFIER ONON ONON ONON OFFOFF
         

1 Si se establece ANSI_WARNINGS en ON, ARITHABORT se establece implícitamente en ON.1 Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON.

Si usa una conexión de servidor OLE DB u ODBC, el único valor que se debe modificar es la configuración de ARITHABORT.If you are using an OLE DB or ODBC server connection, the only value that must be modified is the ARITHABORT setting. Todos los valores de DB-Library se deben establecer correctamente en el nivel de servidor mediante sp_configure o desde la aplicación a través del comando SET.All DB-Library values must be set correctly either at the server level by using sp_configure or from the application by using the SET command.

Importante

Se recomienda encarecidamente que establezca la opción de usuario ARITHABORT en ON en todo el servidor en cuanto se cree la primera vista indizada o el primer índice en una columna calculada en cualquier base de datos del servidor.We strongly recommend that you set the ARITHABORT user option to ON server-wide as soon as the first indexed view or index on a computed column is created in any database on the server.

Vistas deterministasDeterministic Views

La definición de una vista indizada debe ser determinista.The definition of an indexed view must be deterministic. Una vista es determinista si todas las expresiones de la lista de selección y las cláusulas WHERE y GROUP BY son deterministas.A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic. Las expresiones deterministas siempre devuelven el mismo resultado cada vez que son evaluadas con un conjunto específico de valores de entrada.Deterministic expressions always return the same result any time they are evaluated with a specific set of input values. Solo las funciones deterministas pueden participar en expresiones deterministas.Only deterministic functions can participate in deterministic expressions. Por ejemplo, la función DATEADD es determinista porque siempre devuelve el mismo resultado para cualquier conjunto dado de valores de argumento para sus tres parámetros.For example, the DATEADD function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE no es determinista porque siempre se invoca con el mismo argumento, pero el valor que devuelve varía cada vez que se ejecuta.GETDATE is not deterministic because it is always invoked with the same argument, but the value it returns changes each time it is executed.

Para determinar si una columna de la vista es determinista, use la propiedad IsDeterministic de la función COLUMNPROPERTY .To determine whether a view column is deterministic, use the IsDeterministic property of the COLUMNPROPERTY function. Para determinar si una columna determinista de una vista con enlaces de esquema es precisa, use la propiedad IsPrecise de la función COLUMNPROPERTY.To determine if a deterministic column in a view with schema binding is precise, use the IsPrecise property of the COLUMNPROPERTY function. COLUMNPROPERTY devuelve 1 si el valor es TRUE, 0 si es FALSE y NULL en entradas no válidas.COLUMNPROPERTY returns 1 if TRUE, 0 if FALSE, and NULL for input that is not valid. Esto significa que la columna no es determinista ni precisa.This means the column is not deterministic or not precise.

Aun cuando una expresión sea determinista, si contiene expresiones de tipo float, es posible que un resultado exacto dependa de la arquitectura de procesador o de la versión de microcódigo.Even if an expression is deterministic, if it contains float expressions, the exact result may depend on the processor architecture or version of microcode. Para asegurar la integridad de los datos, estas expresiones solo pueden participar como columnas que no son de clave de vistas indizadas.To ensure data integrity, such expressions can participate only as non-key columns of indexed views. Las expresiones deterministas que no contienen expresiones flotantes se denominan expresiones precisas.Deterministic expressions that do not contain float expressions are called precise. Solo las expresiones deterministas precisas pueden participar en columnas de clave y en cláusulas WHERE o GROUP BY de vistas indizadas.Only precise deterministic expressions can participate in key columns and in WHERE or GROUP BY clauses of indexed views.

Requisitos adicionalesAdditional Requirements

Además de las opciones SET y los requisitos de funciones deterministas, se deben cumplir los requisitos siguientes:In addition to the SET options and deterministic function requirements, the following requirements must be met:

  • El usuario que ejecuta CREATE INDEX debe ser el propietario de la vista.The user that executes CREATE INDEX must be the owner of the view.

  • Cuando crea el índice, la opción IGNORE_DUP_KEY debe establecerse en OFF (configuración predeterminada).When you create the index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).

  • En la definición de vista, se debe hacer referencia a las tablas mediante nombres de dos partes, esquema . nombretabla .Tables must be referenced by two-part names, schema.tablename in the view definition.

  • Las funciones definidas por el usuario a las que se hace referencia en la vista se deben crear con la opción WITH SCHEMABINDING.User-defined functions referenced in the view must be created by using the WITH SCHEMABINDING option.

  • Para hacer referencia a las funciones definidas por el usuario a las que se hace referencia en la vista, se deben usar nombres de dos partes, <schema> . <function> .Any user-defined functions referenced in the view must be referenced by two-part names, <schema>.<function>.

  • La propiedad de acceso a datos de una función definida por el usuario debe ser NO SQL y la propiedad de acceso externo debe ser NO.The data access property of a user-defined function must be NO SQL, and external access property must be NO.

  • Las funciones de Common Language Runtime (CLR) pueden aparecer en la lista de selección de la vista, pero no pueden formar parte de la definición de la clave de índice clúster.Common language runtime (CLR) functions can appear in the select list of the view, but cannot be part of the definition of the clustered index key. Las funciones CLR no pueden aparecer en la cláusula WHERE de la vista ni en la cláusula ON de una operación JOIN en la vista.CLR functions cannot appear in the WHERE clause of the view or the ON clause of a JOIN operation in the view.

  • Los métodos y las funciones CLR de tipos definidos por el usuario CLR utilizados en la definición de la vista deben establecer las propiedades según se indica en la tabla siguiente.CLR functions and methods of CLR user-defined types used in the view definition must have the properties set as shown in the following table.

    PropiedadProperty NotaNote
    DETERMINISTIC = TRUEDETERMINISTIC = TRUE Debe declararse de forma explícita como un atributo del método de Microsoft .NET Framework.Must be declared explicitly as an attribute of the Microsoft .NET Framework method.
    PRECISE = TRUEPRECISE = TRUE Debe declararse de forma explícita como un atributo del método de .NET Framework.Must be declared explicitly as an attribute of the .NET Framework method.
    DATA ACCESS = NO SQLDATA ACCESS = NO SQL Se determina mediante la definición del atributo DataAccess como DataAccessKind.None y del atributo SystemDataAccess como SystemDataAccessKind.None.Determined by setting DataAccess attribute to DataAccessKind.None and SystemDataAccess attribute to SystemDataAccessKind.None.
    EXTERNAL ACCESS = NOEXTERNAL ACCESS = NO Esta propiedad tiene el valor predeterminado NO en rutinas CLR.This property defaults to NO for CLR routines.
       
  • La vista se debe crear mediante la opción WITH SCHEMABINDING.The view must be created by using the WITH SCHEMABINDING option.

  • La vista solo debe hacer referencia a tablas base que estén en la misma base de datos que la vista.The view must reference only base tables that are in the same database as the view. La vista no puede hacer referencia a otras vistas.The view cannot reference other views.

  • La instrucción SELECT de la definición de vista no debe contener los siguientes elementos de Transact-SQL:The SELECT statement in the view definition must not contain the following Transact-SQL elements:

    COUNT Funciones de ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET y OPENXML)ROWSET functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML) Combinaciones OUTER (LEFT, RIGHT o FULL)OUTER joins (LEFT, RIGHT, or FULL)
    Tabla derivada (definida mediante una instrucción SELECT en la cláusula FROM)Derived table (defined by specifying a SELECT statement in the FROM clause) AutocombinacionesSelf-joins Especificación de columnas mediante SELECT * o SELECT <table_name>.*Specifying columns by using SELECT * or SELECT <table_name>.*
    DISTINCT STDEV, STDEVP, VAR, VARP o AVGSTDEV, STDEVP, VAR, VARP, or AVG Expresión de tabla común (CTE)Common table expression (CTE)
    Columnas float1, text, ntext, image, XML o filestreamfloat1, text, ntext, image, XML, or filestream columns SubconsultaSubquery Cláusula OVER, que incluye funciones de categoría o de agregadoOVER clause, which includes ranking or aggregate window functions
    Predicados de texto completo (CONTAINS, FREETEXT)Full-text predicates (CONTAINS, FREETEXT) Función SUM que hace referencia a una expresión que acepta valores NULLSUM function that references a nullable expression ORDER BY
    Función de agregado definida por el usuario CLRCLR user-defined aggregate function TOP Operadores CUBE, ROLLUP o GROUPING SETSCUBE, ROLLUP, or GROUPING SETS operators
    MIN, MAXMIN, MAX Operadores UNION, EXCEPT o INTERSECTUNION, EXCEPT, or INTERSECT operators TABLESAMPLE
    Variables de tablaTable variables OUTER APPLY o CROSS APPLYOUTER APPLY or CROSS APPLY PIVOT, UNPIVOTPIVOT, UNPIVOT
    Conjuntos de columnas dispersasSparse column sets Funciones insertadas (TVF) o con valores de tabla de múltiples instrucciones (MSTVF)Inline (TVF) or multi-statement table-valued functions (MSTVF) OFFSET
    CHECKSUM_AGG
         

    1 La vista indexada puede contener columnas float, aunque no se pueden incluir en la clave de índice agrupado.1 The indexed view can contain float columns; however, such columns cannot be included in the clustered index key.

  • Si GROUP BY está presente, la definición de VIEW debe contener COUNT_BIG(*), pero no HAVING.If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. Estas restricciones GROUP BY solo se pueden aplicar a la definición de vista indizada.These GROUP BY restrictions are applicable only to the indexed view definition. Una consulta puede usar una vista indizada en su plan de ejecución aun cuando no satisfaga estas restricciones GROUP BY.A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

  • Si la definición de vista contiene una cláusula GROUP BY, la clave del índice clúster único solo puede hacer referencia a las columnas especificadas en la cláusula GROUP BY.If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

Importante

No se admiten vistas indexadas con consultas temporales (las consultas que usan la cláusula FOR SYSTEM_TIME).Indexed views are not supported on top of temporal queries (queries that use FOR SYSTEM_TIME clause).

RecomendacionesRecommendations

Cuando haga referencia a los literales de cadena datetime y smalldatetime de las vistas indizadas, se recomienda convertir explícitamente el literal al tipo de datos deseado mediante un estilo de formato de fecha determinista.When you refer to datetime and smalldatetime string literals in indexed views, we recommend that you explicitly convert the literal to the date type you want by using a deterministic date format style. Para obtener una lista de los estilos de formato de fecha deterministas, vea CAST y CONVERT (Transact-SQL).For a list of the date format styles that are deterministic, see CAST and CONVERT (Transact-SQL). Para obtener más información sobre las expresiones deterministas y no deterministas, consulte la sección Consideraciones de esta página.For more information about deterministic and nondeterministic expressions, see the Considerations section in this page.

Al ejecutar DML (como UPDATE, DELETE or INSERT) en una tabla a la que hace referencia un gran número de vistas indexadas, o menos vistas indexadas pero muy complejas, dichas vistas indexadas deberán actualizarse igualmente durante la ejecución de DML.When you execute DML (such as UPDATE, DELETE or INSERT) on a table referenced by a large number of indexed views, or fewer but very complex indexed views, those indexed views will have to be updated as well during DML execution. Como resultado, el rendimiento de la consulta DML se puede degradar notablemente o, en algunos casos, puede que tampoco se genere un plan de consulta.As a result, DML query performance may degrade significantly, or in some cases, a query plan cannot even be produced. En estos casos, pruebe las consultas DML antes de usarlas en entornos de producción, analice el plan de consulta y ajuste o simplifique la instrucción DML.In such scenarios, test your DML queries before production use, analyze the query plan and tune/simplify the DML statement.

ConsideracionesConsiderations

La configuración de la opción large_value_types_out_of_row de las columnas de una vista indexada se hereda de la configuración de la columna correspondiente de la tabla base.The setting of the large_value_types_out_of_row option of columns in an indexed view is inherited from the setting of the corresponding column in the base table. Este valor se establece mediante sp_tableoption.This value is set by using sp_tableoption. La configuración predeterminada de las columnas formadas a partir de expresiones es 0.The default setting for columns formed from expressions is 0. Esto significa que los tipos de valores grandes se almacenan de forma consecutiva.This means that large value types are stored in-row.

En una tabla con particiones se pueden crear vistas indizadas, en las que a su vez se pueden crear particiones.Indexed views can be created on a partitioned table, and can themselves be partitioned.

Para evitar que Motor de base de datosDatabase Engine use vistas indexadas, incluya la sugerencia OPTION (EXPAND VIEWS) en la consulta.To prevent the Motor de base de datosDatabase Engine from using indexed views, include the OPTION (EXPAND VIEWS) hint on the query. Además, si alguna de las opciones enumeradas no está establecida correctamente, el optimizador no utilizará los índices en las vistas.Also, if any of the listed options are incorrectly set, this will prevent the optimizer from using the indexes on the views. Para obtener más información sobre la sugerencia OPTION (EXPAND VIEWS), vea SELECT (Transact-SQL).For more information about the OPTION (EXPAND VIEWS) hint, see SELECT (Transact-SQL).

Si se quita la vista, todos sus índices se quitan.All indexes on a view are dropped when the view is dropped. Todos los índices no clúster y las estadísticas creadas automáticamente de una vista se quitan si se quita el índice clúster.All nonclustered indexes and auto-created statistics on the view are dropped when the clustered index is dropped. Las estadísticas creadas por el usuario de la vista se conservan.User-created statistics on the view are maintained. Los índices no clúster se pueden quitar individualmente.Nonclustered indexes can be individually dropped. Quitar el índice clúster de la vista quita el conjunto de resultados almacenado; el optimizador vuelve a procesar la vista como una vista estándar.Dropping the clustered index on the view removes the stored result set, and the optimizer returns to processing the view like a standard view.

Los índices de las tablas y las vistas se pueden deshabilitar.Indexes on tables and views can be disabled. Cuando se deshabilita un índice clúster de una tabla, también se deshabilitan los índices de las vistas asociadas a la tabla.When a clustered index on a table is disabled, indexes on views associated with the table are also disabled.

Las expresiones que implican la conversión implícita de cadenas de caracteres a datetime o smalldatetime se consideran no deterministas.Expressions that involve implicit conversion of character strings to datetime or smalldatetime are considered nondeterministic. Para obtener más información, vea Conversión no determinista de las cadenas de fecha literales en valores DATE.For more information, see Nondeterministic conversion of literal date strings into DATE values.

SeguridadSecurity

PermisosPermissions

Se necesita el permiso CREATE VIEW en la base de datos y el permiso ALTER en el esquema en que se crea la vista.Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

Usar Transact-SQLUsing Transact-SQL

Para crear una vista indizadaTo create an indexed view

Este ejemplo siguiente crea una vista y un índice en esa vista.The following example creates a view and an index on that view. Se incluyen dos consultas que usan la vista indexada en la base de datos de AdventureWorks.Two queries are included that use the indexed view in the AdventureWorks database.

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
   QUOTED_IDENTIFIER, ANSI_NULLS ON;
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
   DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
   WITH SCHEMABINDING
   AS  
      SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
   ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev,
   OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o
   ON od.SalesOrderID=o.SalesOrderID
      AND ProductID BETWEEN 700 and 800
      AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
   GROUP BY OrderDate, ProductID
   ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o
   ON od.SalesOrderID=o.SalesOrderID
      AND DATEPART(mm,OrderDate)= 3
      AND DATEPART(yy,OrderDate) = 2002
    GROUP BY OrderDate
    ORDER BY OrderDate ASC;

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

Consulte tambiénSee Also