Out of order

  • Comments posted to this topic are about the item Out of order

  • Nice question

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • CREATE VIEW vw_clients AS

    SELECT TOP 100 PERCENT Code, Name FROM Clients

    ORDER BY Code

    I am curious to know if the VIEW above always returns records in sorted order.

  • Carlo Romagnano (10/21/2011)


    CREATE VIEW vw_clients AS

    SELECT TOP 100 PERCENT Code, Name FROM Clients

    ORDER BY Code

    I am curious to know if the VIEW above always returns records in sorted order.

    No, it doesn't, unless you're still running SQL 2000. On more recent SQL versions the optimiser will see that the TOP 100 PERCENT and the ORDER BY are both redundant and will remove them. If you want the results of a view to be sorted, you have to include the ORDER BY in the SELECT statement you use to query the view.

  • Sneaky question.

  • This was removed by the editor as SPAM

  • paul.knibbs (10/21/2011)


    Carlo Romagnano (10/21/2011)


    CREATE VIEW vw_clients AS

    SELECT TOP 100 PERCENT Code, Name FROM Clients

    ORDER BY Code

    I am curious to know if the VIEW above always returns records in sorted order.

    No, it doesn't, unless you're still running SQL 2000. On more recent SQL versions the optimiser will see that the TOP 100 PERCENT and the ORDER BY are both redundant and will remove them. If you want the results of a view to be sorted, you have to include the ORDER BY in the SELECT statement you use to query the view.

    Thanks. In fact, in sql2000 the optimizer use an index scan (create index idx_client_code on Clients(Code)).

  • Well, I'm glad I looked at these posts. I knew you could order a view if you did the SELECT TOP (100) PERCENT thing but I hadn't realised that it didn't work!

    So what looked like an easy question has become an opportunity to learn something new.

  • Stewart "Arturius" Campbell (10/21/2011)


    Good question...

    even then, the sort order of any select from the view is not guaranteed....

    In fact, the TOP clause with the "ORDER BY", in this case, is just a filter and not a command to sort rows.

  • Easy point! 🙂

  • really it's a good question!!!!!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • Ha. Got it right. And it was a tricky question. 😛

    But a great one about a somewhat confusing subject (the ORDER BY, in that case, belongs only to the data filtered by the TOP clause and not the ordering of the whole query).

    Thank you.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Carlo Romagnano (10/21/2011)


    CREATE VIEW vw_clients AS

    SELECT TOP 100 PERCENT Code, Name FROM Clients

    ORDER BY Code

    I am curious to know if the VIEW above always returns records in sorted order.

    As other people already said, in SQL 2005 and up the "TOP 100 PERCENT" will not be used. I had a legacy database with a lot of views that where using this. I had to change them to "TOP 99999999" in order to get them "working" again.

  • Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Excellent! Thanks for the question. And thanks to others for the discussion of TOP 100 percent. I didn't realize the optimizer would throw that out. Of course, I've never used it, as I don't create many views, and never order them - leaving that for the user of the view. Still, it's good info to file away for later.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

Viewing 15 posts - 1 through 15 (of 34 total)

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