SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execution Plans


Execution Plans

Author
Message
sushila
sushila
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13989 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
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 Visits: 83

Very nice article...


dougjjj
dougjjj
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1027 Visits: 648

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
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1970 Visits: 1148

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
SSChasing Mays
SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)

Group: General Forum Members
Points: 620 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 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 Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 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
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13989 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
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 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