What Is Indexed View In SQL By Sagar Jaybhay

In this article we will understand What Is Indexed View In SQL Server By Sagar Jaybhay.

Indexed View

A normal view or non-indexed view is a saved SQL query or a virtual table. When you try to get data from the table the data is coming from an underlying table. So it means the view is a virtual table and doesn’t store any data.

But when you create an index on view it gets materialized which means you can store data in view so in the SQL server we called is as Indexed view.

There are some guidelines for creating an indexed view.

  1. The view should be created with schemabinding option.
  2. If in your select function contains aggregate functions like sum, avg like that then for this you need to replace the null value with other value means 0 or not null value.
  3. If your select query contains group by clause then you need to use Count_Big(*) expression.
  4. Whatever table you used in view you need to specify 2 part names of these tables the example of above shown below

create view IndexedView
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from Employee as e 
inner join Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

Indexed View In SQL By Sagar Jaybhay
Indexed View In SQL By Sagar Jaybhay

Now by using the above query, we created a view but we are not able to create an index on that so that you can get below error

Msg 1939, Level 16, State 1, Line 59

Cannot create an index on view ‘IndexedView’ because the view is not schema bound.

Indexed View In SQL Error Message By Sagar Jaybhay
Indexed View In SQL Error Message By Sagar Jaybhay

For this, we need to create a view schemabinding option and now we can alter our view. For altering view our query looks like below

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

Msg 4512, Level 16, State 3, Procedure IndexedView, Line 4 [Batch Start Line 58]

Cannot schema bind view ‘IndexedView’ because the name ‘Employee’ is invalid for schema binding. Names must be in two-part format and an object cannot reference

Now when we are altering the view with schema binding option we get the above error. To fix this we need two-part names of the table.

Now our view gets created by using below query

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from dbo.Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

But when we are going to create the index we will get below error

Msg 10138, Level 16, State 1, Line 68

Cannot create an index on view ‘temp.dbo.IndexedView’ because its select list does not include proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select a list.

Now we add count_big(*) in our query

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(isnull(e.salary,0)) as totalsal,
COUNT_BIG(*) as totalemp
from dbo.Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

and when we use below query

create unique clustered index view_deptindex
on IndexedView(dept);

to create an index it works perfectly fine.

Updatable View in SQL By Sagar Jaybhay

In this article we will understand how to Update view or What it means In SQL Server By Sagar Jaybhay.

Before Proceeding To this article Please Read Part 1 – https://sagarjaybhay.com/view-in-sql-by-sagar-jaybhay-2020/

Updatable View in SQL Server

By using view it is possible to update the base table.

By using view it is possible to update the base table.
By using view it is possible to update the base table.

Updatable View 2
Updatable View 2

Query for this

update empwithdepartment set full_name='flower blossam' where EmpID=2;

Updatable View 3
Updatable View 3

See the above image you will see the result the data is updated but it not present in view. It is updated in the underlying table so If you want to find this whether it is updated or not fire below query.

select top 4 * from empwithdepartment;

Updatable View 4
Updatable View 4

In the above image, you will find the base table also gets updated.

Like the update, you can also delete data from view or insert data into view which turn deleted or inserted in the underlying table.

Updatable View 5
Updatable View 5

But in this case, we got an error why.

Msg 4405, Level 16, State 1, Line 35

View or function ’empwithdepartment’ is not updatable because the modification affects multiple base tables.

Because our view is based on multiple tables so we are not able to insert records in view or it might update records incorrectly but if our view is based on a single table then we are able to add records in view which in turn added in a base table.

When you trying to insert data into view and view finds that multiple tables contain these fields so it gets confused and throws an error.

Updatable View 6
Updatable View 6

In the above figure, you will understand we create a view that is based on a single table and we are able to insert records using the view.

create view simpleview
as
select EmpID,full_name,Salary,Gender from Employee where Gender='Male';

insert into simpleview values(1002,'Sagara',20000,'Male');

select * from simpleview where EmpID>1000;
select * from Employee where EmpID=1002;

View in SQL By Sagar Jaybhay 2020

In this article Sagar Jaybhay explain what is View. What is the use of View. what is the advantages of views in SQL server.

View

The view is saved SQL query or we can call it a virtual table.

View In SQL 1
View In SQL 1

We have these 2 tables and by joining these 2 tables we want to output.

View In SQL 2
View In SQL 2

By joining these 2 tables we get the above result. Now we want to create a view, like other create statement like create a table, create procedure we have to create view statement

Below is a query for creating a view

create view empwithdepartment
as
select e.EmpID,e.full_name,e.Salary,e.Gender,d.Department_Name from Employee as e
join Department as d on d.DepartmentID=e.DepartmentID;

View In SQL 3
View In SQL 3

To find a view in the database refer below image

How to find view
How to find view

If you check the above query it just selects query and if you want to get data from the view you will able to treat it as a table and simple select * from view_name; by using this you can get data.

View doesn’t store any data and it is just saved select query.

Advantages of views

  1. It is used to reduce the complexity of database schema
  2. It provides a mechanism to implement column level and row-level security means if you want to give access to certain users with a limited number of rows and columns then put your query in view and given that view name to end-user so that he performs the operation on that considering this is a table. By doing this end-user doesn’t know the underlying base table.
  3. It Is used to present aggregated data or detailed data.

To alter view you can use alter view syntax

Alter view view_name
As
-	your query syntax here

To drop the view you can use

Drop view view_name;


GitHub : https://github.com/Sagar-Jaybhay