Execution Plans

  • 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.


    Sameer Raval

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance

  • 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. 

  • 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.

  • here's a link to Chris's article on cluster that index

    **ASCII stupid question, get a stupid ANSI !!!**

  • 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.

  • 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.

  • I like this type of Article. Excellent.

  • Excellant article.  I think theme of the article is more important than his English.  Hope to c some more articles from Vijay Kumar


  • 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.


    Kindest Regards,

    Amit Lohia

  • Good, informative and simple.



  • Excellent article. Simple and Practical.Followup to this article necessary on when to use various scans etc.



  • 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.

  • Thanks so much ...

    Article has cleared all the doubt.


  • 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

  • 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