Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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

SQL View

By formatting the cells to "Wrap Text" the Char(10) acts like a line break and puts each address line in a single column.

Excel Version of View

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.

Vendor View

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]

Vendor View Modified

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

Dept View

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

Dept After Update

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'

SQL Failure on Update

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:

Insert View

This code will result in a failure to Insert.

INSERT INTO dbo.vw_HRDepts2 (Name, GroupName, EmployeeID) VALUES ('Administrative','Executive General and Administration', 290)

Insert Failure

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)

Indexed View

By executing the View and selecting the Display Actual Execution Plan, the index scanning shows how the view performs.

View Performance Statistics

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.

Query Execution Plan