Looking into Views in SQL Server

In this post I would like to look into Views in SQL Server.

As always I will demonstrate all the various concepts with hands-on examples.

I know that all these concepts are well documented elsewhere. I will try to look into views with step by step instructions and many screenshots.

Many people have found useful my style of posting. They feel they can follow the steps and master the technology.

Views have many advantages and when used correctly can benefit our applications.Please note that I will use T-SQL to create/alter/drop views. I will not use SSMS wizzards and graphic tools.

Views are virtual tables.What is stored in SQL Server as a distinct object is the definition of the view(SELECT statement) and nothing else. Some people refer to views as a stored query producing a virtual table.The data that is accessible through the view is not stored in the database.

View can provide data to client applications.We use them to customise data with different filters and sorts for different users or different applications. The end user or application do not have to know the structure of the data. We can restrict users to specific rows and columns of a table so they do not have access to unecessary or sensitive data. Many times we use views to provide aggregate data to the user and join columns from multiple tables so that they look like a single table.We can also use views to partition data.

One thing many people do not know is that a user or a role that someone else has granted them access to a particular view can have access to the underlying data even if that user or role does not have explicit access to the underlying tables.So permissions granted for a view supersede permissions revoked for the underlying tables.

Now let’s write some views. We need an instance of SQL Server runing in our machine. I have SQL Server 2008 R2 Developer edition.You can use any edition of SQL Server you might have installed in your machine.We need a database as well. I will use the Northwind database.

You can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

There are some rules/restrictions regarding creating views.

• You cannot have an ORDER BY clause unless there is a TOP or FOR XML clause present
• Obviously the name of the view must follow all the rules for identifiers and must be unique for each schema in the database
• Nested views cannot exceed 32 levels
• Views cannot have rules or defaults
• You can have only INSTEAD OF triggers applied to views
• You cannot create temporary views or views on temporary tables
• COMPUTE,COMPUTE BY on INTO clauses are not allowed in views

We can create views using the SSMS designers or use the Query editor and type raw T-SQL.I will use the later method.

1) Launch SQL Server Management Studio and connect to the local instance of SQL Server.

2) We will create a view that selects every product in the “Products” table with a unit price higher than the average unit price

If you want to see the full definition of how to create a view click here

USE Northwind
GO

IF OBJECT_ID ('[Products_Above_Average_Price]', 'V') IS NOT NULL
 DROP VIEW Products_Above_Average_Price ;
 GO

CREATE  VIEW [Products_Above_Average_Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

SELECT * FROM Products_Above_Average_Price

This is a very easy to create a view. Copy the code and paste in the query window in the SSMS. Run the query and see the results.

Let’s create another view. In this view we are looking to find the customers with the most orders.

USE Northwind
go

IF OBJECT_ID ('[vwCustomersTopTwenty ]', 'V') IS NOT NULL
DROP VIEW vwCustomersTopTwenty  ;
GO

CREATE VIEW dbo.vwCustomersTopTwenty
AS
SELECT TOP (20) WITH TIES
c.CustomerID,
c.CompanyName,
COUNT(*) AS NumOrders
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
ON c.CustomerID=o.CustomerID
GROUP BY c.CustomerID, c.CompanyName
ORDER BY NumOrders DESC;
GO

SELECT * FROM dbo.vwCustomersTopTwenty

This is a very easy to create a view. Copy the code and paste in the query window in the SSMS. Run the query and see the results.

We can see the newly created views in the object explorer. Have a look at the picture below.

You can select a view and then  right-click on it. You can do various things. You can delete, rename an existing view. You can can create a new view.You can see the design of the existing view.You can select the top 1.000 rows.You can also script the view and see the T-SQL for creating,altering,dropping the view.

Now I would like to show you how to encrypt a view. Sometimes we need to encrypt the defintion of the view.

CREATE VIEW dbo.EncryptedView
WITH ENCRYPTION
AS
SELECT LastName, FirstName,city
FROM dbo.Employees
WHERE City='London';
GO
SELECT * FROM EncryptedView;

EXEC sp_helptext 'dbo.EncryptedView';

If we try to get the T-SQL statement that is the definition of the view, we will fail. That is what I try to do with the statement above but I fail.I use the sp_helptext system stored procedure to do that.

This is what I get when I execute the code above.

The text for object ‘dbo.EncryptedView’ is encrypted.

Run the T-SQL code above so you can verify yourself what I have just told you and to see the result of the view.

We can also alter the definition of a view. We can do that using the ALTER keyword.In the following example I am altering the previous view so I can add the “WITH CKECK OPTION”. With this option, we force all data modification statements executed against the view to follow the criteria set within select statement

alter VIEW dbo.EncryptedView
WITH ENCRYPTION
AS
SELECT LastName, FirstName,city
FROM dbo.Employees
WHERE City='London'
WITH CHECK OPTION;
GO

Now I will try to update the data through the view. I will try to update the data in the view by setting the values in the city column to  “Paris”

UPDATE dbo.EncryptedView
SET city = 'paris';

GO

When I run the statement above I get an error, as expected. The error I get follows.

“Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.”

Another option many people use when creating views is “SCHEMABINDING”.

SCHEMABINDING is an option that when applied to Views forces SQL Server to create dependencies between any objects referenced within the View and the View. We cannot  have their definitions altered whilst the schema-bound View exists. This ensures the integrity of the View.

CREATE VIEW vwSample

With SCHEMABINDING

As

SELECT

CustomerID,

CompanyName,

ContactName

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

GO

SELECT * FROM VWSAMPLE

ALTER TABLE dbo.Customers

DROP COLUMN CompanyName

I create the view vwSample with the schemabinding on.Note that I use the schema-table name (dbo.Customers) when referencing the table.

When I try to alter the definition of the table by dropping a column that is referenced in the view (CompanyName) I get an error:

“Msg 5074, Level 16, State 1, Line 1
The object ‘vwSample’ is dependent on column ‘CompanyName’.”

You can also use the Template Explorer to create a view. Have a look at the picture below.

We can drop the view, by using T-SQL

DROP VIEW [dbo].[vwCustomersTopTwenty]

We can use another bit of T-SQL code to drop the database.We query the sys.views system view to see if the view exists and thenwe drop the view.

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCustomersTopTwenty]'))
DROP VIEW [dbo].[vwCustomersTopTwenty]
GO

We can use views to update data. When data is updated through the view changes will affect only one table.

Columns that are based on expressions, set operators, aggregate functions cannot be updated.

I will create a view below.I get data from both the Categories and Product tables. Then I have 3 update statements. The first two update statements will succeed but the last one will fail.The first two updates will attempt to change the data for one table only. The third one will fail as it attempts to update data in two tables. This is the error messageI get when I try to update multiple base tables.

“View or function ‘dbo.vwProductInCategories’ is not updatable because the modification affects multiple base tables.”

CREATE VIEW dbo.vwProductInCategories
AS
SELECT  C.CategoryName ,
C.Description ,
P.ProductID ,
P.ProductName ,
p.QuantityPerUnit
FROM    dbo.Products AS P
INNER JOIN dbo.Categories AS C ON P.CategoryID = C.CategoryID
GO

SELECT * FROM dbo.vwProductInCategories

-- This will work
UPDATE dbo.vwProductInCategories
SET ProductName = 'Genen Good'
WHERE ProductID = 15

-- This will work
UPDATE dbo.vwProductInCategories
SET CategoryName = 'Meat'
WHERE ProductID = 19

-- This will not work
UPDATE dbo.vwProductInCategories
SET ProductName = 'chocolate', CategoryName = 'Milk'
WHERE ProductID = 21

Run the T-SQL above to see the results for yourself.

We can create indexed views as well.  We all know that Indexes enhance performance. We can have indexes on one or multiple columns and we can have faster searches and sorting through the indexes. When we index views we materialise the view. That means the view data is stored as well along with the definition of the view.Ιndexed views boost performance when we have complex aggregations or joins.We must use the SCHEMABINDING option.

Indexed view definitions must use the two part name (schemaname.tablename).Before I create a view I will write a T-SQL query that performs some aggregate calculation.

I also use the “SET STATISTICS IO ON” , which causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

SET STATISTICS IO ON

SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Sales,
OrderDate, ProductID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE  ProductID in (7, 89, 22, 34)
AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Sales DESC;

Have a look at the picture below to see the logical and physical reads required.

Have a look also at the execution plan generated for the above query.

I create a view in the following example. I get the Product sales by date.

CREATE VIEW dbo.vwSalesByProductByDate
WITH SCHEMABINDING
AS
SELECT OrderDate, ProductID,
SUM(UnitPrice*Quantity*(1.00-Discount)) AS Sales,
COUNT_BIG(*) AS LineItemCount
FROM  dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID=O.OrderID
GROUP BY  OrderDate, ProductID
GO

SELECT * FROM dbo.vwSalesByProductByDate
GO

EXEC sp_spaceused 'dbo.vwSalesByProductByDate'

--Create the index on the view.

CREATE  UNIQUE CLUSTERED INDEX idx_vwSalesByProductByDate
ON dbo.vwSalesByProductByDate (OrderDate, ProductID)
GO

EXEC sp_spaceused 'dbo.vwSalesByProductByDate'

Then I select the data from the view. Then I use the sp_spaceused stored procedure (Displays the number of rows, disk space reserved, and disk space used by a table, indexed view) to see the disk space reserved for the results.

Have a look at the picture below to see the results of that statement.So now we can prove thet data is materialised in the view.

Then I create a clustered index on that view, so I have an indexed view. Then I use the sp_spaceused stored procedure  to see the disk space reserved for the results.They are different.

Have a look at the picture below and see the results of that statement.

Now we need to run the query again (the one we created just before we created the view)

SET STATISTICS IO ON

SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Sales,
OrderDate, ProductID
FROM dbo.[Order Details] OD JOIN dbo.Orders O
ON OD.OrderID = O.OrderID
WHERE  ProductID in (7, 89, 22, 34)
AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Sales DESC;

Have a look at the picture below to see the logical and physical reads required.

Have a look also at the execution plan generated for the above query.

Compare your findings with the relevant results befere we created the indexed view.  You can see now clearly that the query is using the clustered index and there is a  huge performance gain.

Do not use indexed views when the data in the underlying tables changes frequently.

In this final part I would like to talk about partitioned views.

A partitioned view is a database object that unions together tables with certain attributes.Tables with the same structure are combined to provide a unified view of a set of data, but that are divided on a “partitioning column.”

I will create a new database called “sample”.I will have 3 tables and I will populate them with data. The tables are divided/partitioned into the “city” column. The tables are CustomersParis,CustomersAthens,CustomersLondon

Create database [sample]
GO

USE [sample]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CustomersParis](
[CustomerID] [char](5) NOT NULL,
[CompanyName] [varchar](40) NOT NULL,
[ContactName] [varchar](30) NULL,
[Address] [varchar](60) NULL,
[City] [varchar](15) NOT NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NOT NULL,
[Phone] [nvarchar](24) NULL,
CONSTRAINT [PK_Cust1] PRIMARY KEY CLUSTERED
(
[City] ASC,
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CustomersParis]  WITH CHECK ADD CHECK  (([City]='Paris'))
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CustomersLondon](
[CustomerID] [char](5) NOT NULL,
[CompanyName] [varchar](40) NOT NULL,
[ContactName] [varchar](30) NULL,
[Address] [varchar](60) NULL,
[City] [varchar](15) NOT NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NOT NULL,
[Phone] [nvarchar](24) NULL,
CONSTRAINT [PK_Cust2] PRIMARY KEY CLUSTERED
(
[City] ASC,
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CustomersLondon]  WITH CHECK ADD CHECK  (([City]='London'))
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CustomersAthens](
[CustomerID] [char](5) NOT NULL,
[CompanyName] [varchar](40) NOT NULL,
[ContactName] [varchar](30) NULL,
[Address] [varchar](60) NULL,
[City] [varchar](15) NOT NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NOT NULL,
[Phone] [nvarchar](24) NULL,
CONSTRAINT [PK_Cust] PRIMARY KEY CLUSTERED
(
[City] ASC,
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CustomersAthens]  WITH CHECK ADD CHECK  (([City]='Athens'))
GO

Download the script from here. In this .sql file you will find the script to create the database, the tables and populate the tables with data.

Run the script to create the tables and to populate them.

Now we will create the partiotioned view.We  union those tables back together using a partitioned view.

USE [sample]

CREATE VIEW PartitionedView AS
SELECT * FROM dbo.CustomersAthens
UNION ALL
SELECT * FROM dbo.CustomersLondon
UNION ALL SELECT * FROM
dbo.CustomersParis GO

SELECT * FROM PartitionedView
WHERE city ='athens'

Run the T-SQL code to create the view and to use the view to query the underlying tables.

Hope it helps!!!