Update: Please read the summary post of all the 11 Limitation of the view SQL SERVER – The Limitations of the Views – Eleven and more… Let us learn about Index Created on View not Used Often.
I have heard many people saying that if they create a view and index on it, this will reduce the load on the original table as all the subsequent queries on view will not access the basic table. This is not true always and the view may not give you the performance optimizations which you are looking for.
In the following example, we will create the base table first. We will then create the view on top of it and create the index on the view. Subsequently, we will run the simple SELECT statement on base table and on the view. It is noticed that the view does not pick up the index created in the view, but uses the base index created on the original table. It will also expand the underlying table and use the index created on the base table. If you assume that after creating the index on the view, the view is materialized and has no relation with original table, then this can very well be incorrect.
USE tempdb GO IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]')) DROP VIEW [dbo].[SampleView] GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U')) DROP TABLE [dbo].[mySampleTable] GO -- Create SampleTable CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100)) INSERT INTO mySampleTable (ID1,ID2,SomeData) SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name), ROW_NUMBER() OVER (ORDER BY o2.name), o2.name FROM sys.all_objects o1 CROSS JOIN sys.all_objects o2 GO -- Create Index on Table CREATE UNIQUE CLUSTERED INDEX [IX_OriginalTable] ON mySampleTable ( ID1 ASC ) GO -- Select from table SELECT ID1,ID2,SomeData FROM mySampleTable GO -- Create View CREATE VIEW SampleView WITH SCHEMABINDING AS SELECT ID1,ID2,SomeData FROM dbo.mySampleTable GO -- Create Index on View CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView] ( ID2 ASC ) GO -- Select from view SELECT ID1,ID2,SomeData FROM SampleView GO
Now let us observe the execution plan and the use of index along with the query.
Querying Original Table
You might have noticed that the View used the same index as original table and did not use the index created on the table. The reason is as follows: the optimizer has decided that the original index will be more beneficial in query plan than the newly created index. I tried clearing the cache, but the same result is obtained every time.
If you really want your view to act as an independent table and have no relation with the original underlying table, then you can use the hint [noexpand] and the view will not expand. We will see this in some other article in future.
Let me know if you want to read about any other limitation of the view.
Reference: Pinal Dave (https://blog.sqlauthority.com)