October 7, 2005 at 7:10 am
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
October 7, 2005 at 8:08 am
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.
October 7, 2005 at 9:41 am
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.
October 7, 2005 at 10:07 am
here's a link to Chris's article on cluster that index
**ASCII stupid question, get a stupid ANSI !!!**
October 7, 2005 at 10:10 am
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.
October 7, 2005 at 10:31 am
I felt that this could be confusing for a beginner because the author was trying to optimize a simple query that couldn't be optimized.
Instead the author changed the requirements of the query to:
1. put all the data into a "covering index", so you get another full table scan, but in this case the table is known as an index (it's still a table)
2. put some constraints on the result set to allow the use of indexes that had been created
I really thought that this was backwards to change requirements to fit the technological implementation.
October 9, 2005 at 1:59 am
I like this type of Article. Excellent.
October 10, 2005 at 5:48 am
Excellant article. I think theme of the article is more important than his English. Hope to c some more articles from Vijay Kumar
October 12, 2005 at 12:55 pm
Nice article. Vijay, keep writing. Take this article to next level (explain different types of joins hints , covering index). I wish I get time to write on these lines.
Amit Lohia
October 12, 2005 at 4:00 pm
Good, informative and simple.
Cheers
Glenn
October 19, 2005 at 4:36 am
Excellent article. Simple and Practical.Followup to this article necessary on when to use various scans etc.
Anand
November 3, 2005 at 2:02 am
This article was overly laborious and not cutting to the point. It would be nice to see articles on this site rated on level of expertise - e.g. beginner, intermediate etc. in relation to the subjects they discuss - so that one has an an indication as to whether or not one will learn anything from reading them. This would be a beginner's article.
April 17, 2006 at 6:22 pm
Thanks so much ...
Article has cleared all the doubt.
May 4, 2006 at 4:31 am
hello sir,
I read your article and found it was so much facinating.
i was familiar with the word Execution plan but not knowing much about it.
Your article give me brief information about How to utilize performance using clustered index seek Thanks a lot
May 14, 2006 at 4:49 pm
Found this article very average and a bit too self congratulating.
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply