Jan 24, 2020 
5.8k
27
DecChristmas Special : Upto 40% OFF! + 2 free courses - SCHEDULE CALL
- SQL Server Blogs -
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.
Views are of two types-
| System defined view | User-defined view |
| Already defined in the system | Defined by the user |
Three types-
| 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. |
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
Two types-
| 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 are of two types
| 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 |
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
Select max(CustomerID) from Simpleview
The output is
Insert operation in Simple View
The criteria of using a simple view for insert statement are
Example
Read: SQL Operators You Need to Know
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
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.
In the above screenshot the Person and Production, both are schema names.
Read: What Is The Difference Between The SQL Inner Join And Outer Joins?
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
Schema binding
The SchemaBinding option ensures that the View is strictly bound to database objects.
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.
For this, we will be using a new table called testusercheck.
Read: How To Differentiate SQL Server JOIN, IN And EXISTS Clause?
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.
FaceBook
Twitter
LinkedIn
Pinterest
Email
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.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Oct 12, 2022
659.7k
659.7k