IMPLICATIONS, LIMITATIONS AND RECOMMENDATIONS FOR FUTURE RESEARCH
5.2. Practical implications
5.2.1 Increasing work engagement
Después de completar este capítulo, usted será capaz de Crear una vista
Modificar datos a través de una vista Crear una vista indexada
En el capítulo 8, "Recuperación de datos", y el Capítulo 9, "Recuperación avanzada de datos", aprendió sobre las diversas formas en que una declaración SELECT se podía construir para recuperar datos. Aunque algunas sentencias SELECT son relativamente simples, otras sentencias SELECT pueden ser extremadamente complejas, uniéndose muchas tablas con múltiples niveles de combinaciones y traducción de datos.
SQL Server tiene un objeto llamado “vista” view, que le permite almacenar declaraciones SELECT complejas en su base de datos. En esta lección, aprenderá sobre cómo crear vistas, modificar datos mediante una vista, e indexar una vista a fin de mejorar el rendimiento de la consulta.
Creación de una vista
Una vista es simplemente una declaración SELECT a la que se le ha dado un nombre y almacenado en una base de datos.
La principal ventaja de una vista es que una vez creada actúa como una tabla para cualquier otra declaración SELECT que se desea escribir.
La sintaxis genérica para crear una vista es:
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ]
AS select_statement [ WITH CHECK OPTION ] [ ; ]
Aunque una vista es un nombre almacenado para una declaración SELECT, el SELECT definido para la vista puede hacer referencia a otras tablas, vistas y funciones. La declaración SELECT no puede:
Contener las cláusulas COMPUTE o COMPUTE BY Usar la palabra clave INTO
Usar una cláusula OPTION
Hacer referencia a una tabla temporal o variable o cualquier otro tipo.
Contener una cláusula ORDER BY a menos que se especifique un operador TOP
La vista puede contener varias declaraciones SELECT, siempre y cuando usted utilice los operadores UNION o UNION ALL.
180 Parte IV Diseño de objetos de bases de datos avanzadas
En el siguiente ejercicio, creará vistas que combinan las tablas Customer, CustomerAddress, y OrderHeader juntas.
Creación de una vista
1. Ejecute el siguiente código contra la base de datos SQL2008SBS (el código está en el archivo Capítulo11\code1.sql):
CREATE VIEW Customers.CustomerOrders AS
SELECT CASE WHEN a.CompanyName IS NOT NULL THEN a.CompanyName ELSE a.FirstName + ' ' + a.LastName END CustomerName,
b.AddressLine1, b.AddressLine2, b.AddressLine3, b.City, d.StateProvinceAbbrev, e.CountryName, c.OrderDate, c.GrandTotal, c.FinalShipDate
FROM Customers.Customer a INNER JOIN Customers.CustomerAddress b ON a.CustomerID = b.CustomerID
INNER JOIN Orders.OrderHeader c ON a.CustomerID = c.CustomerID
INNER JOIN LookupTables.StateProvince d ON b.StateProvinceID = d.StateProvinceID INNER JOIN LookupTables.Country e ON b.CountryID = e.CountryID
GO
2. Ejecute la siguiente consulta para ver los resultados desde la vista que acaba de crear (El código está en el archivo Capítulo11\code1.sql):
SELECT CustomerName, AddressLine1, AddressLine2, AddressLine3, City, StateProvinceAbbrev,
CountryName, OrderDate, GrandTotal, FinalShipDate FROM Customers.CustomerOrders
GO
Sustitución de consulta
Cuando se hace referencia a una vista, SQL Server sustituye el nombre de la vista con la declaración SELECT definida por la vista, vuelve a escribir la consulta como si no hubiera referencia a la vista, y luego envía la consulta reescrita al optimizador.
Por lo tanto, aunque es posible que usted haya ejecutado:
SELECT CustomerName, AddressLine1, AddressLine2, AddressLine3, City, StateProvinceAbbrev, CountryName, OrderDate, GrandTotal, FinalShipDate
FROM Customers.CustomerOrders GO
SQL Server realmente enviará la siguiente consulta al optimizador: SELECT CASE WHEN a.CompanyName IS NOT NULL THEN a.CompanyName
ELSE a.FirstName + ' ' + a.LastName END CustomerName,
b.AddressLine1, b.AddressLine2, b.AddressLine3, b.City, d.StateProvinceAbbrev, e.CountryName, c.OrderDate, c.GrandTotal, c.FinalShipDate
FROM Customers.Customer a INNER JOIN Customers.CustomerAddress b ON a.CustomerID = b.CustomerID
Capítulo 11 Vistas
INNER JOIN Orders.OrderHeader c ON a.CustomerID = c.CustomerID
INNER JOIN LookupTables.StateProvince d ON b.StateProvinceID = d.StateProvinceID INNER JOIN LookupTables.Country e ON b.CountryID = e.CountryID
GO
181
Modificar datos mediante una vista
Puede realizar modificaciones a los datos a través de una vista, siempre y cuando se cumplen los siguientes requisitos:
La modificación de los datos debe hacer referencia exactamente a una tabla
Las columnas de la vista deben hacer referencia directa a las columnas de una tabla ❏
❏
La columna no puede ser derivada desde una agregación
La columna no puede ser calculada como el resultado de un UNION / UNION ALL, CROSS JOIN, EXCEPT o INTERSECT
La columna que se está modificando no puede estar afectada por las cláusulas DISTINCT, GROUP BY, o HAVING.
No se usa el operador TOP
Nota Si una vista no cumple con los requisitos para ser actualizable, puede crear un trigger INSTEAD OF sobre la vista. El desencadenador INSTEAD OF se ejecutará para la operación de DML que está realizando en lugar de enviar la DML a través de la vista. Usted aprenderá acerca de los disparadores en el Capítulo 14, "Triggers".
Dado que la definición de una vista puede contener una cláusula WHERE, es posible realizar una modificación a través de la vista que no está visible cuando se recuperan datos de la misma. La cláusula WITH CHECK OPTION obliga a que los únicos datos que pueden manipularse, a través de la vista, también deban poder ser recuperados cuando se realiza el SELECT desde la vista.
En el siguiente ejercicio, creará una vista que combina las tablas Employee y EmployeeAddress juntas, de tal manera que se pueden realizar modificaciones de datos.
Modificar mediante una vista
1. Ejecute el siguiente código en la base de datos SQL2008SBS (el código está en el archivo Capítulo11\code2.sql):
CREATE VIEW HumanResources.v_Employees AS
SELECT a.EmployeeID, a.FirstName, a.LastName, a.JobTitle, a.BirthDate, a.HireDate, c.AddressType, b.AddressLine1, b.AddressLine2, b.AddressLine3, b.City, d.StateProvince, e.CountryName
182 Parte IV Diseño de objetos de bases de datos avanzadas
FROM HumanResources.Employee a LEFT JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID
LEFT JOIN LookupTables.AddressType c ON b.AddressTypeID = c.AddressTypeID LEFT JOIN LookupTables.StateProvince d ON b.StateProvinceID = d.StateProvinceID LEFT JOIN LookupTables.Country e ON b.CountryID = e.CountryID
GO
2. Observe los resultados de la vista que acaba de crear, usando el siguiente SELECT (el código está en el archivo Capítulo11\code2.sql):
SELECT EmployeeID, FirstName, LastName, JobTitle, BirthDate, HireDate, AddressType, AddressLine1, AddressLine2, AddressLine3, City, StateProvince, CountryName
FROM HumanResources.v_Employees GO
3. Insertar una fila en la vista usando la siguiente declaración INSERT (el código está en el archivo Capítulo11\code2.sql):
INSERT INTO HumanResources.v_Employees (FirstName, LastName, JobTitle, BirthDate, HireDate)
VALUES('Diane','Prescott','Vice-President','4/11/1980','1/2/2008') GO
4. Observe los resultados de la instrucción INSERT ejecutando el siguiente SELECT (el código está en el archivo Capítulo11\code2.sql):
SELECT EmployeeID, FirstName, LastName, JobTitle, BirthDate, HireDate, AddressType, AddressLine1, AddressLine2, AddressLine3, City, StateProvince, CountryName
FROM HumanResources.v_Employees GO
Creación de una vista indexada
Además de realizar modificaciones de datos a través de una vista, también se puede crear un índice en una vista.
Cuando es creada una vista normal, SQL Server sólo almacena la definición de la vista, que luego es sustituida por el optimizador de consultas para las sentencias SELECT emitidas contra dicha vista.
El índice se construye con una lista de valores en una columna. Cuando se indexa una vista, SQL Server ejecuta la declaración SELECT definida por la vista, almacena el conjunto de resultados, y luego construye el índice. Cualquier DML posterior emitida contra cualquiera de las tablas definidas en la vista, causará que SQL Server actualice incrementalmente el conjunto de resultados almacenados, así como mantener el índice, si es necesario. Dado que SQL Server almacena físicamente y mantiene el conjunto de resultados, o "materializa" los datos, una vista indexada a veces es conocida como una “vista materializada”.
Las vistas indexadas tienen una larga lista de requisitos para poder ser construidas. Los requisitos para una vista indexada derivan del hecho de que los datos han de ser materializados en disco en una manera que no cambia y los datos dentro del índice también tienen que ser fijos.
Capítulo 11 Vistas 183 Algunos de los requisitos para crear una vista indexada son:
La declaración SELECT no puede hacer referencia a otras vistas Todas las funciones deben ser deterministas
No se permiten AVG, MIN, MAX, STDEV DESVESTP, VAR, y VARP El índice creado debe ser a la vez clustered y unique
ANSI_NULLS debe haber sido configurado en ON, cuando se crearon la vista y cualquiera de las tablas referenciadas en la vista.
La vista debe ser creada con la opción SCHEMABINDING
La declaración SELECT no contiene subconsultas o outer joins, EXCEPT, INTERSECT TOP, UNION, ORDER BY, DISTINCT, COMPUTE / COMPUTE BY, CROSS / OUTER APPLY, PIVOT, o UNPIVOT
Más información Todas las restricciones para la creación de una vista indexada se pueden encontrar en el Artículo de los Books Online "Creación de vistas indizadas."
El cumplimiento de los requisitos para crear una vista indexada, pueden parecer prohibitivos.
Sin embargo, la principal ventaja de una vista indexada es que los datos ya están materializadas y no tienen que ser calculados sobre la marcha como en una vista regular. Las vistas indexadas pueden proporcionar una significativa ganancia de rendimiento cuando se realizan consultas que combinan grandes volúmenes de datos en conjunto, tales como agregados. Las vistas indexadas tienen que ser mantenidas cuando se producen cambios en las tablas subyacentes, por lo que una vista indexada no debería crearse contra tablas que reciben grandes volúmenes de modificaciones de datos.
En el siguiente ejercicio, creará una vista indexada contra las tablas Archive.OrderHeader y
Archive.OrderDetail.
Crear una vista indexada
1. Ejecute el siguiente código en la base de datos SQL2008SBS (el código está en el archivo Capítulo11\code3.sql):
CREATE VIEW Archive.v_OrderItems WITH SCHEMABINDING
AS
SELECT a.OrderID, a.CustomerID, a.OrderDate, a.SubTotal, a.TaxAmount, a.ShippingAmount, a.GrandTotal, a.FinalShipDate, b.OrderDetailID, b.SKU, b.Quantity, b.UnitPrice,
b.Quantity + b.UnitPrice LineItemTotal
FROM Archive.OrderHeader a INNER JOIN Archive.OrderDetail b ON a.OrderID = b.OrderID GO
184 Parte IV Diseño de objetos de bases de datos avanzadas
2. Ahora cree el índice sobre la vista ejecutando el siguiente código (el código está en el archivo Capítulo11\code3.sql):
CREATE UNIQUE CLUSTERED INDEX iucx_orderitems ON Archive.v_OrderItems (OrderID, OrderDetailID)
GO
3. Realice un SELECT de la vista.
Sustitución de consultas
Cuando se crea un índice en una vista, el dato está materializado. Las consultas que hacen referencia a la vista indexada no sustituyen a la definición de la vista, sino que devuelven los resultados directamente desde la vista indexada. Los resultados pueden ser devueltos directamente, porque en términos de almacenamiento, la vista indexada es de hecho una tabla que mantiene el motor de almacenamiento.
En la edición Enterprise de SQL Server, la sustitución de consulta va un paso más allá cuando está presente una vista indexada. Normalmente, el optimizador seleccionará los índices creados sobre las tablas referenciadas en una consulta si se determina que un determinado índice mejorará el rendimiento de la consulta.
En la edición Enterprise de SQL Server, si el optimizador de consultas determina que los datos pueden ser recuperados de manera más eficiente a través de la vista indexada, entonces construye un plan de consulta que recupera los datos de la vista indexada en lugar de las tablas.
En la práctica anterior, se creó una vista indexada sobre las tablas Archive.OrderHeader y Archive.OrderDetail. Si se va a ejecutar la consulta siguiente, en lugar de utilizar las tablas Archive.OrderHeader y
Archive.OrderDetail, SQL Server realmente utiliza Archive.v_OrderItems para satisfacer la siguiente consulta (el
código está en el archivo Capítulo11\code4.sql):
SELECT a.OrderID, CASE WHEN a.CompanyName IS NOT NULL THEN a.CompanyName ELSE a.FirstName + ' ' + a.LastName END CustomerName,
a.OrderDate, a.SubTotal, a.TaxAmount, a.ShippingAmount, a.GrandTotal, a.FinalShipDate, b.OrderDetailID, b.SKU, b.Quantity, b.UnitPrice, b.Quantity + b.UnitPrice LineItemTotal FROM Archive.OrderHeader a INNER JOIN Archive.OrderDetail b ON a.OrderID = b.OrderID INNER JOIN Customers.Customer c ON a.CustomerID = c.CustomerID
GO O
Capítulo 11 Referencia Rápida
ParaAlmacenar una declaración SELECT en una base de datos para uso futuro Índexar una vista
Haga lo siguiente
Ejecute un comando CREATE VIEW