Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Introduction to Indexes: Part 3 – The nonclustered index Expand / Collapse
Author
Message
Posted Monday, November 16, 2009 6:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
Comments posted to this topic are about the item Introduction to Indexes: Part 3 – The nonclustered index


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #819337
Posted Wednesday, November 18, 2009 6:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 10,340, Visits: 13,341
Great series Gail. Glad you mentioned the index intersection in this article, I've always had questions about that.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #820769
Posted Wednesday, November 18, 2009 6:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
Thanks Gail,

Well written. I read all 3 and learned a few things.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #820788
Posted Wednesday, November 18, 2009 6:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 1,331, Visits: 19,315
Asking what is probably an obvious question, but from what I understand:

Adding a clustered index that covers multiple columns within a table is not necessarily advantageous, because it will expand all of the non-clustered indexes as well. (from part II)

Therefore, the clustered index should be as narrow as possible, and the non-clustered index should be created as a composite key to assist with querying?

So if I have a heap that only has a unique value by combining three separate columns, I should create a new column with a unique value (using newsequentialid() or some such) and make that the clustered index, but use the composite non-clustered index to help my queries against the table?


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #820789
Posted Wednesday, November 18, 2009 7:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
jcrawf02 (11/18/2009)
Adding a clustered index that covers multiple columns within a table is not necessarily advantageous, because it will expand all of the non-clustered indexes as well. (from part II)


Yes

Therefore, the clustered index should be as narrow as possible, and the non-clustered index should be created as a composite key to assist with querying?


Yes.

So if I have a heap that only has a unique value by combining three separate columns, I should create a new column with a unique value (using newsequentialid() or some such) and make that the clustered index, but use the composite non-clustered index to help my queries against the table?


Maybe.

Remember that clustered index != primary key. While it is a good idea to put the cluster on a unique column, it's not obligatory. SQL can make the clustered index unique (by adding an int behind the scenes) if the column it's defined on is not. Look at what columns exist. Are any of them fairly good candidates for the cluster? Are any reasonably good?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #820815
Posted Wednesday, November 18, 2009 7:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 1,331, Visits: 19,315
ok, so if I have a system generated UID from a different table, but the values are not entirely unique, only a few duplicates in my heap, I could/should use that, as the system would make the clustered index unique in the backend? That was my first instinct, but got derailed when I saw duplicates.

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #820820
Posted Wednesday, November 18, 2009 7:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
jcrawf02 (11/18/2009)
ok, so if I have a system generated UID from a different table, but the values are not entirely unique, only a few duplicates in my heap, I could/should use that, as the system would make the clustered index unique in the backend? That was my first instinct, but got derailed when I saw duplicates.


Maybe. How well does it fit the other guidelines for the clustered index? Narrow, unchanging, ever-increasing? I've often used a 'date inserted' type column for the cluster. Not quite unique, fairly narrow, unchanging, ever-increasing. It's all about what tradeoffs you're willing to make.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #820834
Posted Wednesday, November 18, 2009 7:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 1,331, Visits: 19,315
Thanks, food for thought. Narrow (15 character static IDs), unchanging (check), ever-increasing (not sure, ID has leading alpha characters and is not consistently assigned). In short, not sure that's my best choice, but I'll go back and re-evaluate.

Great series, I'll be reading and re-reading these for a long time.


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #820838
Posted Wednesday, November 18, 2009 9:42 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 808, Visits: 1,993
Great article - All three parts.

In the column order I get that as this is how the index is sorted. When talking about included columns one would guess that the order is no more or less important than the column order in the table itself.


ATB

Charles Kincaid

Post #820957
Posted Wednesday, November 18, 2009 11:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
I agree, a great series of articles explained in a way that is easy to understand. Gail, thanks for taking the time to put this together.
Post #821056
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse