About Index - Can we create a nonclustered index on clustered index column

Question

About Index - Can we create a nonclustered index on clustered index column

p.shabbir

SSCrazy

Points: 2404
More actions
April 5, 2016 at 6:00 am

#317673

Hi All,
I know that we can create a nonclustered index on a clustered index column.
Can anyone let me know is it useful by doing so.
Does that improve performance?
Thnx In Advnc.
🙂

Viewing 13 posts - 1 through 12 (of 12 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

It will increase performance if the index is used by the optimizer for SELECT statements. Say the column name is C1 and you execute: SELECT C1 FROM youratable... The optimizer will use your nonclustered index. It you are sorting or grouping by other columns then they should be included in the index as well. If you are simply including other columns in your query these should be included as INCLUDE columns which will make the index a "covering index."

Note that indexes slow down data modification. That's something else to consider.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

p.shabbir SSCrazy Points: 2404 More actions · Answer 2

Appreciate for the prompt response Alan.

I have query like below

Select C1 from mytable where C1 = Somevalue

Which index is used in the above query?

Again Thnx.

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

The smallest one that supports the query.

While you can create a nonclustered and a clustered index on the same column, there's usually not much point in doing so. Unless the minimal difference between the larger and smaller index is critical to your system (usually meaning your queries are sensitive to 1 or 2 millisecond difference), then the nonclustered index is likely to be more overhead than use. The more columns you add to the nonclustered index (for grouping or sorting or as include columns) the smaller the difference between the two indexes will be

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

p.shabbir SSCrazy Points: 2404 More actions · Answer 4

Thnx Gail,

So, finally no use of creating nonclustered index on clustered indexed column since there will be diff in milliseconds for both indexes right?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

The best way to find out is to try it. But do you really have a query like that? If so, you might consider redesigning your application, because that query can't return anything useful.

John

p.shabbir SSCrazy Points: 2404 More actions · Answer 6

Thnx John and all.

No, I don't have a query. I was reading about indexes and suddenly strike into my mind this. And I thought to check with the most talented guys (Like you all in this blog). And every time i learnt a many news things and almost my questions are answered.

If I get anydoubt the first blog comes into my mind is sqlserver central.com

Definitely I will try.

Thnx all for your help.:-)

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 7

p.shabbir (4/5/2016)
Thnx Gail,
So, finally no use of creating nonclustered index on clustered indexed column since there will be diff in milliseconds for both indexes right?

Just to clarify, are you referring to single column index or a multi column index? A clustered index on COL_A and non clustered index on COL_A vs a clustered index on COL_A, COLB, COL_C and a non clustered index on say COL_C are two different scenarios.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

Indexing is a complex topic indeed. I huge part of the game is understanding how your server is using them. I always develop SQL with "Include actual Execution Plan" turned on. A good book that will help you understand indexes is SQL Server Execution Plans by Grant Fritchey. Also not the POC indexing strategy discussed in this article: How to Write T-SQL Window Functions, Part 3 That strategy does not only apply to Window Functions and has helped me a great deal.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

p.shabbir SSCrazy Points: 2404 More actions · Answer 9

ZZartin (4/5/2016)
p.shabbir (4/5/2016)
Thnx Gail,
So, finally no use of creating nonclustered index on clustered indexed column since there will be diff in milliseconds for both indexes right?
Just to clarify, are you referring to single column index or a multi column index? A clustered index on COL_A and non clustered index on COL_A vs a clustered index on COL_A, COLB, COL_C and a non clustered index on say COL_C are two different scenarios.

ZZartin,

I am referring to first scenario.

A clustered index on COL_A and non clustered index on COL_A

And if you don't mind can you let me know about second scenario.

a clustered index on COL_A, COLB, COL_C and a non clustered index on say COL_C

p.shabbir SSCrazy Points: 2404 More actions · Answer 10

Alan.B (4/5/2016)
Indexing is a complex topic indeed. I huge part of the game is understanding how your server is using them. I always develop SQL with "Include actual Execution Plan" turned on. A good book that will help you understand indexes is SQL Server Execution Plans by Grant Fritchey. Also not the POC indexing strategy discussed in this article: How to Write T-SQL Window Functions, Part 3 That strategy does not only apply to Window Functions and has helped me a great deal.

ThankS Alan,

Will go through the books.

Thnx Again.:-)

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

p.shabbir (4/5/2016)
And if you don't mind can you let me know about second scenario.
a clustered index on COL_A, COLB, COL_C and a non clustered index on say COL_C

Those are two completely different indexes.

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

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

ScottPletcher SSC Guru Points: 101118 More actions · Answer 12

Yes, creating a non-clustered index on the clus index column(s) can indeed help performance for certain queries. It's a valid thing to do when that situation exists.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".