SQL SERVER – Indexed View always Use Index on Table

SQL SERVER - Indexed View always Use Index on Table TSQL2sDay This blog post is written in response to T-SQL Tuesday hosted by Shankar Reddy.

I have been recently writing about Views and their Limitations. While writing this article series, I got inspired to write about SQL Server Quiz Questions. You can view the Quiz Question posted over here.

In SQL Server 2005, a single table can have maximum 249 non clustered indexes and 1 clustered index. In SQL Server 2008, a single table can have maximum 999 non clustered indexes and 1 clustered index. It is widely believed that a table can have only 1 clustered index, and this belief is true. I have some questions for all of you. Let us assume that I am creating view from the table itself and then create a clustered index on it. In my view, I am selecting the complete table itself.

USE tempdb
GO
-- Create sample Table
CREATE TABLE mySampleTable(ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable(ID1,ID2,SomeData)
SELECT TOP 1000 ROW_NUMBER()OVER (ORDER BY o1.name),
ROW_NUMBER()OVER (ORDER BY o1.name DESC),
o1.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create Index on Table
CREATE UNIQUE CLUSTERED INDEX [IX_SampleView] ON mySampleTable
(
ID1 ASC
)
GO
-- Create sample 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
-- Enable Execution Plan using CTRL + M
SELECT ID1,ID2,SomeData
FROM mySampleTable
GO
SELECT ID1,ID2,SomeData
FROM SampleView
GO
-- Clean up
DROP VIEW SampleView
DROP TABLE mySampleTable
GO

Now run the following script and answer these questions:

Q1. Does the table use an index created on itself?
Q2. Does the view use an index created on itself?
Q3. Do both the queries use the same index? If yes, why? If no, why not?

The answers are very clear.

SQL SERVER - Indexed View always Use Index on Table indexview1_1

SQL SERVER - Indexed View always Use Index on Table indexview2

The answers are very clear.

A1: Yes
A2: No
A3: Read the rest of the blog! or visit BeyondRelationa.com (where you can win iPAD as well).

Usually, the assumption is that Index on table will use Index on table and Index on view will be used by view. However, that is the misconception. It does not happen this way. In fact, if you notice the image, you will find the both of them (table and view) use both the index created on the table. The index created on the view is not used. The reason for the same as listed in BOL.

The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. This often happens when the indexed view is defined on small tables. You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don’t initially reference the view explicitly. You can get the actual cost of the query with NOEXPAND and compare it to the actual cost of the query plan that doesn’t reference the view. If they are close, this may give you the confidence that the decision of whether or not to use the indexed view doesn’t matter.

Various answers to this question are given over here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Presenting at South East Asia SharePoint Conference – Maintaining SQL Server at Optimal Performance for Blazing Fast SharePoint Site
Next Post
SQL SERVER – Visiting Alma Mater – Delivering Session on Database Performance and Career – Nirma Institute of Technology

Related Posts

8 Comments. Leave new

  • Hi,

    It quite logical, the expanding I mean, when I finally figured it out. If you use index in the view, you need to scan/seek through it and then go to the table and use it’s clustered index to read the actual row data. With table you need only to use table’s clustered index.

    Reply
  • Nice explanation of how indexes on views work.
    Thank you

    Reply
  • I am focusing on your articles since 3 months.These are very impressive.
    I am using queris without indexing and result is very slow performance.I am fadded of that kind of results.
    I want to use Indexing in my queries for fast results.
    Can you tell me where can i find all articles regarding Indexes.
    I just want those articles from where i can easily find all articles related on Indexes.
    Any guy if can help then please send me on my email below:

    Thanks
    —————————

    Reply
  • Hi,
    Thanks for ur valuable post it helped me lot to Crack interviews on indexing

    Reply
  • If view view use an index created on table not created on itself by default(can be use index on itself by using hints no NOEXPAND).

    Whats the benefit of indexed view when no hints(NOEXPAND) is used in the query created on view.

    Reply
  • Prasanna Kalahasthi
    January 20, 2014 3:16 pm

    COULD YOU PLEASE HELP ME IN ANSWERING THIS QUESTION.. “HOW TO INSERT AN IMAGE INTO A TABLE IN SQL SERVER?”

    Reply
  • What is the difference in working of index seek on clustered and non clustered index?

    Reply

Leave a Reply

Menu