Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Limitations of the View Many and Many

4.50/5 (2 votes)
13 Oct 2010CPOL2 min read 12.9K  
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.

SQL
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)