Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…
I have heard many people saying that if they create view and index on it, this will reduce the load on 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 view does not pick up the index created on 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 base table. If you assume that after creating the index on view, the view is materialized and has no relation with original table, then this can very well be incorrect.
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
-- 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),
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
-- Create Index on Table
CREATE UNIQUE CLUSTERED INDEX [IX_OriginalTable] ON mySampleTable
-- Select from table
-- Create View
CREATE VIEW SampleView
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
-- Select from view
Now let us observe the execution plan and the usage of index along with 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 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 (http://blog.sqlauthority.com)