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 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 UNIQUE CLUSTERED INDEX [IX_SampleView] ON mySampleTable
(
ID1 ASC
)
GO
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT ID1,ID2,SomeData
FROM dbo.mySampleTable
GO
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
SELECT ID1,ID2,SomeData
FROM mySampleTable
GO
SELECT ID1,ID2,SomeData
FROM SampleView
GO
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