View is not ordered. Why?

Question

View is not ordered. Why?

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 1

Craig Farrell (12/16/2010)
Michael Valentine Jones (12/16/2010)
There are a lot of people that think that if you do a "select * from MyTable" that it will return data in order by the clustered index. It might and probably usually will, but it is not guaranteed to.
Alright, I'll be the one to bite into that apple. SELECT * FROM MyTable performs a Clustered Index Scan, would not initiate a sort of any kind without outside influence, and thus would kick out records in the order read.
AFAIK.
Where's this from?

I have no Idea ... another myth that needs debunking..

The discussion on why this is not so is a long and involved topic and all I can suggest is read 'Inside Microsoft SQL Server T-SQL quering' by Ben-Gan to get the whole picture. Chapter 4 page 191 ... get it read it !!

CodeOn

😛

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Malcolm Daughtree (12/16/2010)
I have no Idea ... another myth that needs debunking..

Which are you saying is the myth? That the clustered index scan does always return in order or that it doesn't

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

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

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 3

GilaMonster (12/16/2010)
Malcolm Daughtree (12/16/2010)
I have no Idea ... another myth that needs debunking..
Which are you saying is the myth? That the clustered index scan does always return in order or that it doesn't

Myth: "That the clustered index scan does always return in order".

Index Page allocation order, Physical and Logical fragmentation, Table hints such as NOLOCK (Ugh !) the choises that the storage engine is presented with all affect the order the data is returned in - if an order by is not specified.

I don't mind trying to teach the masses but there come a time where there has to be some work done on their part to research and read for themselves.

CodeOn

😛

WayneS SSC Guru Points: 95460 More actions · Answer 4

Craig Farrell (12/16/2010)
Michael Valentine Jones (12/16/2010)
There are a lot of people that think that if you do a "select * from MyTable" that it will return data in order by the clustered index. It might and probably usually will, but it is not guaranteed to.
Alright, I'll be the one to bite into that apple. SELECT * FROM MyTable performs a Clustered Index Scan, would not initiate a sort of any kind without outside influence, and thus would kick out records in the order read.
AFAIK.
Where's this from?

@Craig - what does this last select return on your system?

IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;

CREATE TABLE #Test (RowID INT PRIMARY KEY CLUSTERED);

;WITH

TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),

MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),

TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)

INSERT INTO #Test

SELECT N FROM TALLY;

SELECT TOP (5) * FROM #Test WHERE RowID > 5000;

SELECT TOP (5) * FROM #Test WHERE RowID > 7000;

SELECT TOP (5) * FROM #Test WHERE RowID > 9000;

SELECT * FROM #Test;

On my system, the first row returned is # 298561.

Edit: The execution plan shows just a CI Scan and SELECT.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 5

evald (12/15/2010)
And i would say also that if you ask 10 dba about this maybe only one knows all this story.

And I would have to say that all 10 should know that an order is only guaranteed on the query that is actually returning the data, or if a top is used that is actually reducing the number of rows being returned.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

WayneS (12/16/2010)
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test; CREATE TABLE #Test (RowID INT PRIMARY KEY CLUSTERED); ;WITH TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3), MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2), TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS) INSERT INTO #Test SELECT N FROM TALLY; SELECT TOP (5) * FROM #Test WHERE RowID > 5000; SELECT TOP (5) * FROM #Test WHERE RowID > 7000; SELECT TOP (5) * FROM #Test WHERE RowID > 9000;
SELECT * FROM #Test;

Wayne, very interesting to see an easily reproduceable example.

I wonder do you know is that behaviour dependent on it being a #temp table rather than a non-temp table as I find that if I change it to be a permanent table the behaviour goes back to selecting in the order of the CI?

Also, do you know if that behaviour is evident with only 1 tempdb FILE (rows data) as I can see while running that query that it is hitting all of mine and wondering if that is the reason for the ordering being out of whack?

Thanks for your time

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

WayneS (12/16/2010)
@Craig - what does this last select return on your system?

As expected, 5001-5005, 7001-7005, 9001-9005, and the final select starts at 1 and carries through (for about 30 spot checks) lockstep to the row. So, not always repeatable.

I see where y'all are going with this, though. For when it mattered I always dropped an order by on the tail of my queries, mostly because I don't trust someone not to muck with the clustered indexes. In general, though, I don't think I'd ever thought about it hard enough.

Well, thanks folks.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

Malcolm Daughtree (12/16/2010)
I don't mind trying to teach the masses but there come a time where there has to be some work done on their part to research and read for themselves.

Ah, but see, if someone makes a statement, they're usually the ones with the links/books to show you where, to avoid you having to go digging for a few days to find out what the heck they were talking about.

On occassion, it's some esoteric point dealing with the conjuncting Eye of Jupiter during a Loch Ness Monster's Roar. On others, it's just something you really didn't think about, such as the affect of physical framentation on the IAM. Which I didn't... and thus, must go read. 🙂

GilaMonster (12/16/2010)
1) Parallelism
2) Allocation order scan with a fragmented index.
3) Advanced scan (merry go round). This one's hard and I've never personally been able to repo it.

As usual, thanks Gail.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

WayneS SSC Guru Points: 95460 More actions · Answer 9

mister.magoo (12/16/2010)
Wayne, very interesting to see an easily reproduceable example.
I wonder do you know is that behaviour dependent on it being a #temp table rather than a non-temp table as I find that if I change it to be a permanent table the behaviour goes back to selecting in the order of the CI?
Also, do you know if that behaviour is evident with only 1 tempdb FILE (rows data) as I can see while running that query that it is hitting all of mine and wondering if that is the reason for the ordering being out of whack?
Thanks for your time

MM,

Very good questions.

I don't know that I would say it is "dependent" on it being a #temp table. However, I just ran a few tests running against a permanent table, and as you noticed it is selecting in CI order. Not enough to be definitive, and a bet that some of the gurus here (Gail, Jeff, et all) probably have some code that will show it in random order against a permanent table. Hmmm - I'll have to go check Gail's blog now.

My tempdb is a default setup - one rows data file, and one log file.

IMO, I see the "ordering being out of whack" for two reasons:

1. An "ORDER BY" wasn't specified (duh), and

2. Parallelism (dual-core laptop here).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 10

Craig Farrell (12/16/2010)
WayneS (12/16/2010)
@Craig - what does this last select return on your system?
As expected, 5001-5005, 7001-7005, 9001-9005, and the final select starts at 1 and carries through (for about 30 spot checks) lockstep to the row. So, not always repeatable.
I see where y'all are going with this, though. For when it mattered I always dropped an order by on the tail of my queries, mostly because I don't trust someone not to muck with the clustered indexes. In general, though, I don't think I'd ever thought about it hard enough.
Well, thanks folks.

Do you have a single-processor system? Now I'm curious as to why you can't reproduce it so easily.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

WayneS (12/16/2010)
IMO, I see the "ordering being out of whack" for two reasons:
1. An "ORDER BY" wasn't specified (duh), and
2. Parallelism (dual-core laptop here).

Agreed - I was ignoring the lack of an order by as that was the point of the test - to show it breaking without one - I think!

If parallelism is coming into it, wouldn't that show in the execution plan...

Anyway, thanks for your response..

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 12

Yeah, sometimes... I think that this "View not ordered. why?" topic and the discussion that insued is a clear indication that mostly,

1. People want to be spoon feed.

2. Have incorrect expectations and results that are based on incorrect assumptions dispite the evidence published in well documented changes.

3. Saying it "shouldn't be like that" or "It always worked like that before" is an impediment to continued learning.

These forums are an excellent jumping off points around avenues of research on various theories but should be just that..

Always glad to help the learning process

CodeOn

😛

WayneS SSC Guru Points: 95460 More actions · Answer 13

mister.magoo (12/16/2010)
WayneS (12/16/2010)
IMO, I see the "ordering being out of whack" for two reasons:
1. An "ORDER BY" wasn't specified (duh), and
2. Parallelism (dual-core laptop here).
Agreed - I was ignoring the lack of an order by as that was the point of the test - to show it breaking without one - I think!

Yep - hence the (duh)

If parallelism is coming into it, wouldn't that show in the execution plan...

I forgot about that - I would sure think so... and it's not. As I mentioned, it's showing just a CI Scan, and I assume that this is what you saw also.

Anyway, thanks for your response..

No problem - it's always nice when people start brainstorming together on things like this. Everyone learns!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150468 More actions · Answer 14

CREATE TABLE dbo.Example

(

row_id INTEGER IDENTITY NOT NULL PRIMARY KEY CLUSTERED,

data AS CONVERT(VARCHAR(11), row_id) PERSISTED NOT NULL UNIQUE

);

GO

INSERT dbo.Example

DEFAULT VALUES;

GO 10

SELECT E.row_id, E.data

FROM dbo.Example E;

edit: removed data column reference so it will work on 2005 too

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 15

Paul White NZ (12/16/2010)
CREATE TABLE dbo.Example ( row_id INTEGER IDENTITY NOT NULL PRIMARY KEY CLUSTERED, data AS CONVERT(VARCHAR(11), row_id) PERSISTED NOT NULL UNIQUE ); GO INSERT dbo.Example (data) DEFAULT VALUES; GO 10 SELECT E.row_id, E.data
FROM dbo.Example E;

Thanks Paul... a little adaptation for the 2k5 amongst us:

--drop table dbo.Example

CREATE TABLE dbo.Example

(

row_id INTEGER IDENTITY NOT NULL PRIMARY KEY CLUSTERED,

data VARCHAR(40) NOT NULL UNIQUE

);

GO

INSERT dbo.Example (data) VALUES (NEWID())

GO 10

SELECT E.row_id, E.data

FROM dbo.Example E;

And that produces the unordered mess. However, it's pulling from the non-clustered index. If I swap it to this:

SELECT E.row_id, E.data

FROM dbo.Example E WITH (INDEX (1));

I get clustered indexing order, as you would expect. You wouldn't know it though unless you looked at the execution plan and figured out why... so I understand what you're showing here, Paul, thank you.

@Wayne: Yes, pretty sure that was a single core, will check tomorrow to confirm when I'm back in the office.

I'm trying to force an IAM defrag that will cause this. I've forced some fragging, but I'm still getting logical read orders. Get back to you. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA