Developing Matt

My Technical Journal

What Itzik Taught me Part 3: Ordering Your Relations

leave a comment »

A relational database isn’t called so because of how it relates to other tables.  It is called so because it is based upon set theory.  It is centered around the mathematical term relation which emphasizes how things should be grouped (or set) together.  A relational table has like items, that’s all.  This is why you can’t add an order to a view or a derived table.  The concept of ordering is in direct contrast to the relation concept.

For example, think of how you would order your family members.  For me, I would order myself first.  I would place myself first in order of importance.  Mother might wrinkle her nose at this.  Brother might disagree and want to fight me over it.  Father might wait and see how the fight turns out and laugh at us on the lawn, two middle aged guys fighting over who should come first. 

There is no ‘first’ in relation.  We are all equal.  Even if your mother likes putting her flowers in a toilet in her front lawn, you aren’t better than her.  You should still call her every week and ask how they are growing.

However, you can take the group, query a common value and order the result.  Everyone would agree that if we ordered my family in descending order by birth date that I would come first.  No one can argue that.  I’m definitely first there.

This concept is why the engine balks when you attempt to order a view.  A view is a direct representation of the data…data that is organized in groups.  When you select top 100 % …order by 1 you are cheating the system and cheating yourself.  There isn’t any guarantee that it will be ordered because this is not the query giving you an order of the data.  It is a operating as a filter at this point, giving you the top x of data sorted by column 1.  It is saying ‘Well, if column 1 is sorted alphabetically, the top x rows of data are…”

Thanks Itzik for speaking these concepts over and over again to people like me.  i needed to hear it a few times before it sunk in.


Written by matt

June 25, 2011 at 3:41 pm

Posted in Sql Server

Tagged with

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: