Capitulo 5 – Lección 4: Programación de Microsoft SQL Server con Vistas

En el capítulo 1, "Recuperación de datos", y el Capítulo 4, "Utilización de técnicas de consulta adicionales", analizamos diversas formas que pueden tener una sentencia SELECT construida para recuperar los datos. Mientras que algunas de las declaraciones SELECT que se utilizan para crear algo específico, algunas sentencias SELECT se utilizan una y otra vez dentro de su entorno. Algunas de las consultas que se vuelven a utilizar dentro de su entorno contienen una lógica de negocio compleja, así como complejos códigos T-SQL que seguramente no quieren tener que volver a crear cada vez que se necesite la consulta.
SQL Server le permite almacenar una sentencia SELECT dentro de una base de datos utilizando un objeto llamado vista. Ahora veremos como crear vistas, modificar los datos a través de una vista e índices a fin de mejorar el rendimiento de las consultas.

Creación de una vista(Más información AQUI)
Una vista no es más que una instrucción SELECT que se le ha dado un nombre y se almacena en una base de datos.
La principal ventaja de una vista es que una vez que se la crea, actúa como una tabla para cualquier otra declaración SELECT que desea escribir.
La sintaxis genérica para crear un punto de vista es:



La sentencia SELECT definida para la vista puede hacer referencia a tablas, vistas, y funciones, pero no pueden hacerlo cualquiera de los siguientes objetos:

• Contener la cláusula COMPUTE o COMPUTE BY
• Crear una tabla temporal o permanente mediante el uso de la palabra clave INTO
• Utilice una cláusula OPTION
• Referencia a una tabla temporal
• Hacer referencia a cualquier tipo de variable
• Contener una cláusula ORDER BY a menos que se especifique también un operador TOP

La vista puede contener varias instrucciones SELECT, siempre y cuando se utiliza los operadores UNION o UNION ALL .
Los atributos que se pueden especificar son ENCRYPTION,SCHEMABINDING, y VIEW_METADATA. Los atributos de ENCRYPTION y SCHEMABINDING se comportan de la misma manera como se ha discutido ya en capítulos anteriores. La opción VIEW_METADATA se utiliza al crear una vista actualizable y causa que SQL Server devuelva a los clientes aplicaciones de metadatos sobre la vista, en lugar de sobre las tablas subyacentes de la vista.

Modificar datos mediante una vista
Usted puede modificar los datos a través de una vista, siempre y cuando se cumplan los siguientes requisitos:

• La modificación de los datos debe hacer referencia exactamente a una tabla.
• Columnas de la vista debe hacer referencia a las columnas de una tabla directamente.
• La columna no pueden derivarse de un agregado.
• La columna no puede ser calculado como el resultado de un UNION / UNION ALL,CROSSJOIN, EXCEPT o INTERSECT.
• La columna que se modifica no puede ser afectado por el DISTINCT, GROUP BY, o HAVING.
• El operador TOP no es usado.

Si una vista no cumple con los requisitos para ser actualizable, puede crear un desencadenador INSTEAD OF en la vista. El desencadenador INSTEAD OF se ejecuta durante la operación DML que está realizando en lugar de enviar la LMD a través de la vista.
Debido a que la definición de una vista puede contener una cláusula WHERE, es posible hacer una modificación a través de la vista que no es visible cuando se recuperan los datos de la vista. La cláusula WITH CHECK OPTION exige que la manipulación de datos solo que puede ocurrir a través de la vista y también debe ser accesible cuando se selecciona desde el vista.

Vistas particionadas (Más Información AQUI)
Como veremos en el capítulo 6, "Técnicas para mejorar la eficiencia de las consultas," se puede implementar las particiones para dividir una tabla grande a través de las estructuras de almacenamiento. En lugar de utilizar la particiones integrada de las características de SQL Server, también puede hacerse de forma manual en una tabla grande  para hacer la descomposición de una sola tabla en varias tablas con la misma estructura. Cuando particionamos manualmente una tabla, puede llevar a todos los datos de nuevo conjuntamente con un vista.
Vistas que unifican varias tablas de la misma estructura que se hace referencia avistas como particiones.
Una vista con particiones implementa una clausula UNION ALL de las tablas miembros con la misma estructura. Una vista con particiones tiene las siguientes condiciones:

• Todas las columnas de las tablas miembro deben incluirse en la lista de selección de la vista.
• Columnas en la misma posición ordinal de cada instrucción SELECT deben ser exactamente del mismo tipo de datos y collation.
• Al menos una columna debe ser una restricción CHECK, único para cada tabla miembro, y debe estar en la misma posición ordinal de cada instrucción SELECT.
• Las restricciones deben ser únicas, de forma que no se superpongan los conjuntos de datos en cada tabla miembro.
• La misma columna no puede ser utilizado varias veces en la lista de selección.
• La columna de partición, definida por la restricción CHECK, debe ser parte de la clave primaria.
• La columna de partición no se puede calcular, no puede ser un tipo de datos timestamp, tener una restricción DEFAULT, o ser una columna de identidad.
• El mismo miembro de la tabla no puede aparecer dos veces en la definición de la vista.
• Las tablas miembro no puede tener índices en columnas calculadas.
• La clave primaria de cada tabla debe tener el mismo número de columnas para cada tabla miembro.
• Todas las tablas miembro deben tener el mismo valor de ANSI_PADDING.

Si bien esta lista puede parecer bastante restrictivo, la intención es asegurar que cada miembro de tabla dentro de una vista con particiones contiene un conjunto único de datos. Si se les permitió colocar el mismo valor de clave principal en las tablas de miembros múltiples, la vista con particiones se podría mostrar con duplicados y crear confusión dentro de las aplicaciones, y no sería capaz de resolver una actualización de una sola fila.
Una de las ventajas de hacer las particiones de una tabla de forma manual es que usted puede utilizar los recursos de hardware adicionales que no están disponibles en la función incorporada. Debido a que de forma manual una tabla de partición produce varias tablas miembro, usted puede decidir las prioridades de bases de datos SQL Server y donde se encuentra cada tabla. Al dividir las tablas miembro de una vista con particiones en las instancias de SQL Server, se crea un caso especial llamado una vista dividida distribuida.Una vista con particiones distribuida tiene mucho la misma estructura de una vista con particiones, excepto que cada miembro de tabla se hace referencia con un nombre de cuatro partes y utiliza servidores vinculados para combinar todas las tablas miembro.

Actualización de vistas particionadas: A pesar de que ambas formas de una vista con particiones utilizar una instrucción UNION ALL, puede realizar cambios a una vista con particiones. Las vistas con particiones imponen restricciones adicionales sobre la definición de la vista, así como cualquier transacción, tal como se indica en los libros de SQL Server artículo en línea AQUI

Creación de una vista indizada (Más información AQUI)
Además de hacer las modificaciones de datos a través de una vista también puede crear un índice en una vista. Sin embargo, un índice no se puede crear en una vista con particiones ya que las tablas miembro pueden extenderse a distintas bases de datos e instancias de SQL Server. Cuando una vista normal se crea, SQL Server almacena sólo la definición de la vista, que luego es sustituida por el optimizador para las sentencias SELECT emitidas en la vista.
Un índice puede ser construido con una lista de valores de una columna. Cuando se crea un índice de una vista, SQL Server ejecuta la sentencia SELECT se define en la vista,almacena el conjunto de resultados, y luego construye el índice. Cualquier DML posteriores dictadas contra cualquiera de las tablas de la vista está definida contra el resultado de SQL Server para actualizar el conjunto de resultados almacenado de forma incremental, así como mantener el índice, si es necesario. Debido a que SQL Server almacena físicamente y mantiene el conjunto de resultados, o "materializa"los datos, una vista indizada se refiere a veces como una vista materializada.
Las vistas indizadas tienen una lista muy larga de requisitos. Los requisitos para una vista indizada se derivan del hecho de que los datos tienen que ser materializado en el disco de una manera inmutable, y los datos en el índice también tiene que ser fijo.

Algunos de los requisitos para crear una vista indizada son los siguientes:

• La sentencia SELECT no puede hacer referencia a otras vistas.
• Todas las funciones deben ser deterministas.
• AVG, MIN, MAX, STDEV, STDEVP, VAR y VARP no están permitidos.
• El índice creado debe ser agrupado y único.
• ANSI_NULLS debe establecerse en ON cuando la vista y las tablas que hace referencia el vista se han creado.
• La vista se debe crear con la opción SCHEMABINDING.
• La sentencia SELECT no puede contener subconsultas, outer joins, EXCEPTO, INTERSECT, TOP, UNION, ORDER BY, DISTINCT, COMPUTE / COMPUTE BY, APLICAR LA CRUZ / EXTERIOR, PIVOT, o UNPIVOT.

El cumplimiento de los requisitos para crear una vista indizada puede parecer prohibitivo. Sin embargo, la principal ventaja de una vista indizada es que los datos ya se han materializado y no tienen que ser calculados sobre la marcha, como en las vistas regulares. Las vistas indizadas pueden proporcionar un aumento de rendimiento significativo cuando se realizan consultas que combinan grandes volúmenes de datos, por ejemplo, con los agregados. Las vistas indizadas se mantiene cuando se producen cambios en las tablas subyacentes, por lo que una vista indizada no se deben crear en tablas que reciben grandes volúmenes de modificaciones de datos.

Determinismo
Una función que devuelve el mismo valor cada vez que se llama, dado el mismos parámetros de entrada, es una función determinista. Una función que podría devolver un valor diferente cada vez que se llama, dado el mismos parámetros de entrada, es una función no determinista. Un ejemplo de una función determinista es SUBSTRING, ya que devuelve el mismo valor cada vez que los parámetros de entrada lo mismo. Ejemplos de funciones no deterministas son RAND y GETDATE porque cada uno puede devolver un valor diferente cada vez que se llama.
Estas funciones se pueden utilizar en las columnas calculadas, así como en vista. También puede crear un índice en una columna calculada, así como la indexación de un vista. Para crear un índice, los resultados de una función debe ser determinista, de manera que el conjunto de valores para el índice es fijo. Además, debido a la creación de un índice en una vista hace que los resultados de la vista que se materializa y se almacena, el índice de un vista en todas las funciones dentro de la vista deben ser deterministas.

La sustitución de consultas
Cuando una vista no materializada se le hace referencia, SQL Server sustituye el nombre de la vista con la declaración real SELECT definida para la vista, y vuelve a escribir la consulta como si no hubiera referencia a la vista de todos, y luego envía la consulta reescrita para el optimizador.
Es posible que tenga una vista con la siguiente definición:

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

A continuación, podría emitir el siguiente comando SELECT:

SELECT CustomerName, AddressLine1, AddressLine2, AddressLine3,
  City, StateProvinceAbbrev,
  CountryName, OrderDate, GrandTotal, FinalShipDate
FROM Customers.CustomerOrders
GO

Sin embargo, SQL Server en realidad presenta la siguiente consulta para el 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
    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

Cuando se crea un índice en una vista, los datos se materializa. Consultas que hacen referencia a la vista indizada no sustituyen a la definición de la vista, sino que devuelve el resultado directo de la vista indizada. Los resultados pueden ser devueltos directamente, porque en términos de almacenamiento, a la vista indizada en realidad es una tabla que mantiene el motor de almacenamiento.
En SQL Server Enterprise Edition, la sustitución de la consulta da un paso más en una vista indizada presente. Normalmente, el optimizador seleccionará los índices creados con las tablas de referencia dentro de una consulta si decide que un determinado índice mejora el rendimiento de las consultas. En SQL Server Enterprise Edition, si el optimizador determina que los datos se pueden recuperar de manera más eficiente a través de la vista indizada, luego construye un plan de consulta que hace caso omiso de las tablas de base de referencia la consulta y recupera los datos en lugar de la vista indizada en lugar de las tablas .

Resumen de la lección

• Una vista es un nombre de una instrucción SELECT almacenada en una base de datos.
• Una vista tiene que devolver un solo conjunto de resultados y no se puede hacer referencia a variables o tablas temporales.
• Puede actualizar los datos a través de una vista, siempre y cuando la modificación de datos se puede resolver en un conjunto específico de filas de una tabla subyacente.
• Si una vista no cumple con los requisitos para permitir las modificaciones de datos, puede crear un disparador INSTEAD OF para la modificación de datos en su lugar.
• Se pueden combinar varias tablas que han sido físicamente particiones mediante una instrucción UNION ALL para crear una vista con particiones.
• Una vista con particiones distribuida utiliza servidores vinculados para combinar varias tablas miembro a través de instancias de SQL Server.
  Usted puede crear un índice agrupado único en una vista para materializar el conjunto de resultados de rendimiento de las consultas mejorado.