View is not ordered. Why?

  • Malcolm Daughtree

    SSCrazy

    Points: 2373

    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;

    I think the reason that your example comes out in order is because the data is not causing a page split (8K) nor a re-allocation.

    try this

    CREATE TABLE [dbo].[Example](

    [row_id] [int] IDENTITY(1,1) NOT NULL,

    [data] AS (CONVERT([varchar](11),[row_id],(0))) PERSISTED,

    [MoreData] AS (newid()),

    CONSTRAINT [PK__Example__6965AB572B3F6F97] PRIMARY KEY CLUSTERED

    (

    [row_id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

    CONSTRAINT [UQ__Example__D9DE21E12E1BDC42] UNIQUE NONCLUSTERED

    (

    [data] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    on the above table

    run

    INSERT dbo.Example (data)

    DEFAULT VALUES;

    GO 100-- 0000

    SELECT E.row_id, E.data,E.moredata

    FROM dbo.Example E;

    and have a look at the ordering received now insert the order by Row_id and have a look at the difference. Clustered Index is still Row ID but the retrived order is different. The first is a non clustered index scan and the second (Order by Row_ID) is a Clustered index Scan. Clearly showing retrieve order is indepndant of the Inserted order of data using a clustered index.

    Hope that helps

    CodeOn

    πŸ˜›

  • TomThomson

    SSC Guru

    Points: 104773

    Paul White NZ (12/15/2010)


    Perhaps it would be nice if SQL Server printed a warning message in the second case. My understanding is that this is technically impossible to do for every case in practice, so they decided not to bother.

    Paul

    Then your understanding is wrong, whatever MS says about it. It's not technically impossible, just somewhat difficult.

    At a naive level, one could say that if it is technically possible for the optimizer to decide that it will not do the sort, then it is technically possible to determine that the optimizer will reach that conclusion. At a less naive level, I will sat that it is possible in a high level analysis to determine whether a given 'order by' relates either to a count-constrained selection or to the outermost select in a query (at least it certainly was in 1991; I don't think anything added to SQL since then could have changed that, but I may be wrong). What is probably true is that to do it in the current syntax analyser in SQLServer would be very difficult, involving a change in the whole philosophy of the thing - the current syntax analyser isn't at all into semantics, and this is of course semantics.

    Tom

  • Paul White

    SSC Guru

    Points: 150442

    Tom.Thomson (12/16/2010)


    Paul White NZ (12/15/2010)


    Perhaps it would be nice if SQL Server printed a warning message in the second case. My understanding is that this is technically impossible to do for every case in practice, so they decided not to bother.

    Then your understanding is wrong, whatever MS says about it. It's not technically impossible, just somewhat difficult.

    Ok. It was impractical to do. Better?

  • Paul White

    SSC Guru

    Points: 150442

    Malcolm Daughtree (12/16/2010)


    I think the reason that your example comes out in order is because the data is not causing a page split (8K) nor a re-allocation.

    The point of the demo is the rows don't come back in clustered index order - look where row_id 10 appears in the output πŸ˜›

  • Paul White

    SSC Guru

    Points: 150442

  • Paul White

    SSC Guru

    Points: 150442

    Craig Farrell (12/16/2010)


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

    I hadn't noticed that 2005 objected...the fix is simple though, just remove the pointless (data) column reference:

    INSERT dbo.Example (data) --> INSERT dbo.Example

    For some reason the 2005 parser objects to that with DEFAULT VALUES. Never noticed that before.

  • WayneS

    SSC Guru

    Points: 95374

    Craig Farrell (12/16/2010)


    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. πŸ™‚

    One thing I noticed is that if you don't have the Unique constraint, then it produces (this limited result set) in the CI order.

    @paul-2 (Mr. Knows_How_The_Optimizer_Works_In_Excruciating_Detail :-D) - is it possible that the query is doing parallelism on a CI Scan without the actual execution plan indicating it? All I see is a CI Scan and a Select.

    @Craig: If you have access to a multi-core processor server, can you try that code on it also? I'd really like to know why it is in order for you, but not for MM and myself.

    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

    WayneS (12/16/2010)


    One thing I noticed is that if you don't have the Unique constraint, then it produces (this limited result set) in the CI order.

    That's the point. The index that enforces the unique constraint is covering for the query, since all non-clustered indexes include the cluster key. The optimizer will almost always choose a covering non-clustered index scan over a clustered scan, even where the estimated cost is the same. Of course, the fact that the rows come back in non-clustered index order isn't guaranteed, for exactly the same reasons, but it happens to do so in this case.

    @Paul (Mr. Knows_How_The_Optimizer_Works_In_Excruciating_Detail :-D) - is it possible that the query is doing parallelism on a CI Scan without the actual execution plan indicating it? All I see is a CI Scan and a Select.

    No, absolutely not. If SQL Server uses parallelism, you'll always see it in the actual plan. Interestingly, there are cases where an apparently parallel plan executes serially, see http://sqlblog.com/blogs/paul_white/archive/2010/11/04/myth-sql-server-caches-a-serial-plan-with-every-parallel-plan.aspx

    The INDEX(1) forces an ordered scan of the clustered index, that's all. This still does not guarantee that the final output will be in clustered index order, of course. For more on INDEX(1) and ordered scans, see http://sqlblog.com/blogs/paul_white/archive/2010/09/23/a-tale-of-two-index-hints.aspx

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    On the original subject of this thread, this is what Books Online says:

    "SQL Server 2008 Books Online (August 2008)

    Sorting Rows with ORDER BY

    ...ORDER BY guarantees a sorted result only for the outermost SELECT statement of a query. For example, consider the following view definition:

    CREATE VIEW TopView AS

    SELECT TOP 50 PERCENT * FROM Person.Contact

    ORDER BY LastName

    Then query the view:

    SELECT * FROM TopView

    Although the view definition contains an ORDER BY clause, that ORDER BY clause is used only to determine the rows returned by the TOP clause. When querying the view itself, SQL Server does not guarantee the results will be ordered, unless you specify so explicitly, as shown in the following query:

    SELECT * FROM TopView

    ORDER BY LastName"

    As for the whole discussion that I ignited about "Select * from MyTable" not guaranteed to return rows in clustered index order, I don't know of a particular source, but I am fairly sure there is one. I have been able to demo it a number of times on different versions of SQL Server, and a single case is enough to prove the point. If you want your data in a particular order, use an ORDER BY.

    There are people that insist this is not true. See the link below for a discussion on this subject that turned really nasty when I pointed this out. "IncisiveOne" just didn't appreciate being told he was wrong. :crazy:

    Moving away from a cursor to while loop

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131674

    Thinking back, "IncisiveOne" was a lot of fun. This thread is a classic example of someone unable to stand getting called on their BS:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131674

  • TomThomson

    SSC Guru

    Points: 104773

    Paul White NZ (12/16/2010)


    Ok. It was impractical to do. Better?

    yes, that's it exactly.

    Tom

  • TomThomson

    SSC Guru

    Points: 104773

  • Lamprey13

    SSCertifiable

    Points: 6190

    To add to Pauls links, here is a link of links.. πŸ˜‰

    http://sqlserverpedia.com/blog/sql-server-bloggers/merry-go-round-scans/

  • Evil Kraig F

    SSC Guru

    Points: 100851

    <--------------------------- This look on my face is getting to be a habit these past two weeks...

    Okay, that explains why I couldn't get it to go to IAM order, my tables were too small (I was mucking around at around 20-30 pages, trying to keep it reasonable to track). The jawdrop was the NOLOCK item. I knew about the uncommitted rows and the like, but once again, the final implication of the IAM read for NOLOCK... gyeah! Losing the scan position?! :sick:

    * Be aware of the implications of reading data with the NOLOCK hint (or in a read uncommitted isolation). It’s not just a matter of reading uncommitted changes, or data in an intermediate state of the transaction; rather you might even get the same row twice, or SQL Server might lose the scan position during the scan.


    - 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

  • Paul White

    SSC Guru

    Points: 150442

    Lamprey13 (12/17/2010)


    To add to Pauls links, here is a link of links.. πŸ˜‰

    http://sqlserverpedia.com/blog/sql-server-bloggers/merry-go-round-scans/

    My problem with the Advanced Scan feature is that, like Gail, I have been unable to reproduce it. Ever.

    One thing that I haven't seen mentioned anywhere is that it only applies to unordered scans.

    If the scan operation shows Ordered:True, this feature won't be used, for reasons which should be obvious.

  • Paul White

    SSC Guru

    Points: 150442

    Craig Farrell (12/17/2010)


    <--------------------------- This look on my face is getting to be a habit these past two weeks...

    Cool avatar, much better than the last one IMVHO.

    Okay, that explains why I couldn't get it to go to IAM order, my tables were too small (I was mucking around at around 20-30 pages, trying to keep it reasonable to track). The jawdrop was the NOLOCK item. I knew about the uncommitted rows and the like, but once again, the final implication of the IAM read for NOLOCK... gyeah! Losing the scan position?!

    Yeah it can happen, but it is pretty rare in real life (or at least it has been for me).

    Read Committed (the default) isn't much better. You won't lose scan position, but skipping records and double-reads are perfectly possible. Using a row-versioning isolation level (most usually read committed snapshot) is a huge win for this, and many other things too.

Viewing 15 posts - 46 through 60 (of 63 total)

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