Sql Server Views

Sql Server Views

Creates a virtual table that represents the data in one or more tables in an alternative way. CREATE VIEW must be the first statement in a query batch.

schema_name

Is the name of the schema to which the view belongs.

view_name

Is the name of the view.

Column

Is the name to be used for a column in a view. A column name is required only when a column is derived from an arithmetic expression, a function, or a constant.

AS

Specifies the actions the view is to perform.

select_statement

Is the SELECT statement that defines the view. The statement can use more than one table and other views. In an indexed view definition, the SELECT statement must be a single table statement or a multitable JOIN with optional aggregation.

The SELECT clauses in a view definition cannot include the following:

• COMPUTE or COMPUTE BY clauses
• An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement
• The INTO keyword
• The OPTION clause
• A reference to a temporary table or a table variable.

Syntax

CREATE VIEW view_name
[(column_name[,column_name]….)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

CREATE VIEW hiredate_view

AS

SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate

FROM HumanResources.Employee e

JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;

GO

ENCRYPTION

Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

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

SCHEMABINDING

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names - (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

CREATE VIEW vwSample
With SCHEMABINDING
As
SELECT
CustomerID,
CompanyName,
ContactName

FROM DBO.CUSTOMERS -- Two part name [ownername.objectname]
GO

When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies. The text of the CREATE VIEW statement is stored in the sys.sql_modules catalog view.

Updatable Views

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
  
  
• An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.

• A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.

The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.

• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
  
  

INSTEAD OF Triggers

INSTEAD OF triggers can be created on a view to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger lets the user specify the set of actions that must happen to process the data modification statement. Therefore, if an INSTEAD OF trigger exists for a view on a specific data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement. For more information about INSTEAD OF triggers

Partitioned Views

If the view is a partitioned view, the view is updatable, subject to certain restrictions. When it is needed, the Database Engine distinguishes local partitioned views as the views in which all participating tables and the view are on the same instance of SQL Server, and distributed partitioned views as the views in which at least one of the tables in the view resides on a different or remote server.

A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.

--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