Sql Server Views
1. A VIEW in SQL Server is like a virtual table that contains data from one or multiple tables.
2. It does not hold any data and does not exist physically in the database.
3. Similar to a SQL table, the view name should be unique in a database.
4. View is used to provide row level and column level security
5. It contains a set of predefined SQL queries to fetch data from the database. It can contain
database tables from single or multiple databases as well.
Create a SQL VIEW
The syntax to create a VIEW is as follows:
CREATE VIEW Name AS
Select column1, Column2...Column N From tables
Where conditions;
Example 1: SQL VIEW to fetch all records of a table
It is the simplest form of a VIEW. Usually, we do not use a VIEW in SQL Server to fetch all records
from a single table.
Create view Getemp
As
Select *from emp;
Example 2: SQL VIEW to fetch a few columns of a table
How many types of views are there in SQL Server?
There are two types of views in SQL Server, they are
1. Simple view or Updatable views
2. Complex view or non-updatable views.
What is a simple view or an Updatable view in SQL Server?
The view that is created based on the columns of a single table, then it is known as a
simple view.
We can perform all the DML operations on a simple view so that a simple view can also
be called an updatable view or dynamic view.
-- View with all columns
CREATE VIEW vwAllEmployees1
AS
SELECT * FROM Employee
-- View with specific columns
CREATE VIEW vwAllEmployees2
AS
SELECT ID, Name, Gender, DOB, DeptID
FROM Employee
The above two views are examples of Simple or Updatable Views. That means
we can perform all the DML operations on the above views.
To select data from a view, we need to use the SELECT statement, in the same
way, we used to select the data from a table
i.e. SELECT * from vwAllEmployees1
DML Operations on the View:
Select Operation on View: SELECT * FROM vwAllEmployees1
When we execute the above query it will display the following results
Insert Operation on View
INSERT INTO vwAllEmployees1 (ID, Name, Gender, DOB, DeptID) VALUES(7,
‘Rohit’, ‘Male’, ‘1995-04-19 10:53:27.060’, 3)
When we execute the above insert statement, it will successfully insert the record into
the Employee table. To check issue a select query against the Employee table as shown
below
SELECT * FROM Employee will give the following result
As you can see the new record is inserted into the Employee table.
Update Operation on Views:
Let’s try to update the details of the employee whose id is 7 using the view.
UPDATE vwAllEmployees1 SET
Name = 'Rohit Kumar',
DOB = '1996-02-29 10:53:27.060',
DeptID = 1
WHERE ID = 7
Once you execute the above SQL Query, to check whether the data is updated
successfully or not make a select query against the Employee table as shown below.
SELECT * FROM Employee will give the following result
As you can see the Employee whose ID is 7 is successfully updated as expected.
Delete Operation on View:
Let’s try to Delete the Employee whose ID is 7 using the view
DELETE FROM vwAllEmployees1 where ID = 7
Once we execute the above delete operation then check the Employee table and you
will see that the Employee whose ID is 7 is deleted as expected from the Employee
table. So this proofs that we can perform ALL the DML operations on a Simple View
which is also called Updatable View in SQL Server.
What is a complex view in SQL Server?
When we create a view on more than 1 table then it is known as a complex view and on
a complex view, we cannot perform DML operations so that a complex view is also
called the non-updatable or static view.
Complex view Example
CREATE VIEW vwAllEmployees3
AS
SELECT emp.ID, emp.Name, emp.Gender, emp.DOB,
dep.Name as DepartmentName
FROM Employee emp
INNER JOIN Department dep
on emp.DeptID = dep.ID
Note: A view that is created based on a single table will also be considered as a
complex view provided if the query contains any of the following.
Distinct. Aggregate Function, Group By Clause, having Clause, calculated columns, and
set operations.
CREATE VIEW vwAllEmployees4
AS
SELECT Gender, Count(*) as TotalEmployee
FROM Employee Group BY Gender
Can we drop a table that has dependent views on it?
Yes, you can drop a table even if any dependent views are associated with it, but the
views that are associated with it will not be dropped. They will still execute in the
database only with the status as inactive object and all those views become active and
start functioning provided the table is recreated.
Can we create a view based on other views?
Yes, It is possible in SQL Server to create a view based on other views.
Can we update the views in SQL Server?
Yes, in SQL server views can be updated. However, updating a view that is based on
multiple tables, may not update the base tables correctly. To correctly update a view that
is based on multiple
Example 6: Use Sp_helptext to retrieve VIEW definition
We can use sp_helptext system stored procedure to get VIEW definition. It returns the complete
definition of a SQL VIEW.
For example, let’s check the view definition for EmployeeRecords VIEW.
Example 7:
sp_refreshview to update the Metadata of a SQL VIEW
Suppose we have a VIEW on a table that specifies select * statement to get all columns of that
table.
1 CREATE VIEW DemoView
2 AS
3 SELECT *
4 FROM [AdventureWorks2017].[dbo].[MyTable];
Once we call the VIEW DemoView, it gives the following output.
Let’s add a new column in the table using the Alter table statement.
1 Alter Table [AdventureWorks2017].[dbo].[MyTable] Add City nvarchar(50)
Rerun the select statement to get records from VIEW. It should display the new column as well in
the output. We still get the same output, and it does not contain the newly added column.
