Create a Non-clustured index on multiple columns

Question

Create a Non-clustured index on multiple columns

Shamshad Ali

SSCarpal Tunnel

Points: 4822
More actions
July 25, 2008 at 6:14 am

#130179

I want to know which Non-Clustered index is better to be applied. all 3 in one or each one should have individual index. Like below
CREATE NONCLUSTERED INDEX [IX_index_tbl_Program_Schedule_3Cols] ON [dbo].[tbl_Program_Schedule]
(
[Prog_Details_ID] ASC,
[Sch_End_Time] ASC,
[Sch_Start_Time] ASC,
[Title] ASC
)
go
OR each columns should have individual indexes? Like below:
CREATE NONCLUSTERED INDEX [IX_index_tbl_Program_Schedule_Prog_Details_ID] ON [dbo].[tbl_Program_Schedule]
(
[Prog_Details_ID] ASC
)
go
CREATE NONCLUSTERED INDEX [IX_index_tbl_Program_Schedule_Sch_End_Time] ON [dbo].[tbl_Program_Schedule]
(
[Sch_End_Time] ASC
)
go
CREATE NONCLUSTERED INDEX [IX_index_tbl_Program_Schedule_Sch_Start_Time] ON [dbo].[tbl_Program_Schedule]
(
[Sch_Start_Time] ASC
)
go
CREATE NONCLUSTERED INDEX [IX_index_tbl_Program_Schedule_Title] ON [dbo].[tbl_Program_Schedule]
(
[Title] ASC
)
go
Shamshad Ali.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Abhijit More SSCertifiable Points: 6897 More actions · Answer 1

so you are creating an index ... good!

But let me tell you when we create the index , we need to consider some points like Index Size, Col Searchability, Whether columns will be used in joins or not how frequently and bla bla bla....

I suggest to to go for..

CREATE NONCLUSTERED INDEX [IX_index_tbl_Program_Schedule_3Cols] ON [dbo].[tbl_Program_Schedule]

(

[Prog_Details_ID] ASC,

[Sch_End_Time] ASC,

[Sch_Start_Time] ASC,

)

go

also if you are not searching the data using [Sch_End_Time], [Sch_Start_Time] then remoev these cols.

Abhijit - http://abhijitmore.wordpress.com

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 2

Usually we require to find out schedule start time and end time most frequently and title as well,

So here these are all

[Sch_End_Time] ASC,

[Sch_Start_Time] ASC,

[Title] ASC

I require search on these all columns, I got this script from Database Engine Tunning Advisor and based on Load, it gave me this index to be created as recommendation.

My question is, creating individual column indexes may affect on INSERTIONS OR NOT?

or creating composite column index is better as compared to individuals? and in which case its affect performance (at the time of insertion or selecting rows) ?

Shamshad Ali.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

In general, wider nonclustered indexes are more useful than narrow. You want the indexes to satisfy as much of the where clause of the queries as possible.

Can you post the table structure and a couple of examples of queries that are run often on it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason Crider SSCertifiable Points: 6019 More actions · Answer 4

Isn't it true that a covered index will only be used when the column on the left is part of the query?

So a covered non-clustered index with all three items included would work if all three items were part of the query, but if only Sch_Start_Time and Title were used then the index would not be utilized.

And I'm also thinking that the transactions updating a single column would be less costly to a single index vs. a covered index. I'm open to getting all this wrong but that seems to be what I understood.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter