Click here to Skip to main content
Click here to Skip to main content

Limitations of the View Many and Many

, 13 Oct 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
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!

History

  • 13th October, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

pinaldave
Founder http://blog.SQLAuthority.com
India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 13 Oct 2010
Article Copyright 2010 by pinaldave
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid