Advantage of having unique non clustered index same as clustered index on a table

Question

Advantage of having unique non clustered index same as clustered index on a table

Pvm_25

Ten Centuries

Points: 1073
More actions
May 6, 2010 at 8:39 am

#234669

Hello guys,
Is there any advantage of having a non clustered index same as a clustered index on a table?.
Here is a little background for my question: In our current data warehouse framework (sql server version - 2005), In all of the tables we have one unique non clustered index same as a clustered index. So I was wondering if there is any advantage of doing that.
From my knowledge I think database engine will always pick clustered index over non clustered index (if they both have have column(s) in same order) because clustered index contains data at the leaf level.
whats your thoughts about this?

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Highly unlikely to be useful. There are edge-cases where it can be, but very rare. SQL will use the NC if it thinks it'll be faster, but is rare unless it's covering and much smaller than cluster.

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

Pvm_25 Ten Centuries Points: 1073 More actions · Answer 2

thanks for the reply Gail...

as I said...non-clustered index is of the same structure as clustered...same columns, same order n no included columns.....Only difference I see is in size....n that's obvious because one is physical and one is logical......for ex: one of my fact table has clustered index of size 3.1 GB n identical non clustered index of size 1.0 GB......

so do you think that is considerable difference that engine uses NC over Clust.? ....I am bit confused about that because eventually Non clust. index goes to clust. index to get the data.

Is it something like because of the small size its faster to travel through b-tree of non clust. index? :unsure:

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

The only way I think this is beneficial is that if you are covering a query with the NC, then it is less IO to seek to values in the index because it's physically smaller. However I wouldn't think that would fit a lot of cases.

I typically eliminate duplicates since they impact insert/update/delete performance.

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

As I said, highly unlikely to be useful, there are edge cases, but they are rare. It is very uncommon to have a useful perf improvement by adding a NC index on the same columns as the cluster.

Test out your queries, see if the performance improvement (if any) warrents the increased space used and impact on inserts/updates.

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

Pvm_25 Ten Centuries Points: 1073 More actions · Answer 5

I think I'll check performance for SELECT queries dropping/adding that identical non clust. index.

for the insert/update/delete i think it won't be a problem because we disable all non clust. indexes before load everynight.

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

It'll have an indirect impact. It'll increase the time to rebuild those indexes. Plus there's the space considerations.

You don't insert/update/delete any other time than the overnight load?

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

Pvm_25 Ten Centuries Points: 1073 More actions · Answer 7

Yup, true...time and space were also my concerns after performance and that's why I started this question....

And No, its a data warehouse for reporting purpose, So no insert/update/delete operations during day time.