May 6, 2010 at 8:39 am
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?
May 6, 2010 at 8:53 am
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
May 6, 2010 at 9:07 am
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:
May 6, 2010 at 9:09 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 6, 2010 at 9:11 am
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
May 6, 2010 at 9:40 am
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.
May 6, 2010 at 9:50 am
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
May 6, 2010 at 10:09 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply