Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Out of order


Out of order

Author
Message
rfr.ferrari
rfr.ferrari
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 13619
really it's a good question!!!!!


rfr.ferrari
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!

Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1065 Visits: 1514
Ha. Got it right. And it was a tricky question. :-P

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
Mighty
Mighty
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4173 Visits: 1646
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.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21073 Visits: 18259
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

Thomas Abraham
Thomas Abraham
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 2254
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.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Britt Cluff
Britt Cluff
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 253
Thanks for the question. What's the point in using TOP in a view?

http://brittcluff.blogspot.com/
paul.knibbs
paul.knibbs
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1972 Visits: 6213
Britt Cluff (10/21/2011)
Thanks for the question. What's the point in using TOP in a view?


You might want the view to only return a certain number of rows. The question ought to be, why did Microsoft use the ORDER BY syntax to specify which rows you're returning when you do this, because it just causes people to think that views can be ordered!
Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1065 Visits: 1514
Britt Cluff (10/21/2011)
Thanks for the question. What's the point in using TOP in a view?


I wonder that too since the result may be different in each SELECT of the view.
Maybe en example would be something like "VW_BestEmployeesEver" to show only the best 10 employees? :-D

Best regads,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10700 Visits: 11991
Britt Cluff (10/21/2011)
Thanks for the question. What's the point in using TOP in a view?

OK, here is a real example that I had in one job.
In an interactive entertainment system, I want to know for example what are the 5 most frequently paid for PPV movies in my current catalogue; and the 5 least often paid for too. This helps plan future ordering of additional titles and retiring of existing titles. The view is basically a union of two select clauses each with a TOP and an ORDER BY clause. The view is actually quite complex, as (a) I'm only interested in recent payments, and (b) I'm only interested in movies that have been on the system for at least a month, (c) I may want separate information for "adult" movies, "family" movies, and "mainstream" movies, (d) I may want to treat recent releases and back catalogue and current catalogue differently, and (e) since the license fee demanded by the studios for a "blockbuster" is higher than that for an ordinary film I may want to split on that property too. Then I may want to split on license provider too, as different providers have different licensing systems; and I may want to split on territory since I have to either wait for official middle east cuts, make my own middle east cut (if the studio permits me to do that) or not supply the film in the middle east because of local censorship laws, and I have to take note of the difficulty of getting European/American films licensed in for example India (again because of local censorship laws) or even on an individual resort/hotel/residential complex/whatever because that, and not the viewer, is my customer and my contract with the customer requires me to have so many films in various categories (crime, adventure, scifi, whatever) at any time and to provide at least so many new films 10 times per year and so on. But those TOP and ORDER BY clauses are fundamental to the usefulness and function of the view(s). (Similar views for music, for TV channels, for radio stations, .... also need top and order by).

Tom

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
Good question and with 40% incorrect answers with over 500 responses it seems a back to basics topic that lots of people don't know.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search