Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execution Plans


Execution Plans

Author
Message
sushila
sushila
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2565 Visits: 639
Ties in well with the other article on "order by" - despite language issues, the step by step approach with snapshots etc. is very easy to understand.







**ASCII stupid question, get a stupid ANSI !!!**
Agson Chellakudam
Agson Chellakudam
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 83

Very nice article...


dougjjj
dougjjj
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 644

Not bad, a little basic though. Would have been nice to see what happened when you had multiple columns in the where clause or if you joined to a second table.





Mike Hinds
Mike Hinds
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 1074

Excellent explanations. I appreciate that English is not his first language; however his knowledge of the topic and exhaustive examples provide a much clearer lesson than I have been able to get from BOL.

 

I look forward to reading more from this author.



Mike Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
elaine-131617
elaine-131617
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 170

I opened this article because I "knew I should" but wasn't really looking forward to it. Sometimes these kinds of articles get dull and then very confusing (probably because I'm having difficulty paying attention). This article was clear and well thought out.  The step by step examples with screenshots made it very easy to follow along.  Thanks for a great article.


Sameer Raval
Sameer Raval
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 Visits: 255

Excellent Article.

I Like the way you explain, example helps more than 100' s of lines of description. Obviously the target audience is sql server dba/developer so it's always  good not to start every article with how to create indexes. More articles are expected from you.

Thanks,

Sameer Raval



Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
buktseat
buktseat
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 29
Clearly the writer is knowledgable, but this cat definitely needs a proofreader or a ghost-writer or something...  Very hard to understand and follow at times.



William Kimball
William Kimball
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
In reply to: "I would also like to see some examples of when to use clustered index and when to use non-clustered index if possible."

Start with the behavior of the two different index types and the answer starts to become clear.

1. The "natural order" of records in a table is the physical order of the records in the table as they would be scanned from first to last without an index or sort order.
2. A "clustered index" physically reorganizes every record in the table such that the "natural order" of the records in the table becomes the logical sort order of the clustered index. A seperate index records at which record the selected column value(s) change. This is why there can be only one "clustered index" per table.
3. A "non-clustered index" leaves the records as they are in the table and creates a seperate lookup tree of only the column(s) for which the index is built. Each node of this tree points to the location in the table of the corresponding record value.

In short:
1. Use a "clustered index" only on values that are highly repetetive in a table and for which you often select sequential ranges of data. The "clustered index" will then very quickly return entire blocks of matching records.
2. Use a regular index on values that are generally distinct and for which you often return individual records.

Rule of Thumb: If you seek values from a table based on a WHERE clause of a single column, be sure to have an index on it. If the values of the column are highly repetitive, use a clustered index. A column that will have a distinct value for every record is a poor candidate for a clustered index.
sushila
sushila
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2565 Visits: 639
here's a link to Chris's article on cluster that index







**ASCII stupid question, get a stupid ANSI !!!**
David Poole-249495
David Poole-249495
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 1
You can force a Clustered Index Seek by adding a seemingly superfluous WHERE clause that searches for values in that index between two values.

For example, if the clustered index was on a field called StateId and StateId was a TINY INT then we could write add a WHERE clause that says

WHERE StateId BETWEEN 0 AND 255

You can get a performance boost this way but a clustered index seek DOES NOT always result in a lower cost query. You have to experiment with your application to see what the affect is.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search