Views in SQL Server

Views in SQL Server

Views in SQL Server

In this post I will explain all about Views in SQL Server. So let’s start.

Views in SQL Server

Views are really nothing more than a stored query. In other words the views are virtual tables that hold data from one or more than one table, which can have multiple columns. It is stored in the database as an object and It can be used like the normal table. Normally view cannot store the data itself permanently like in the tables. Views are used for security purposes in the databases. Views restrict to user from viewing certain columns and rows of any tables in the database on which it’s applied or created. In other words, using a view we can apply the restriction on accessing specific rows and columns for a specific user. A view can be created using the tables of the same database or different databases. It is used to implement the security mechanism in the SQL Server. When we create the view it stores the view definition schema as an object under the concerned database.

Views are a logical table based on another table for data. Represent a subset of main data from the table. Hide table definition. Implement business logic. Easy complex query.

Diagram of Views in SQL Server

Views in SQL Server

In the above diagram we have created a view that contains the columns of two tables like TblOne and TblTwo using SQL queries. A view is created using a select statement. Views are stored in the database as an object so it does not require additional storage space.

Types of Views in SQL Server

There are two types of Views as mentioned below –

  1. System Defined Views
  2. User Defined Views

System Defined Views in SQL Server

Microsoft SQL Server has various predefined or system defined databases as Tempdb, Master, temp. All these predefined databases have their own properties and responsibilities like Master database is a template database for all user-defined databases, it contains many predefined views that work as templates for other databases and tables. We can see all these by clicking on plus(+) sign of databases – System Databases – master – Views – System Views like below snapshot –

Views in SQL Server

According to nomenclature and behavior of system-defined or predefined views can be divided into two categories –

  1. Catalog View
  2. Information Schema

Catalog View

Catalog Views categories fall into various groups, these are used to show the self-describing information of a database and start with “sys“. Let’s take an example – Suppose I want to see all types views are under my all databases then I will use the query to get all available views “SELECT * FROM sys.all_views” and will execute this query into query window of SQL Server Management Studio, once executes the query it will show all available views lists in all the databases along with all the required details.

Views in SQL Server

In the above result set we can see that the query provided information of all types of views using the database.

Now if we want to see the information about all the databases defined by the system, including user-defined and system-defined databases then we have to run the query like “SELECT * FROM sys.databases” and will execute this query into query window of SQL Server Management Studio, once executes the query it will show all available databases lists along with all the required details.

Views in SQL Server

In the above result set we can see that the query provided information of all databases including system defined as well as user-defined databases like “AdventureWorks2012” which is a user-defined database using be me for training purposes.

Information Schema

Information Schema can be used to display the physical information of any databases are available and attached with SQL Server Management Studio like table and columns. These predefined information schemas are very useful to us, these are around 21 in the master database up to SQL Server 2012 version. These begin like INFORMATION_SCHEMA.[View Name]. Let’s take an example – Suppose I want to see all the views are under my database then I will use the query to get information of available views “SELECT * FROM INFORMATION_SCHEMA.VIEWS” and will execute this query into the query window of SQL Server Management Studio, once executes the query it will show all available views lists in the database along with all the required details like table name, etc. I have selected one table name like “vEmployee” and want to see the View details into that table. In below example, I have taken both queries with result sets –

Views in SQL Server

In the above result set we can see that the INFORMATION_SCHEMA returned the details of all the views used by table vEmployee, in my case there is only one view that’s why it is showing alone.

If we want to the Constraint details applied on this table then please see the below query and result set using INFORMATION_SCHEMA –

Views in SQL Server

In the above result set we can see that the INFORMATION_SCHEMA returned the details of all the Constraints used by table CK_Product_Style, in my case there is no one Constraints that’s why it is showing blank.

User-Defined Views: A Views created by the user by his choice only, called User Defined Views. For this we need to create two tables first. In this regard we will create two different tables with different data fields to understand it better.

1st Table (TblOne) with the help of SQL Script –

Views in SQL Server

In above screen we have created TblOne with one Primary Key Constraints name ‘PK_TblOne’ using SQL Query, Now we will insert some random data into the newly created table (TblOne) with the help of following queries –

Views in SQL Server

In above screen we have inserted 3 records into TblOne, let’s see the inserted records using SELECT statement –

SELECT DATA FROM A TABLE

The work is completed for a first table (TblOne), now we will create another table name ‘TblTwo’ using SQL query –

create a table in sql

In above screen we have created TblTwo with one Foreign Key Constraints name ‘FK_TblTwo’ referencing prior table ‘TblOne’ ID column using SQL Query, Now we will insert some random data into the newly created table (TblTwo) with the help of following queries –

insert data in a table

In above screen we have inserted 2 records into TblTwo, let’s see the inserted records using SELECT statement –

SELECT DATA FROM A TABLE

The work is completed for second table (TblTwo) also, now we will start the complete steps of User-Defined Views (UDVs) –

Syntax –

CREATE VIEW View_Name AS

SELECT Column1, Column2,…

FROM Table

WHERE Your Conditions

Now we will create some views step by step in different types. Let’s start –

Type 1: We can select all columns of a table into the desired view while its creation time, as mentioned below –

Create a view in sql

Let’s see the result of view ‘View1_TblOne‘ how looks like the main table? using SELECT statement –

select data from a view in SQL

Wow… It’s looking pretty much like original table, this is what works a view. Now we will go through all possible types of Views.

Type 2: We can select required specific columns of a table into the desired view while its creation time, as mentioned below –

Syntax:

CREATE VIEW View2_TblOne AS
SELECT ID,Name,Did,[Employee Code],[Bank Account] FROM TblOne

Type 3: We can select required specific columns of a table on specific condition into the desired view while its creation time, as mentioned below –

Syntax:

CREATE VIEW View3_TblOne AS
SELECT ID,Name,Did,[Employee Code],[Bank Account]
FROM TblOne
WHERE ID =3

Type 4: We can select required specific columns of different tables on specific condition into the desired view while its creation time, as mentioned below –

Syntax:

CREATE VIEW View4_TblOne AS
SELECT t1.ID,t1.Name,t2.[Employee Code]
FROM TblOne t1 LEFT OUTER JOIN
TblTwo t2 ON t1.ID =t2.ID
WHERE t1.ID <3

Retrieving data from Views

We can proceed with SQL queries to see all the columns or specific columns from a view using SELECT statement, although no data is stored into respective yet it is a stored query only which will go to the respective tables and show to user as per instruction given while its creation time and according to user’s query and condition –

select data from a view in SQL

Renaming Views in SQL Server

A View can be renamed using Sp_Rename system stored procedure in SQL Server. If we want to rename this view (View4_TblOne) then we have to use the following query statement and execute the query in SQL Server Management Studio-

Syntax –

Sp_Rename Old_View_Name, New_View_Name

Example –

Sp_Rename View4_TblOne, Vw_TblTwo

Dropping Views in SQL Server

A View can be dropped using the DROP command in SQL Server. If we want to drop this view (View4_TblOne) then we have to use the following query statement and execute the query in SQL Server Management Studio-

Syntax –

DROP VIEW View4_TblOne

To get information of Views

If we want to see or retrieve all the internal information of a view (View4_TblOne) then we have to use the ‘Sp_Helptext‘ system stored procedure like following query statement and execute the query in SQL Server Management Studio-

Syntax –

Sp_Helptext View4_TblOne

sp_helptext

To Edit or Alter Views

If we want to change or alter the schema or structure of existing views like if we need to add or remove some columns or to change some conditions on the previous ones that are applied in a predefined view then we can do all these things with altering the view. For this we have to use the ALTER statement as shown in example –

Syntax –

alter a view in SQL

In the above snapshot, you can see easily that I have added one more column-like Remarks with auto-filled value with ‘by Alter‘ which will in each row of record sets and also changed where condition like less than id 5 and executed the query as we can see the message is showing “Command(s) completed successfully” means existing views (View4_TbleOne) structure has been changed or altered as discussed earlier.

Refresh Views

We need to refresh an existing view in our database if we used “SELECT * FROM TABLE NAME” statement in that view and recently associated table structure has been changed either some data fields have been removed or some data fields has been added on that table because a view doesn’t refresh by itself and if we don’t refresh it then it will present all in the old structure. We can go through an example. First we will create a table EmpTest, will insert some test data in it and then we will create a view namely Vw_EmpTest taking entire columns of the table after creation of view we will add one more column in that table and then we will run the select query on both (table and view) separately lets see –

CREATE TABLE EmpTest (
ID INT NULL, Name VARCHAR(50))

INSERT INTO EmpTest (ID,Name)
VALUES (1, ‘Raghawendra’)
INSERT INTO EmpTest (ID,Name)
VALUES (2, ‘Prasad’)
INSERT INTO EmpTest (ID,Name)
VALUES (3, ‘Shukla’)

CREATE VIEW Vw_EmpTest AS
SELECT * FROM EmpTest

ALTER TABLE EmpTest
ADD Salary MONEY

Now we will retrieve data from the table and view and output will show as below –

select data from view in SQL

As we can see above result that the view is showing correct structured data as in table actually, to resolve this issue we need to refresh the existing view using sp_refreshview system-defined stored procedure as mentioned below –

exec sp_refreshview Vw_EmpTest

Now we will run a select query on that view and will get exact data with structure as desired –

select data from view in SQL

Schema Binding of Views

As seen in the above example of refreshing a view that is needed if any changes done in the base table. In such a way if we want to prevent any type of changes into the base table then we can use the SQL concept for the view is schema binding. If we use schema binding then it will lock the table being referred to by the view and restrict all kinds of changes that may change the schema of the table means no alter command. In this way if we use this schema binding then we can not specify “SELECT * FROM TABLE NAME” within the query although we need to specify all the column names of the table.

CREATE VIEW View_EmpTest
WITH SCHEMABINDING
AS
SELECT ID,Name,Salary FROM dbo.EmpTest

In the above example we have created a view using schema binding. Now we will try to change the datatype of EmpTest table into NVARCHAR instead of Money, let’s see –

edit a table definition in SQL

As we can see above snapshot that we can not change the data type of the table as we have already used the schema binding which is preventing any type of changes in the table where it is applied.

Encrypt Views

If we want to create a view on a specific table and do not want it to be visible using system stored procedure sp_helptext then we can use the WITH Encryption keyword while creating a view. This option encrypts the definition of the view. User will not be able to see the definition of the view, let’s create and see –

CREATE VIEW View1_EmpTest
WITH ENCRYPTION AS
SELECT ID,Name,Salary FROM dbo.EmpTest

Now we will try to retrieve the definition of the view using sp_helptext –

sp_helptext

In the above snapshot we can see that we can not see the definition of the view as it was encrypted while creation.

Check Views

If we use the CHECK condition while creating a view then it checks for a certain condition and ensures that all the UPDATE and INSERT commands must satisfy the given condition in the view definition

CREATE VIEW dbo.VwC_EmpTest AS
SELECT * FROM EmpTest
WHERE Salary>1000
WITH CHECK OPTION

Now we will try to insert the data using INSERT statement query against the condition then it will throw the error

INSERT INTO VwC_EmpTest
VALUES (4, ‘Devid’,500,’Ali’)

Result:

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.

Operations of DML (Data Manipulation Language) Queries in View

We can implement DML queries like SELECT, INSERT, UPDATE and DELETE but it will better to know some conditions before applying as mentioned below –

  1. View should not contain the multiple tables
  2. View should not contain the SET Functions
  3. View should not use the DISTINCT keyword
  4. View should not contain the GROUP BY and HAVING CLAUSE
  5. View should not contain Sub Queries
  6. View should not use the SET Operators
  7. All NOT NULL defined columns from the base table must be included in the view in order for the INSERT query to apply like mentioned below –

SELECT * FROM VwC_EmpTest

INSERT INTO VwC_EmpTest
VALUES (1, ‘Devid’,1500,’Ali’)

INSERT INTO VwC_EmpTest
VALUES (2, ‘Desuza’,1500,’Javed’)

DELETE FROM VwC_EmpTest WHERE ID = 4

SELECT * FROM VwC_EmpTest

We can drop an existing view using DROP Statement –

DROP VIEW VwC_EmpTest

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc.

Leave a Reply