About Index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

About Index

Author

Topic

jessiefun
Starting Member

35 Posts

Posted - 2010-07-05 : 03:47:13

I find a problem about Index -
E.g. Table - tbTest which contains 3 columns (ID, Name, Age) and ID is Primary key.
There is a index on this table with column Name;
While when i use this query -
select id from tbTest
the output is not ordered by id, it's ordered by Name.

So my question is: As there is a unique clustered index on ID (by default for primary key as we all know), why the output is not ordered by ID?

Hope you guys can give me some responses.
Really appreciate your help.
Thanks.

Kristen
Test

22859 Posts

Posted - 2010-07-05 : 03:56:14

There is NO implied order in a relational database.

Sometimes you may get an order (as you say, it appears to be sorted by Name, but it will NOT always be the same.

If you want an order to the results you MUST use an ORDER BY clause.

jessiefun
Starting Member

35 Posts

Posted - 2010-07-05 : 04:25:52

While if I create another index on column - Age and excute the following query -
select age from tbTest - The results should be ordered by age

That's to say, if there is a index on column A, then when you JUST query the column A, the results sholud be ordered without using ORDER BY clause, right?

If YES, then the query - Select ID from tbTest should give a sorted results.

Kristen
Test

22859 Posts

Posted - 2010-07-05 : 04:38:38

There is NO implied order in a relational database

Sometimes you may get an order (as you say, it appears to be sorted by Name, but it will NOT always be the same.

If you want an order to the results you MUST use an ORDER BY clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-05 : 04:46:27

And to emphasize what Kristen is already saying

There is NO implied order in a relational database

Sometimes you may get an order (as you say, it appears to be sorted by Name, but it will NOT always be the same.

If you want an order to the results you MUST use an ORDER BY clause.

N 56°04'39.26"
E 12°55'05.63"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-05 : 05:35:31

The index may be sorted however SQL is under no obligation to use that index or to scan it in key order or to preserve the order after reading it. Hence, there is no guarantee of order of rows unless there is an order by specified on the outer query. Without that, the optimiser is free to reorder the rows if it needs, to do unordered scans, or to use different indexes.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-07-05 : 05:38:23

quote:
Originally posted by yangziyi

If there is an index for Column A, then there will be a Sorted column A!!
No need to reply, if you guys cannot give the reason why!!

The part I have highlighted IS NOT TRUE. It MAY be the case that most times you see the data sorted that way. But even if that IS the case it is NOT guaranteed. For example, if SQL happens to have some of the data your query needs in cache then that data MAY be processed before other data (which has to be retrieved from disk) and thus that data may show BEFORE other data in the output results. The circumstances when this happens are rare, and thus MOST of the time you may see data APPARENTLY sorted the way you want. But you basically just have a hard-to-find-bug.

If you WANT your data SORTED you HAVE to use an ORDER BY clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-05 : 06:04:47

There is NO guaranteed order of the data in a relational database.
If the data happens to come out sorted the way you want, it's entirely by coincidence.
If you get parallellism in your query, the two streams MAY output each stream in the order you expect, but the two streams are merged and the records are sent to the client in the merged order.

See this very simple sample code

SET NOCOUNT ON

CREATE TABLE	#Sample
		(
			Number INT PRIMARY KEY CLUSTERED
		)

INSERT	#Sample
SELECT	1

WHILE (SELECT COUNT(*) FROM #Sample) < 4000
	INSERT	#Sample
		(
			Number
		)
	SELECT	Number + (SELECT COUNT(*) FROM #Sample)
	FROM	#Sample

SELECT		s1.Number AS s1number,
		s2.Number AS s2number
FROM		#Sample AS s1
INNER JOIN	#Sample AS s2 ON s2.Number >= s1.Number

DROP TABLE #Sample

When I run this, I sometimes get
1, 1
1, 2
1, ...
1, 339
1, 340
624, 624
624, 625
624, ...
624, 1302
624, 1303
1, 341
1, 342
1, ...

and so on! I DO NOT get same order every execution,

The order is determined by the query optimized and the query optimizers fetches the wanted records the way it choose depending on a number of calculations. The most cost-efficient execution plan is chosen and therefore you may not get same order each time depending on a lot of factors; stale statistics, IO load and such.

Have I got you attention now? As you can see, I am using TWO clustered indexed in this case so with your mindset, the data should be perfectly sorted by s1number and s2number, right?

N 56°04'39.26"
E 12°55'05.63"

jessiefun
Starting Member

35 Posts

Posted - 2010-07-05 : 23:16:24

quote:
Originally posted by Kristen

quote:
Originally posted by yangziyi

If there is an index for Column A, then there will be a Sorted column A!!
No need to reply, if you guys cannot give the reason why!!

The part I have highlighted IS NOT TRUE. It MAY be the case that most times you see the data sorted that way. But even if that IS the case it is NOT guaranteed. For example, if SQL happens to have some of the data your query needs in cache then that data MAY be processed before other data (which has to be retrieved from disk) and thus that data may show BEFORE other data in the output results. The circumstances when this happens are rare, and thus MOST of the time you may see data APPARENTLY sorted the way you want. But you basically just have a hard-to-find-bug.

If you WANT your data SORTED you HAVE to use an ORDER BY clause.

Yeah, i think the cache thing explains this question~~~
Thanks A LOT.

jessiefun
Starting Member

35 Posts

Posted - 2010-07-05 : 23:19:25

quote:
Originally posted by Peso

There is NO guaranteed order of the data in a relational database.
If the data happens to come out sorted the way you want, it's entirely by coincidence.
If you get parallellism in your query, the two streams MAY output each stream in the order you expect, but the two streams are merged and the records are sent to the client in the merged order.

See this very simple sample code
SET NOCOUNT ON

CREATE TABLE #Sample
(
Number INT PRIMARY KEY CLUSTERED
)

INSERT #Sample
SELECT 1

WHILE (SELECT COUNT(*) FROM #Sample) < 4000
INSERT #Sample
(
Number
)
SELECT Number + (SELECT COUNT(*) FROM #Sample)
FROM #Sample

SELECT s1.Number AS s1number,
s2.Number AS s2number
FROM #Sample AS s1
INNER JOIN #Sample AS s2 ON s2.Number >= s1.Number

DROP TABLE #Sample
When I run this, I sometimes get
1, 1
1, 2
1, ...
1, 339
1, 340
624, 624
624, 625
624, ...
624, 1302
624, 1303
1, 341
1, 342
1, ...

and so on! I DO NOT get same order every execution,

The order is determined by the query optimized and the query optimizers fetches the wanted records the way it choose depending on a number of calculations. The most cost-efficient execution plan is chosen and therefore you may not get same order each time depending on a lot of factors; stale statistics, IO load and such.

Have I got you attention now? As you can see, I am using TWO clustered indexed in this case so with your mindset, the data should be perfectly sorted by s1number and s2number, right?

N 56°04'39.26"
E 12°55'05.63"

Thaks for your sample and your explanation. That helps a lot.
-Jessie

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-06 : 01:39:40

quote:
Originally posted by jessiefun

Yeah, i think the cache thing explains this question~~~

It's not just the cache. Even with an empty data cache I can get this behaviour.

SQL is not under any obligation to read an index in index key order if it thinks there's a faster way (under certain circumstances)
The optimiser is under no obligation to use order-preserving operations when developing a query plan. Parallelism, hash joins, hash aggregates are specific examples of operators that will return rows in a different order to what they read them in.

--
Gail Shaw
SQL Server MVP

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-06 : 01:50:45

quote:
Originally posted by yangziyi

If there is an index for Column A, then there will be a Sorted column A!!
No need to reply, if you guys cannot give the reason why!!

So he is gone. Has deleted his post.
Coward.
And I thought now there will something go on like in the past with wildfang (was this his nick?)

edit: Now I know! It was whitefang (http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=48159)

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-07-06 : 02:33:01

Its in Google Cache too:

yangziyi Posted - 07/05/2010 : 05:22:28
If there is an index for Column A, then there will be a Sorted column A!!
No need to reply, if you guys cannot give the reason why!!

Kristen
Test

22859 Posts

Posted - 2010-07-06 : 02:33:52

Hahaha ... and I had already quoted it in full. I'll get my coat!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources