View is not ordered. Why?

  • Malcolm Daughtree

    SSCrazy

    Points: 2373

    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: 1004474

    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: 2373

    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: 95384

    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: 95384

    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: 47068

    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);

  • 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]

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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

    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: 95384

    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: 95384

    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: 47068

    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);

  • 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: 2373

    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: 95384

    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: 150442

    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

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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

  • Viewing 15 posts - 31 through 45 (of 63 total)

    You must be logged in to reply to this topic. Login to reply