SQL SERVER – Index Created on View not Used Often – Observation of the View – Part 2
- by pinaldave
Earlier, I have written an article about SQL SERVER – Index Created on View not Used Often – Observation of the View. I received an email from one of the readers, asking if there would no problems when we create the Index on the base table.
Well, we need to discuss this situation in two different cases.
Before proceeding to the discussion, I strongly suggest you read my earlier articles. To avoid the duplication, I am not going to repeat the code and explanation over here.
In all the earlier cases, I have explained in detail how Index created on the View is not utilized.
SQL SERVER – Index Created on View not Used Often – Limitation of the View 12
SQL SERVER – Index Created on View not Used Often – Observation of the View
SQL SERVER – Indexed View always Use Index on Table
As per earlier blog posts, so far we have done the following:
Create a Table
Create a View
Create Index On View
Write SELECT with ORDER BY on View
However, the blog reader who emailed me suggests the extension of the said logic, which is as follows:
Create a Table
Create a View
Create Index On View
Write SELECT with ORDER BY on View
Create Index on the Base Table
Write SELECT with ORDER BY on View
After doing the last two steps, the question is “Will the query on the View utilize the Index on the View, or will it still use the Index of the base table?“
Let us first run the Create example.
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 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
ORDER BY ID2
GO
-- Create Index on Original Table
-- On Column ID1
CREATE UNIQUE CLUSTERED INDEX [IX_OriginalTable] ON mySampleTable
(
ID1 ASC
)
GO
-- On Column ID2
CREATE UNIQUE NONCLUSTERED INDEX [IX_OriginalTable_ID2] ON mySampleTable
(
ID2
)
GO
-- Select from view
SELECT ID1,ID2,SomeData
FROM SampleView
ORDER BY ID2
GO
Now let us see the execution plans for both of the SELECT statement.
Before Index on Base Table (with Index on View):
After Index on Base Table (with Index on View):
Looking at both executions, it is very clear that with or without, the View is using Indexes.
Alright, I have written 11 disadvantages of the Views. Now I have written one case where the View is using Indexes. Anybody who says that I am being harsh on Views can say now that I found one place where Index on View can be helpful.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL View, SQLServer, T SQL, Technology
