CREATE VIEW (Transact-SQL)

Es posible modificar los datos de una tabla base subyacente mediante una vista, siempre que se cumplan las siguientes condiciones:

• Cualquier modificación, incluidas las instrucciones UPDATE, INSERT y DELETE, debe hacer referencia a las columnas de una única tabla base.

• Las columnas que se vayan a modificar en la vista deben hacer referencia directa a los datos subyacentes de las columnas de la tabla. Las columnas no se pueden obtener de otra forma, como las siguientes:

  • Una función de agregado: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR y VARP.

  • Un cálculo. La columna no se puede calcular a partir de una expresión que utilice otras columnas. Las columnas formadas mediante los operadores de conjunto UNION, UNION ALL, CROSSJOIN, EXCEPT e INTERSECT equivalen a un cálculo y tampoco son actualizables.

• Las columnas que se van a modificar no se ven afectadas por las cláusulas GROUP BY, HAVING o DISTINCT.

• No se utiliza TOP con la cláusula WITH CHECK OPTION en ningún punto de la instrucción select_statement de la vista.

Las restricciones anteriores se aplican a cualquier subconsulta de la cláusula FROM de la vista, al igual que a la propia vista. Normalmente, el Database Engine (Motor de base de datos) debe poder realizar un seguimiento sin ambigüedades de las modificaciones de la definición de la vista a una tabla base. Para obtener más información, vea Modificar datos mediante una vista.

Si las restricciones anteriores le impiden modificar datos directamente mediante una vista, considere las siguientes opciones:

• Desencadenadores INSTEAD OF

  Es posible crear desencadenadores INSTEAD OF en una vista para que sea actualizable. El desencadenador INSTEAD OF se ejecuta en lugar de la instrucción de modificación de datos en la que se define el desencadenador. Este desencadenador permite al usuario especificar el conjunto de acciones que hay que realizar para procesar la instrucción de modificación de datos. Por lo tanto, si existe un desencadenador INSTEAD OF para una vista en una instrucción de modificación de datos determinada (INSERT, UPDATE o DELETE), la vista correspondiente se puede actualizar mediante esa instrucción. Para obtener más información acerca de los desencadenadores INSTEAD OF, vea Diseñar desencadenadores INSTEAD OF.

• Vistas con particiones

  Si la vista es una vista con particiones, se puede actualizar con determinadas restricciones. Si es necesario, el Database Engine (Motor de base de datos) distingue las vistas con particiones locales como las vistas en las que todas las tablas participantes y la vista se encuentran en la misma instancia de SQL Server y las vistas con particiones distribuidas como las vistas en las que al menos una de las tablas de la vista reside en otro servidor o en uno remoto.

  Para obtener más información acerca de las vistas con particiones, vea Crear vistas con particiones.

Una vista con particiones es una vista definida por un operador UNION ALL de las tablas miembro estructuradas de la misma manera pero almacenadas en diferentes tablas de la misma instancia de SQL Server o en un grupo de instancias autónomas de servidores de SQL Server denominados servidores de bases de datos federadas.

Si diseña un esquema de partición, debe tener claro qué datos pertenecen a cada partición. Por ejemplo, los datos de la tabla Customers se distribuyen en tres tablas miembro en tres ubicaciones de servidor: Customers_33 en Server1, Customers_66 en Server2 y Customers_99 en Server3.

Una vista con particiones de Server1 se define de la siguiente forma:

--Partitioned view as defined on Server1
CREATE VIEW Customers
AS
--Select from local member table.
SELECT *
FROM CompanyData.dbo.Customers_33
UNION ALL
--Select from member table on Server2.
SELECT *
FROM Server2.CompanyData.dbo.Customers_66
UNION ALL
--Select from mmeber table on Server3.
SELECT *
FROM Server3.CompanyData.dbo.Customers_99

Normalmente, se dice que una vista tiene particiones si tiene el siguiente formato:

SELECT <select_list1>
FROM T1
UNION ALL
SELECT <select_list2>
FROM T2
UNION ALL
...
SELECT <select_listn>
FROM Tn

1. La list de selección

   • Todas las columnas de las tablas miembro deben seleccionarse en la lista de columnas de la definición de la vista.

   • Las columnas que se encuentren en la misma posición ordinal de cada select list deben ser del mismo tipo, incluidas las intercalaciones. No es suficiente que las columnas sean de tipos implícitamente convertibles, como sucede normalmente con UNION.

     Además, al menos una columna (por ejemplo <col>) debe aparecer en todas las listas de selección en la misma posición ordinal. Esta columna <col> debe definirse de tal forma que las tablas miembro T1, ..., Tn tengan restricciones CHECK C1, ..., Cn definidas en <col>, respectivamente.

     La restricción C1 definida en la tabla T1 debe tener el siguiente formato:

     C1 ::= < simple_interval > [ OR < simple_interval > OR ...]
     < simple_interval > :: = 
     < col > { < | > | <= | >= | = < value >} 
     | < col > BETWEEN < value1 > AND < value2 >
     | < col > IN ( value_list )
     | < col > { > | >= } < value1 > AND
     < col > { < | <= } < value2 >
     

   • Las restricciones deben estar definidas de manera que cualquier valor especificado de <col> pueda cumplir al menos una de las restricciones C1, ..., Cn de modo que las restricciones formen un conjunto de intervalos no combinados o que no se superpongan. La columna <col> en la que se definen las restricciones no combinadas se denomina columna de partición. Observe que la columna de partición puede tener diferentes nombres en las tablas subyacentes. Las restricciones deben estar habilitadas y ser de confianza para cumplir las condiciones mencionadas anteriormente de la columna de partición. Si las restricciones están deshabilitadas, vuelva a habilitarlas mediante la opción CHECK CONSTRAINT constraint_name de ALTER TABLE y la opción WITH CHECK para validarlas.

     En los siguientes ejemplos se muestran conjuntos válidos de restricciones:

     { [col < 10], [col between 11 and 20] , [col > 20] }
     { [col between 11 and 20], [col between 21 and 30], [col between 31 and 100] }
     

   • No se puede utilizar la misma columna varias veces en la lista de selección.

2. Columna de partición

   • La columna de partición forma parte de la restricción PRIMARY KEY de la tabla.

   • No puede ser una columna calculada, de identidad, predeterminada o timestamp.

   • Si existe más de una restricción en la misma columna de una tabla miembro, el Motor de base de datos omite todas las restricciones y no las tiene en cuenta al determinar si la vista tiene particiones. Para cumplir las condiciones de la vista con particiones, solamente debe existir una restricción de partición en la columna de partición.

   • No hay restricciones sobre la posibilidad de actualización de la columna de partición.

3. Tablas miembro o tablas subyacentes T1, ..., Tn

   • Las tablas pueden ser locales o tablas de otros equipos que ejecuten SQL Server a los que se haga referencia mediante un nombre de cuatro partes o un nombre basado en OPENDATASOURCE u OPENROWSET. La sintaxis de OPENDATASOURCE y OPENROWSET puede especificar un nombre de tabla, pero no una consulta de paso a través. Para obtener más información, vea OPENDATASOURCE (Transact-SQL) y OPENROWSET (Transact-SQL).

     Si una o más tablas miembro son remotas, la vista se denomina vista con particiones distribuida y se aplican condiciones adicionales. Se describen más adelante en esta sección.

   • La misma tabla no puede aparecer dos veces en el conjunto de tablas que se está combinando con la instrucción UNION ALL.

   • Las tablas miembro no pueden tener índices creados en columnas calculadas de la tabla.

   • Las tablas miembro deben tener todas las restricciones PRIMARY KEY en el mismo número de columnas.

   • Todas las tablas miembro de la vista deben tener el mismo valor de relleno ANSI. Éste se establece mediante la opción user options de sp_configure o la instrucción SET.

Las siguientes restricciones se aplican a instrucciones que modifican datos en vistas con particiones:

• La instrucción INSERT debe proporcionar valores para todas las columnas de la vista, incluso si las tablas miembro subyacentes tienen una restricción DEFAULT para esas columnas o si admiten valores NULL. En las columnas de la tabla miembro con definiciones DEFAULT, las instrucciones no pueden usar explícitamente la palabra clave DEFAULT.

• El valor que se va a insertar en la columna de partición debe cumplir al menos una de las restricciones subyacentes; en caso contrario, la acción de inserción provocará un error con una infracción de restricción.

• Las instrucciones UPDATE no pueden especificar la palabra clave DEFAULT como valor de la cláusula SET, aunque la columna tenga definido un valor DEFAULT en la tabla miembro correspondiente.

• Las columnas de la vista que sean columnas de identidad en una o varias tablas miembro no se pueden modificar mediante una instrucción INSERT o UPDATE.

• Si una de las tablas miembro contiene una columna timestamp, los datos no se pueden modificar mediante una instrucción INSERT o UPDATE.

• Si una de las tablas miembro contiene un desencadenador o una restricción ON UPDATE CASCADE/SET NULL/SET DEFAULT u ON DELETE CASCADE/SET NULL/SET DEFAULT, no se puede modificar la vista.

• Las acciones INSERT, UPDATE y DELETE en una vista con particiones no están permitidas si hay una autocombinación con la misma vista o con cualquiera de las tablas miembro de la instrucción.

• La importación masiva de datos a una vista con particiones no es compatible con la utilidad bcp ni con las instrucciones BULK INSERT e INSERT ... SELECT * FROM OPENROWSET(BULK...). Sin embargo, puede insertar varias filas en una vista con particiones utilizando la instrucción INSERT. Para obtener más información, vea Exportar o importar datos de forma masiva en una vista.

  [!NOTA]

  Para actualizar una vista con particiones, el usuario debe tener permisos INSERT, UPDATE y DELETE en las tablas miembro.

A las vistas con particiones distribuidas (cuando una o varias tablas miembro son remotas) se les aplican las siguientes condiciones adicionales:

• Se iniciará una transacción distribuida para garantizar la atomicidad en todos los nodos a los que afecta la actualización.

• La opción XACT_ABORT SET debe establecerse en ON para que las instrucciones INSERT, UPDATE o DELETE funcionen.

• Cualquier columna de tipo smallmoney y smalldatetime de las tablas remotas a las que se hace referencia en una vista con particiones se asigna como money y datetime, respectivamente. Por lo tanto, las columnas correspondientes (en la misma posición ordinal de la lista de selección) de las tablas locales deben ser de tipo money y datetime.

• Ningún servidor vinculado de la vista con particiones puede ser un servidor vinculado en bucle de retorno. Se trata de un servidor vinculado que apunta a la misma instancia de SQL Server.

El valor de la opción SET ROWCOUNT se pasa por alto para las acciones INSERT, UPDATE y DELETE que implican vistas con particiones y tablas remotas actualizables.

Cuando las tablas miembro y la definición de la vista con particiones están preparadas, el optimizador de consultas de SQL Server crea planes inteligentes que utilizan las consultas de forma eficaz para tener acceso a los datos de las tablas miembro. Con las definiciones de la restricción CHECK, el procesador de consultas asigna la distribución de valores clave entre las tablas miembro. Cuando un usuario emite una consulta, el procesador de consultas compara la asignación con los valores especificados en la cláusula WHERE y crea un plan de ejecución con una transferencia mínima de datos entre los servidores miembro. Por lo tanto, aunque algunas tablas miembro puedan estar ubicadas en servidores remotos, la instancia de SQL Server resuelve las consultas distribuidas de manera que la cantidad de datos distribuidos que haya que transferir sea mínima. Para obtener más información acerca de cómo SQL Server resuelve las consultas sobre vistas con particiones, vea Resolver vistas con particiones distribuidas.

Para crear vistas con particiones en tablas miembro implicadas en la replicación, deben tenerse en cuenta las consideraciones siguientes:

• Si las tablas subyacentes intervienen en la réplica de mezcla o en la replicación transaccional con suscripciones de actualización, la columna uniqueidentifier también debe incluirse en la lista de selección.

  Las acciones INSERT que se ejecutan en la vista con particiones deben proporcionar un valor NEWID() para la columna uniqueidentifier. Las acciones UPDATE en la columna uniqueidentifier deben proporcionar NEWID() como valor, puesto que no se puede usar la palabra clave DEFAULT.

• La replicación de actualizaciones que se realiza mediante la vista es igual que cuando las tablas se replican en dos bases de datos distintas: agentes de replicación diferentes dan servicio a las tablas y no se garantiza el orden de las actualizaciones.

En el ejemplo siguiente se crea una vista mediante una instrucción SELECT sencilla. Una vista sencilla resulta útil cuando se consulta con frecuencia una combinación de columnas. Los datos de esta vista provienen de las tablas HumanResources.Employee y Person.Contact de la base de datos AdventureWorks. Los datos proporcionan el nombre e información sobre la fecha de contratación de los empleados de Adventure Works Cycles. Esta vista puede crearse para la persona responsable del seguimiento de los aniversarios de trabajo pero sin concederle acceso a todos los datos de estas tablas.

USE AdventureWorks ;
GO
IF OBJECT_ID ('hiredate_view', 'V') IS NOT NULL
DROP VIEW hiredate_view ;
GO
CREATE VIEW hiredate_view
AS 
SELECT c.FirstName, c.LastName, e.EmployeeID, e.HireDate
FROM HumanResources.Employee e JOIN Person.Contact c on e.ContactID = c.ContactID ;
GO

En el siguiente ejemplo se utiliza la opción WITH ENCRYPTION y se muestran columnas calculadas, columnas con el nombre cambiado y varias columnas.

USE AdventureWorks ;
GO
IF OBJECT_ID ('Purchasing.PurchaseOrderReject', 'V') IS NOT NULL
    DROP VIEW Purchasing.PurchaseOrderReject ;
GO
CREATE VIEW Purchasing.PurchaseOrderReject
WITH ENCRYPTION
AS
SELECT PurchaseOrderID, ReceivedQty, RejectedQty, 
    RejectedQty / ReceivedQty AS RejectRatio, DueDate
FROM Purchasing.PurchaseOrderDetail
WHERE RejectedQty / ReceivedQty > 0
AND DueDate > CONVERT(DATETIME,'20010630',101) ;
GO

En el siguiente ejemplo se muestra una vista denominada SeattleOnly que hace referencia a cinco tablas y permite modificar datos aplicados únicamente a los empleados que viven en Seattle.

USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.SeattleOnly', 'V') IS NOT NULL
    DROP VIEW dbo.SeattleOnly ;
GO
CREATE VIEW dbo.SeattleOnly
AS
SELECT c.LastName, c.FirstName, a.City, s.StateProvinceCode
FROM Person.Contact AS c 
JOIN HumanResources.Employee AS e ON c.ContactID = e.ContactID
JOIN HumanResources.EmployeeAddress AS ea ON e.EmployeeID = ea.EmployeeID
JOIN Person.Address AS a ON ea.AddressID = a.AddressID
JOIN Person.StateProvince AS s ON a.StateProvinceID = s.StateProvinceID
WHERE a.City = 'Seattle'
WITH CHECK OPTION ;
GO

En el siguiente ejemplo se muestra una definición de vista que incluye una función integrada. Al utilizar funciones, es necesario especificar un nombre de columna para la columna derivada.

USE AdventureWorks ;
GO
IF OBJECT_ID ('Sales.SalesPersonPerform', 'V') IS NOT NULL
    DROP VIEW Sales.SalesPersonPerform ;
GO
CREATE VIEW Sales.SalesPersonPerform
AS
SELECT TOP 100 SalesPersonID, SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
WHERE OrderDate > CONVERT(DATETIME,'20001231',101)
GROUP BY SalesPersonID;
GO

En el siguiente ejemplo se utilizan las tablas denominadas SUPPLY1, SUPPLY2, SUPPLY3 y SUPPLY4. Estas tablas corresponden a las tablas de proveedores de cuatro oficinas ubicadas en diferentes países o regiones.

--Create the tables and insert the values.
CREATE TABLE dbo.SUPPLY1 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
supplier CHAR(50)
);
CREATE TABLE dbo.SUPPLY2 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),
supplier CHAR(50)
);
CREATE TABLE dbo.SUPPLY3 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),
supplier CHAR(50)
);
CREATE TABLE dbo.SUPPLY4 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),
supplier CHAR(50)
);
GO
INSERT dbo.SUPPLY1 VALUES ('1', 'CaliforniaCorp');
INSERT dbo.SUPPLY1 VALUES ('5', 'BraziliaLtd');
INSERT dbo.SUPPLY2 VALUES ('231', 'FarEast');
INSERT dbo.SUPPLY2 VALUES ('280', 'NZ');
INSERT dbo.SUPPLY3 VALUES ('321', 'EuroGroup');
INSERT dbo.SUPPLY3 VALUES ('442', 'UKArchip');
INSERT dbo.SUPPLY4 VALUES ('475', 'India');
INSERT dbo.SUPPLY4 VALUES ('521', 'Afrique');
GO
--Create the view that combines all supplier tables.
CREATE VIEW all_supplier_view
WITH SCHEMABINDING
AS
SELECT supplyID, supplier
FROM dbo.SUPPLY1
UNION ALL
SELECT supplyID, supplier
FROM dbo.SUPPLY2
UNION ALL
SELECT supplyID, supplier
FROM dbo.SUPPLY3
UNION ALL
SELECT supplyID, supplier
FROM dbo.SUPPLY4;