Indexed views in sql server - Part 41

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.

12 comments:

  1. Thank you So much Venkat,For your amazing You tube Chanel,
    You are the only guy which will make things very easy

    ReplyDelete
  2. 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...

    ReplyDelete
    Replies
    1. thanQ Soo much venkat..Now i get more clarity about indexs and views

      Delete
  3. i support your saying .. most condidates who are following his lessons , are getting better jobs.

    ReplyDelete
  4. Thanks 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.

    ReplyDelete
  5. Thank you sir for such a nice tutorial.
    I 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.

    ReplyDelete

  6. create 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

    ReplyDelete
    Replies
    1. sp_helpindex idx_vw_productwisesales.
      sp_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.

      Delete
  7. You have to use sp_helptext unclusidx_vw_productwisesales

    Instead of sp_helpindex unclusidx_vw_productwisesales

    because it is view not an index.

    ReplyDelete
  8. What happens when we Update the Indexed Views,Either the Data in the Base table gets Updated

    ReplyDelete
  9. How does we know indexed view may contain data or not

    ReplyDelete
  10. I 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

It would be great if you can help share these free resources