Post reply

Out of order

  • October 20, 2011 at 8:57 pm

    #245636

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

  • October 20, 2011 at 9:27 pm

    #1397733

    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]

  • October 21, 2011 at 1:04 am

    #1397749

    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.

  • October 21, 2011 at 1:19 am

    #1397754

    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.

  • October 21, 2011 at 1:45 am

    #1397764

    Sneaky question.

  • October 21, 2011 at 1:51 am

    This was removed by the editor as SPAM

  • October 21, 2011 at 2:07 am

    #1397773

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

  • October 21, 2011 at 2:10 am

    #1397776

    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.

  • October 21, 2011 at 2:10 am

    #1397777

    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.

  • October 21, 2011 at 2:59 am

    #1397803

    Easy point! 🙂

  • October 21, 2011 at 3:51 am

    #1397827

    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!

  • October 21, 2011 at 4:11 am

    #1397835

    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

  • October 21, 2011 at 5:03 am

    #1397856

    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.

  • October 21, 2011 at 5:36 am

    #1397874

    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

  • October 21, 2011 at 5:36 am

    #1397875

    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