MONTH START OFFER: Flat 15% Off with Free Self Learning Course | Use Coupon MONTH15

- SQL Server Blogs -

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
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 views Database-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
Simple Complex
Contains one table Contains multiple table
Cannot use group functions like MAX(),COUNT() Can use group functions
Insert, Update and delete are possible Insert, 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: How to use SQL Count() Aggregate Function
  • 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

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: Coalesce Function SQL Server Example

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

Advantages 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 schemabinding.

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 schemabinding

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: How to Create Stored Procedure & Trigger in SQL Server

Advantages of schema binding

Advantages of schemabinding

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

Read: SQL Server Indexes-All You Need to Know



    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

0 day 04 Dec 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

8 days 12 Dec 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 09 Dec 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

7 days 11 Dec 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

0 day 04 Dec 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

1 day 05 Dec 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

0 day 04 Dec 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

0 day 04 Dec 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

0 day 04 Dec 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

1 day 05 Dec 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

16 days 20 Dec 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

15 days 19 Dec 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews