SQL Server Views — Everything You Should Know

Imagine a situation where an audit is going on in your organization and as a part of the IT team, you are asked to provide the auditor’s access to certain company-related data. Now, there can be a lot of sensitive information also that you do not want the auditors to access. At the same time, there can be a lot of information which is required by the auditors. So you are in a real dilemma, what access to give and what not. You cannot provide full access to the database which would allow the auditors to access all your company-related information including sensitive ones. Neither can you deny access that could lead you to legal trouble. You need something in which you can provide access to some specific information to the auditors. That is where Views comes into play.

Views are virtual tables based on the result of a query. This means, if you have to say a hundred fields in one of your tables, and sixty of those fields have sensitive data, you can easily create a view on the rest forty fields and provide access only to those forty fields. The user of the view will feel like he is accessing a database table without having a faint idea that he is only given access to a part of the organization data.

Types of SQL Server Views

Views are of two types-

• System defined view
• User-defined view

The system defined view user-defined view already defined in the system defined by the user

System Defined View

Three types-

• Information schema view
• Catalog view
• Dynamic management view

Information schema view catalog view dynamic management view they are twenty in number. They are used to show information about a database.hows information that helps to understand the health of the database.out administrator information of a database.They are located under the master database. They have a prefix called INFORMATION_SCHEMA.They are also located under the master database.Returns information that can be used to monitor the health of a server.

Information schema view

Example

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, 
COLUMN_DEFAULT
FROM AdventureWorks2016.INFORMATION_SCHEMA.COLUMNS

Output

Catalog view

Example

SELECT * FROM sys.objects
WHERE type_desc = 'USER_TABLE'

Output

Dynamic management view

Two types-

• Server-scoped dynamic management views
• Database-scoped dynamic management views

Server-scoped dynamic management viewsDatabase-scoped dynamic management views require VIEW SERVER STATE permission on the server require VIEW DATABASE STATE permission on the database

Example

SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;

The output will be

User-Defined Views

User-Defined Views are of two types

• Simple
• Complex

SimpleComplexContains one tableContains multiple tableCannot use group functions like MAX(),COUNT()Can use group functionsInsert, Update and delete are possibleInsert, Update and delete are not possible

Simple view example

For this example, we will be using Customer and Sales Order details of the Adventureworks database.

Create view Simpleview as
Select CustomerID,StoreID,TerritoryID,AccountNumber from [Sales].[Customer]

To check the data in the view

Select * from Simpleview

The output is

Using Group functions in Simple View

Select max(CustomerID) from Simpleview

The output is

Insert operation in Simple View

The criteria of using a simple view for insert statement are

Read: RDBMS Interview Questions

• The view must contain all the mandatory fields
• If there is any primary key in the table, that particular field should also be included in the view.

Example

We will use the same Simpleview for the insert example.

Insert into Simpleview(StoreID,TerritoryID) values('1028','1')

The output will be

Complex View

The query statement looks like this

Create view complexview as

Select a.SalesOrderID,a.CustomerID,a.OrderDate,b.ProductID,b.OrderQty,b.UnitPrice
From [Sales].[SalesOrderHeader] a inner join [Sales].[SalesOrderDetail] b on a.SalesOrderID=b.SalesOrderID

To check the data of a complex view we can write

Select * from complexview

The output will be

Drop a view

To drop a view we need to write the following query statement

Drop view Simpleview

The output is as follows

Advantages of Views

• Allows to simplify complex queries
• Limit data access to specific viewers

Index views

Earlier we have learned that Views are virtual tables that do not have any physical existence. We can perform all the table related functionality using a view. We can also index a view. Here is how we can do it. We will be using the Customer and SalesOrderheader table from the Adventureworks database for this purpose.

Here is how the view will look like-

Create view demo_indexview WITH SCHEMABINDING as

Create view  demo_indexview WITH SCHEMABINDING  as
Select cust.CustomerID,slheader.SalesOrderID,slheader.OrderDate from [Sales].[Customer] cust
Inner join [Sales].[SalesOrderHeader] slheader
on
cust.CustomerID=slheader.CustomerID
Where slheader.OnlineOrderFlag=1

To add an index to the view the following SQL statement is required.

CREATE UNIQUE CLUSTERED INDEX i1 ON demo_indexview (SalesOrderID);

The output is

Schema

Schema is a combination of database objects connected with a particular username.
The username is called the schema owner. In case no schema is defined, if a user creates a database object it is automatically associated with his or her username.

Read: What Is SQL Candidate Key? Difference between Primary Key & Candidate Key

In the above screenshot the Person and Production, both are schema names.

If no schema is mentioned, while creating the object, then SQL Server automatically assigns a schema by default. It is called DBO (Database object Schema).

How to create a schema?

To create a schema use the following SQL Statement-

Create schema test

The output is as below

To add a table to a schema

To add a table to a schema uses the following SQL Statement

ALTER SCHEMA test TRANSFER [dbo].[testab1]

The output is

Advantage of schema

• It helps to manage several logical entities in a database.
• Acts as an object protection tool.

Schema binding

The SchemaBinding option ensures that the View is strictly bound to database objects.

Schema binding View Example

For example, we will create a simple table called testuser and create a view against it using schema binding.

The sql statement to create the table is as follows.

Create table testuser
(
useridvarchar(50),
usernamevarchar(50)
)

Now let us fill some data in the table

Insert into testuser values('id1','user 1')
Insert into testuser values('id2','user 2')

The table looks as below

Now let us create the view with schema binding

CREATE VIEW vw_Test WITH SCHEMABINDING AS   
SELECT userid FROM dbo.testuser

Let us explore the view

Select * from vw_Test

The output is like below

Now let us try to delete the base table testuser and see what happens.

The SQL statement to delete the table is as below.

Drop table testuser

But it gives the following error when executed.

Read: All you need to know about SQL Cheat Sheet

Advantages of schema binding

• Improves the performance of the query execution plan.
• It checks dependency objects before drop a table/view in the database

Check option in SQL Views

For this, we will be using a new table called testusercheck.

Here is how we can create the table.

Create table testusercheck
(
useridvarchar(50),
usernamevarchar(50),
ageint
)

Let us now put some data into it.

Insert into testusercheck values('id1','user 1',42)
Insert into testusercheck values('id2','user 2',45)
Insert into testusercheck values('id3','user 2',30)
Insert into testusercheck values('id4','user 2',25)

The final table looks like below

Now let us create another table called testcheck1 with the same data.

The query statement is as below.

Select * into testusercheck1 from testusercheck

The new table looks like below

Now let us create two views, one with check option and another without one.

First, let us create the view without the check option. Let us use the testusercheck1table. Here how the SQL statement will look like.

Create view viewwithoutcheck as 
Select * from testusercheck1 where Age > 30

Here is the output:

Since this is a simple view and we have included all the columns, we can perform insert, update and delete operation on it. Let us perform an update operation on it.

Insert into view with out check values('id5','user 2',25)

The output is

This means although I have created a view that contains record details where age is greater than thirty it has allowed me to insert a record which is less than thirty. This should not happen.

To prevent this we can create the same view with the check option. Here is the syntax.

create view viewwithcheck as 
select * from testusercheck where Age > 30
with check option

The output is

Now let us fire the same insert statement on this view. The SQL Statement is as below.

Insert into viewwithcheckvalues ('id5','user 2',25)

But this time it throws an error

This is the use of a check option, which does not allow the user to insert or update the base table with a record that does not match the criteria of the view from within the view.

Summary

Through the above few paragraphs, we have introduced the reader to views. It covers almost all the features of SQL Server views but in a summarized manner. But this would encourage the reader to learn about the view in more detail.