Suggested SQL Server Videos before watching this Video
1. Part 39 - Views in sql server
2. Part 40 - Updateable views in sql server
In Part 39, we have covered the basics of views and in Part 40, we have seen, how to update the underlying base tables thru a view. In this video session, we will learn about INDEXED VIEWS.
What is an Indexed View or What happens when you create an Index on a view?
A standard or Non-indexed view, is just a stored SQL query. When, we try to retrieve data from the view, the data is actually retrieved from the underlying base tables. So, a view is just a virtual table it does not store any data, by default.
However, when you create an index, on a view, the view gets materialized. This means, the view is now, capable of storing data. In SQL server, we call them Indexed views and in Oracle, Materialized views.
Let's now, look at an example of creating an Indexed view. For the purpose of this video, we will be using tblProduct and tblProductSales tables.
Script to create table tblProduct
Create Table tblProduct
(
ProductId int primary key,
Name nvarchar(20),
UnitPrice int
)
Script to pouplate tblProduct, with sample data
Insert into tblProduct Values(1, 'Books', 20)
Insert into tblProduct Values(2, 'Pens', 14)
Insert into tblProduct Values(3, 'Pencils', 11)
Insert into tblProduct Values(4, 'Clips', 10)
Script to create table tblProductSales
Create Table tblProductSales
(
ProductId int,
QuantitySold int
)
Script to pouplate tblProductSales, with sample data
Insert into tblProductSales values(1, 10)
Insert into tblProductSales values(3, 23)
Insert into tblProductSales values(4, 21)
Insert into tblProductSales values(2, 12)
Insert into tblProductSales values(1, 13)
Insert into tblProductSales values(3, 12)
Insert into tblProductSales values(4, 13)
Insert into tblProductSales values(1, 11)
Insert into tblProductSales values(2, 12)
Insert into tblProductSales values(1, 14)
tblProduct Table
tblProductSales Table
Create a view which returns Total Sales and Total Transactions by Product. The output should be, as shown below.
Script to create view vWTotalSalesByProduct
Create view vWTotalSalesByProduct
with SchemaBinding
as
Select Name,
SUM(ISNULL((QuantitySold * UnitPrice), 0)) as TotalSales,
COUNT_BIG(*) as TotalTransactions
from dbo.tblProductSales
join dbo.tblProduct
on dbo.tblProduct.ProductId = dbo.tblProductSales.ProductId
group by Name
If you want to create an Index, on a view, the following rules should be followed by the view. For the complete list of all rules, please check MSDN.
1. The view should be created with SchemaBinding option
2. If an Aggregate function in the SELECT LIST, references an expression, and if there is a possibility for that expression to become NULL, then, a replacement value should be specified. In this example, we are using, ISNULL() function, to replace NULL values with ZERO.
3. If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression
4. The base tables in the view, should be referenced with 2 part name. In this example, tblProduct and tblProductSales are referenced using dbo.tblProduct and dbo.tblProductSales respectively.
Now, let's create an Index on the view:
The first index that you create on a view, must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes.
Create Unique Clustered Index UIX_vWTotalSalesByProduct_Name
on vWTotalSalesByProduct(Name)
Since, we now have an index on the view, the view gets materialized. The data is stored in the view. So when we execute Select * from vWTotalSalesByProduct, the data is retrurned from the view itself, rather than retrieving data from the underlying base tables.
Indexed views, can significantly improve the performance of queries that involves JOINS and Aggeregations. The cost of maintaining an indexed view is much higher than the cost of maintaining a table index.
Indexed views are ideal for scenarios, where the underlying data is not frequently changed. Indexed views are more often used in OLAP systems, because the data is mainly used for reporting and analysis purposes. Indexed views, may not be suitable for OLTP systems, as the data is frequently addedd and changed.
1. Part 39 - Views in sql server
2. Part 40 - Updateable views in sql server
In Part 39, we have covered the basics of views and in Part 40, we have seen, how to update the underlying base tables thru a view. In this video session, we will learn about INDEXED VIEWS.
What is an Indexed View or What happens when you create an Index on a view?
A standard or Non-indexed view, is just a stored SQL query. When, we try to retrieve data from the view, the data is actually retrieved from the underlying base tables. So, a view is just a virtual table it does not store any data, by default.
However, when you create an index, on a view, the view gets materialized. This means, the view is now, capable of storing data. In SQL server, we call them Indexed views and in Oracle, Materialized views.
Let's now, look at an example of creating an Indexed view. For the purpose of this video, we will be using tblProduct and tblProductSales tables.
Script to create table tblProduct
Create Table tblProduct
(
ProductId int primary key,
Name nvarchar(20),
UnitPrice int
)
Script to pouplate tblProduct, with sample data
Insert into tblProduct Values(1, 'Books', 20)
Insert into tblProduct Values(2, 'Pens', 14)
Insert into tblProduct Values(3, 'Pencils', 11)
Insert into tblProduct Values(4, 'Clips', 10)
Script to create table tblProductSales
Create Table tblProductSales
(
ProductId int,
QuantitySold int
)
Script to pouplate tblProductSales, with sample data
Insert into tblProductSales values(1, 10)
Insert into tblProductSales values(3, 23)
Insert into tblProductSales values(4, 21)
Insert into tblProductSales values(2, 12)
Insert into tblProductSales values(1, 13)
Insert into tblProductSales values(3, 12)
Insert into tblProductSales values(4, 13)
Insert into tblProductSales values(1, 11)
Insert into tblProductSales values(2, 12)
Insert into tblProductSales values(1, 14)
tblProduct Table
tblProductSales Table
Create a view which returns Total Sales and Total Transactions by Product. The output should be, as shown below.
Script to create view vWTotalSalesByProduct
Create view vWTotalSalesByProduct
with SchemaBinding
as
Select Name,
SUM(ISNULL((QuantitySold * UnitPrice), 0)) as TotalSales,
COUNT_BIG(*) as TotalTransactions
from dbo.tblProductSales
join dbo.tblProduct
on dbo.tblProduct.ProductId = dbo.tblProductSales.ProductId
group by Name
If you want to create an Index, on a view, the following rules should be followed by the view. For the complete list of all rules, please check MSDN.
1. The view should be created with SchemaBinding option
2. If an Aggregate function in the SELECT LIST, references an expression, and if there is a possibility for that expression to become NULL, then, a replacement value should be specified. In this example, we are using, ISNULL() function, to replace NULL values with ZERO.
3. If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression
4. The base tables in the view, should be referenced with 2 part name. In this example, tblProduct and tblProductSales are referenced using dbo.tblProduct and dbo.tblProductSales respectively.
Now, let's create an Index on the view:
The first index that you create on a view, must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes.
Create Unique Clustered Index UIX_vWTotalSalesByProduct_Name
on vWTotalSalesByProduct(Name)
Since, we now have an index on the view, the view gets materialized. The data is stored in the view. So when we execute Select * from vWTotalSalesByProduct, the data is retrurned from the view itself, rather than retrieving data from the underlying base tables.
Indexed views, can significantly improve the performance of queries that involves JOINS and Aggeregations. The cost of maintaining an indexed view is much higher than the cost of maintaining a table index.
Indexed views are ideal for scenarios, where the underlying data is not frequently changed. Indexed views are more often used in OLAP systems, because the data is mainly used for reporting and analysis purposes. Indexed views, may not be suitable for OLTP systems, as the data is frequently addedd and changed.
Thank you So much Venkat,For your amazing You tube Chanel,
ReplyDeleteYou are the only guy which will make things very easy
Believe it or not folks.. i got the job bcoz as .net developer bcoz of venkat sir where in system test i got the question like insert the data into the table and display it in gridview on single button click both should happen at the same time.. i did it without any errors.. All the credit goes to Venkat sir...
ReplyDeletethanQ Soo much venkat..Now i get more clarity about indexs and views
Deletei support your saying .. most condidates who are following his lessons , are getting better jobs.
ReplyDeleteThanks a lot. I wish this video was there in 2010 itself. Evry concept was so confusing then with your videos they seem so simple and understandable.
ReplyDeleteThank you sir for such a nice tutorial.
ReplyDeleteI wanted to know when the materialized view is updated, i.e. if I update a row in base table then how that data is updated in materialized view.
ReplyDeletecreate VIEW idx_vw_productwisesales
WITH SCHEMABINDING
AS
SELECT name, sum(isnull((quantitysold*unitprice),0)) AS [total sale],
count_big(*) as [total trasation] FROM dbo.product
JOIN dbo.productsales
ON dbo.product.productid=dbo.productsales.productid
GROUP BY name
create UNIQUE CLUSTERED INDEX unclusidx_vw_productwisesales ON idx_vw_productwisesales(name)
indexed view is created but when i use
sp_helpindex unclusidx_vw_productwisesales
it showing error
Msg 15009, Level 16, State 1, Procedure sp_helpindex, Line 38
The object 'unclusidx_vw_productwisesales' does not exist in database 'sql_db' or is invalid for this operation.
but the indexed view is created in my db
can anyone help me out
sp_helpindex idx_vw_productwisesales.
Deletesp_helpindex must be executed on an object (in your case it is a view) to know what are the indexes present on that particular object.
You have to use sp_helptext unclusidx_vw_productwisesales
ReplyDeleteInstead of sp_helpindex unclusidx_vw_productwisesales
because it is view not an index.
What happens when we Update the Indexed Views,Either the Data in the Base table gets Updated
ReplyDeleteHow does we know indexed view may contain data or not
ReplyDeleteI would like to know how can i accomplish this exercise with foreign key constraint, because currently is not any relation between two tables. How can the User knows which Article which ID has?
ReplyDelete