SQL Server: Indexed View acquires a Lock on Data

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:

Open a new session and execute below insert statement:

Open another second session and execute below insert statement:

Above both statements completed successfully without blocking to each other.
Now execute ROLLBACK in both the sessions:

Let me create an Indexed View:

Create an unique index on view:

Now again, execute below queries:
Open a new session and execute below insert statement:

Open another second session and execute below insert statement:

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.


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz