Limitations of the View Many and Many

Introduction

In SQL Server 2005, a single table can have a maximum of 249 non clustered indexes and 1 clustered index. In SQL Server 2008, a single table can have a maximum of 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.

The answers are very clear.

• A1: Yes
• A2: No
• A3: Read the rest of the blog!

• SQL SERVER – ORDER BY Does Not Work – Limitation of the View 1
• SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the View 2
• SQL SERVER – Index Created on View not Used Often – Limitation of the View 3
• SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4
• SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
• SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
• SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
• SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8
• SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
• SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10
• SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11

History

• 13^th October, 2010: Initial post