This article is half-done without your Comment! *** Please share your thoughts via Comment ***
A database view with the index is called as an Indexed view or Materialized view which has dedicated storage space for their indexes.
People are suggesting to use an Indexed view for better performance, but this is not 100% correct. There are lots factors need to consider before using an Indexed view like database lock, usage of base table indexes, storage of additional indexes, maintenance of indexes.
I would suggest, please avoid the use of Indexed view because it also increases the lock contention which causes to deadlock. There are very rare situations, where you can think of an Indexed view.
In this post, I will demonstrate on how Indexed view acquires a lock on data. When you create an Indexed view, your base table data need to modify indexes of view as well which requires an additional lock.
Create a sample table:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE dbo.tbl_Students ( StudID INT ,StudName VARCHAR(20) ,Class CHAR(1) ,CONSTRAINT pk_tbl_Students_StudID PRIMARY KEY(StudID) ); GO |
Open a new session and execute below insert statement:
|
1 2 3 4 |
BEGIN TRANSACTION INSERT INTO dbo.tbl_Students VALUES (1,'Anvesh','A') GO |
Open another second session and execute below insert statement:
|
1 2 3 4 |
BEGIN TRANSACTION INSERT INTO dbo.tbl_Students VALUES (2,'Neevan','A') GO |
Above both statements completed successfully without blocking to each other.
Now execute ROLLBACK in both the sessions:
|
1 2 |
ROLLBACK GO |
Let me create an Indexed View:
|
1 2 3 4 5 6 7 8 |
CREATE VIEW dbo.vw_tbl_Students WITH SCHEMABINDING AS SELECT COUNT_BIG(*) AS StudCount ,Class FROM dbo.tbl_Students GROUP BY Class GO |
Create an unique index on view:
|
1 2 |
CREATE UNIQUE CLUSTERED INDEX idx_tbl_students_class ON dbo.vw_tbl_Students(Class); |
Now again, execute below queries:
Open a new session and execute below insert statement:
|
1 2 3 4 |
BEGIN TRANSACTION INSERT INTO dbo.tbl_Students VALUES (1,'Anvesh','A') GO |
Open another second session and execute below insert statement:
|
1 2 3 4 |
BEGIN TRANSACTION INSERT INTO dbo.tbl_Students VALUES (2,'Neevan','A') GO |
At this time, You can find the second query in blocking state, and it is waiting for your first query to complete because the first query also held the key to Indexed view.
