Need Sugestion on Which Columns I need To Create A NonClustered Index?

Question

Post reply

Need Sugestion on Which Columns I need To Create A NonClustered Index?

Anjan@Sql

SSCrazy

Points: 2316
More actions
June 18, 2013 at 2:58 am

#278813

I have a huge amount of data table EMP with 15 columns, and it has only one primary key with its clustered index, no other indexes are there on table columns.
Now I want to create a non clustered index on this table. How you come to know what are the columns should be there in new index. I dont want to search for how many procedures are using EMP and what columns are they using.
Tell me the way to create Index.

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

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

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

You need to examine the procedures using the EMP table and see what columns they're using.

http://www.sqlservercentral.com/articles/Indexing/68439/

http://www.sqlservercentral.com/articles/Indexing/68563/

http://www.sqlservercentral.com/articles/Indexing/68636/

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

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 2

Thanks Gila Monster for the reply.But There is an exception with out searching which procedures are getting used

the table and what are the columns getting used is it possible to identify the non clustered index columns?

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Ananth@Sql (6/18/2013)
Thanks Gila Monster for the reply.But There is an exception with out searching which procedures are getting used
the table and what are the columns getting used is it possible to identify the non clustered index columns?

No. You can use tools like the SQL Server Profiler and DTA to get some suggestions.

But these are only suggestions which should never be accepted without proper analysis and testing.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

Ananth@Sql (6/18/2013)
But There is an exception with out searching which procedures are getting used the table and what are the columns getting used is it possible to identify the non clustered index columns?

No. Did you read the articles I referenced?

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

Neeraj Prasad Sharma Ten Centuries Points: 1315 More actions · Answer 5

GilaMonster (6/18/2013)
You need to examine the procedures using the EMP table and see what columns they're using.
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/

Thanks GILA for sharing all three article 🙂

Neeraj Prasad Sharma
Sql Server Tutorials