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»»»

Execution Plans Expand / Collapse
Author
Message
Posted Monday, May 09, 2005 10:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:05 AM
Points: 2,553, Visits: 559
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 !!!**
Post #180892
Posted Friday, October 07, 2005 1:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 9:02 PM
Points: 79, Visits: 71

Very nice article...

Post #227013
Posted Friday, October 07, 2005 5:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, March 02, 2014 5:07 PM
Points: 480, Visits: 442

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.




Post #227056
Posted Friday, October 07, 2005 6:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:36 AM
Points: 590, Visits: 839

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.



Regards, Mike
Post #227081
Posted Friday, October 07, 2005 7:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 05, 2013 8:19 AM
Points: 366, Visits: 165

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.

Post #227093
Posted Friday, October 07, 2005 7:10 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 13, 2013 8:04 PM
Points: 545, 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

Post #227094
Posted Friday, October 07, 2005 8:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 9:11 AM
Points: 151, Visits: 28
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. 


Post #227120
Posted Friday, October 07, 2005 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2008 11:45 AM
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.
Post #227177
Posted Friday, October 07, 2005 10:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:05 AM
Points: 2,553, Visits: 559
here's a link to Chris's article on cluster that index







**ASCII stupid question, get a stupid ANSI !!!**
Post #227190
Posted Friday, October 07, 2005 10:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 09, 2005 10:22 AM
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.
Post #227193
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse