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

Clustered indexed primary key not in asc order when selected Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 2:39 AM
Points: 6, Visits: 27
Hi All,
I have a table that has a primary key which is a clustered index.
However when I select data based on a different field (not the primary key)
the result is n't sorted by the primary key.
I mean, if a table has a clustered indexed primary key (asc) shouldn't a query return results that are sorted by the primary key as long as not sort order is specified?
When I ran the query, I did not specify any sort order so I am assuming the result will automatically be displayed in the sort order of the primary key init?
it was a simple
select * from table_a where column_b = 'Z'
Please do let me know if you need more info

Thanks
Ugoboy
Post #1422130
Posted Wednesday, February 20, 2013 9:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
ugo boy (2/20/2013)
When I ran the query, I did not specify any sort order so I am assuming the result will automatically be displayed in the sort order of the primary key init?


Incorrect assumption.

Without an order by, you're telling SQL you don't care about the order of the data, so you get it back in whatever order the last query operation left it. It might be the order of the index used to read the data. It might not.

If you care about order, specify the appropriate ORDER BY. If you don't specify an ORDER BY, then you cannot assume anything about the order of the data.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1422143
Posted Wednesday, February 20, 2013 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 2:39 AM
Points: 6, Visits: 27
Thanks Gail for your response. does it mean that a previous query can distort the indexed order?
I just discovered something unusual...
When I ran the query I was talking about, I used a (nolock) hint and that was when the results came back in the unsorted order but when I reran the query without the (nolock) hint, the results came back in the correct ascending order of the clustered indexed primary key.
does a (nolock) hint affect sort orders, can it?
Post #1422161
Posted Wednesday, February 20, 2013 9:59 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 689, Visits: 2,753
Sql Server will scan the table differently for the query's with and without the hints which is why you may be getting a different sort order..

The only way to guarantee the order of the query is to use the ORDER BY regardless of previous results and expectations based upon those..


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1422169
Posted Wednesday, February 20, 2013 10:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
ugo boy (2/20/2013)
Thanks Gail for your response. does it mean that a previous query can distort the indexed order?


Err, no. Selects don't change indexes.

When I ran the query I was talking about, I used a (nolock) hint and that was when the results came back in the unsorted order but when I reran the query without the (nolock) hint, the results came back in the correct ascending order of the clustered indexed primary key.
does a (nolock) hint affect sort orders, can it?


Again, without an ORDER BY on your query, there's no such thing as 'correct order'. Without an Order By, you're telling SQL that there is no meaning, no importance, no relevance to the order of the data, that any order is as good as any other order.

If you need a specific order, you *must* put an ORDER BY on the query.

btw, on that nolock, see - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1422181
Posted Thursday, February 21, 2013 4:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
Pick up Itzik Ben Gan's book on TSQL Fundamentals, if you can.
It will go a long way towards plugging a few gaps in your knowledge.
Post #1422502
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse