February 20, 2013 at 8:49 am
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
February 20, 2013 at 9:10 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2013 at 9:47 am
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?
February 20, 2013 at 9:59 am
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
February 20, 2013 at 10:12 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2013 at 4:50 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply