What will you do if you need to create clustered index on more than one field? Is it possible?

 Posted by Bhakti on 11/24/2009 | Category: Sql Server Interview questions | Views: 2696
Answer:

The answer is yes and no.
You can create only one clustered index in table.
Workaround for such scenario can be :
Either you can create index on entire table or you can create clustered index on a view covering entire table.

Suppose, you have created table with following:
CREATE TABLE [dbo].[Table1](

[field1] [int] IDENTITY(1,1) NOT NULL,
[field2] [nchar](10) ,
[field3] [nchar](10) ,
[field4] [nchar](10)
) ON [PRIMARY]

Create index on entire table:
CREATE INDEX idx_coverTable ON Table1(field1, field2) INCLUDE (field3, field4)

Create clustered index on a view covering entire table:
CREATE VIEW v1111 with SCHEMABINDING

AS
SELECT field1, field2, field3, field4 FROM dbo.Table1
GO
CREATE UNIQUE CLUSTERED INDEX idx_viewClustered ON dbo.v1111(field1, field2, field3, field4)
GO


Thanks,
Bhakti Shah


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response