Developing Matt

My Technical Journal

Order This.

leave a comment »

When you view the execution plan sometimes you see the scan as ordered, sometimes no.  What does this mean?  It just means that (when not ordered) the access method did not rely on the linked list (that part of the leaf node that points to the next node.  Definition Here).  Why wouldn’t it rely on the linked list?  Well, it depends on your query and your index doesn’t it.  If you group or order or use a range or it can utilize a Nonclustered index (based upon your query) then it will use a linked list and therefore be ordered.  It is a good thing, that is, until fragmentation occurs. 

(I am nearing the end of chapter 3 of ‘Inside Microsoft Sql Server 2005: t-sql querying” by Itzik Ben-Gan and was struck by the ordered vs. non-ordered query plans which led me to trying to figure out when and why it was ordered and this is my synopsis of that digging.)

Advertisements

Written by matt

January 22, 2009 at 2:44 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: