SQL Views
Creating SQL Server Views
This tutorial provides a few examples of Views in T-SQL for SQL Server. Views are saved complex queries which provide selected items from the tables that are joined.
SQL View for an Excel Spreadsheet
Sample View with Char (10) for an Excel Sheet. Format the column to wrap the text and get all of the data into a single cell with breaks.
CREATE VIEW [dbo].[vw_employeesExcel] AS SELECT ROW_NUMBER() OVER (ORDER BY HumanResources.Employee.EmployeeID ASC) AS 'RowNum', HumanResources.Employee.EmployeeID, Person.Contact.FirstName + CASE WHEN Len(IsNull(Person.Contact.MiddleName,''))>0 THEN ' ' + Person.Contact.MiddleName + ' ' ELSE '' END + Person.Contact.LastName + CHAR(10) + IsNull(Person.Address.AddressLine1,'') + CASE WHEN Len(Person.Address.AddressLine1) > 0 THEN char(10) ELSE '' END + IsNull(Person.Address.AddressLine2,'') + CASE WHEN Len(Person.Address.AddressLine2) > 0 THEN char(10) ELSE '' END + ISNULL(Person.Address.City,'') + ' ' + ISNULL(Person.StateProvince.StateProvinceCode,'') + ' ' + ISNULL(Person.Address.PostalCode,'') as Employee, HumanResources.Department.Name as DeptName FROM Person.Address INNER JOIN HumanResources.EmployeeAddress ON Person.Address.AddressID = HumanResources.EmployeeAddress.AddressID INNER JOIN HumanResources.Employee ON HumanResources.EmployeeAddress.EmployeeID = HumanResources.Employee.EmployeeID INNER JOIN HumanResources.EmployeeDepartmentHistory ON HumanResources.Employee.EmployeeID = HumanResources.EmployeeDepartmentHistory.EmployeeID INNER JOIN HumanResources.Department ON HumanResources.EmployeeDepartmentHistory.DepartmentID = HumanResources.Department.DepartmentID INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID INNER JOIN Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID GO
By formatting the cells to "Wrap Text" the Char(10) acts like a line break and puts each address line in a single column.
SQL View for a Formatted address list
This view uses substitution of text for missing field data and concatenates the name fields and City State and Zip.
Original View code
ALTER VIEW [Purchasing].[vVendor] AS
SELECT
v.[VendorID]
,v.[Name]
,ct.[Name] AS [ContactType],
c.[Title],c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix],
c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion],
a.[AddressLine1]
,a.[AddressLine2],
a.[City]
,[StateProvinceName] = sp.[Name]
,a.[PostalCode]
,[CountryRegionName] = cr.[Name]
FROM [Purchasing].[Vendor] v
INNER JOIN [Purchasing].[VendorContact] vc
ON vc.[VendorID] = v.[VendorID]
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = vc.[ContactID]
INNER JOIN [Person].[ContactType] ct
ON vc.[ContactTypeID] = ct.[ContactTypeID]
INNER JOIN [Purchasing].[VendorAddress] va
ON va.[VendorID] = v.[VendorID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = va.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
GO
Notice how Null values appear between the line, rather than a well formatted row with each cell filled with data.
A rewrite of the code to prepare the mailing list.
select Name as Vendor, AddressLine1 Addr1,
CASE WHEN AddressLine2 is not null THEN AddressLine2
WHEN City is not null THEN City + ', ' + StateProvinceName + ' ' + PostalCode END Addr2,
CASE WHEN AddressLine2 is not null THEN
City + ', ' + StateProvinceName + ' ' + PostalCode
WHEN City IS not null THEN CountryRegionName
END Addr3,
CASE WHEN AddressLine2 is null
THEN 'Attn: Addressee and Officer, General or Managing Agent'
WHEN AddressLine2 is not null then CountryRegionName
END Addr4,
CASE WHEN AddressLine2 is not null THEN 'Attn: Addressee and Officer, General or Managing Agent'
ELSE '' END
from AdventureWorks.[Purchasing].[vVendor]
Updating and Inserting into Views
It is possible to update or insert data into the base tables through a view. The exception is that you cannot update multiple base tables in a single update statement.
For example, this view joins the Department, EmployeeDepartmentHistory and Employee tables from AdventureWorks
CREATE VIEW dbo.vw_HRDepts2 AS select HumanResources.Department.DepartmentID, Name, GroupName, HumanResources.Employee.EmployeeID, HumanResources.Employee.HireDate from HumanResources.Department Inner join HumanResources.EmployeeDepartmentHistory on HumanResources.Department.DepartmentID = HumanResources.EmployeeDepartmentHistory.DepartmentID inner join HumanResources.Employee on HumanResources.EmployeeDepartmentHistory.EmployeeID = HumanResources.Employee.EmployeeID go
To update the Purchasing department, consider the following code:
UPDATE dbo.vw_HRDepts2 SET GroupName = 'Inventory Management & Stuff' WHERE Name Like 'Purchasing'
We can verify that the base table was updated by running the view.
SELECT * FROM dbo.vw_HRDepts2
However, if the Update statement contains columns from multiple base tables, the update will fail as in the following example.
UPDATE dbo.vw_HRDepts2 SET GroupName = 'Inventory Management', HireDate = '1998-01-21' WHERE Name Like 'Purchasing'
Inserting is also possible on a view. The same rule applies regarding the Insert statement: only 1 base table at a time may be affected.
INSERT INTO dbo.vw_HRDepts2 (Name, GroupName) VALUES ('Administrative','Executive General and Administration')
Verify the insert occurred in the base table:
This code will result in a failure to Insert.
INSERT INTO dbo.vw_HRDepts2 (Name, GroupName, EmployeeID) VALUES ('Administrative','Executive General and Administration', 290)
Indexed Views
Indexing a view is done to improve performance. Views save the developer time in having to recreate a complex query with many joins and perhaps many aggregations of data. To get the view to execute faster, creating an index on column(s) can help optimize performance by prejoining the tables storing the result data set and precomputing aggregations eliminating complex computing at runtime.
The view must use the SCHEMABINDING option and reference only base tables and functions used in the view must be deterministic. There is a list of the rules on the Microsoft website which describe all of the restrictions on Indexed views: Microsoft Indexed Views
The following code creates the view to be indexed:
use AdventureWorks
go
ALTER VIEW dbo.vw_EmployeeData WITH SCHEMABINDING AS
SELECT p.PurchaseOrderID, SUM(OrderQty * UnitPrice) AS POTotal,
TaxAmt, Freight, TotalDue, EmployeeID, LastName as EmpName, COUNT_BIG(*) as poCnt
FROM Purchasing.PurchaseOrderDetail pd INNER JOIN Purchasing.PurchaseOrderHeader p
on p.PurchaseOrderID=pd.PurchaseOrderID
INNER JOIN Person.Contact c on c.ContactID = p.EmployeeID
group by p.PurchaseOrderID, TaxAmt, Freight, TotalDue, EmployeeID, LastName
GO
Create the Unique index on the view:
CREATE UNIQUE CLUSTERED INDEX VempPO on dbo.vw_EmployeeData (PurchaseOrderID, EmployeeID)
By executing the View and selecting the Display Actual Execution Plan, the index scanning shows how the view performs.
If the data in the view is executed as a Query, the Actual Execution Plan displays the other scans that take place on the Primary Keys that are joined.
