Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Out of order Expand / Collapse
Author
Message
Posted Friday, October 21, 2011 3:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:04 AM
Points: 1,254, Visits: 13,552
really it's a good question!!!!!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1194227
Posted Friday, October 21, 2011 4:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:34 AM
Points: 897, Visits: 1,486
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
Post #1194238
Posted Friday, October 21, 2011 5:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 3,202, Visits: 1,241
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.
Post #1194265
Posted Friday, October 21, 2011 5:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:02 PM
Points: 21,342, Visits: 15,017
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1194283
Posted Friday, October 21, 2011 5:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 1,771, Visits: 2,147
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
Post #1194284
Posted Friday, October 21, 2011 6:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Thanks for the question. What's the point in using TOP in a view?

http://brittcluff.blogspot.com/
Post #1194308
Posted Friday, October 21, 2011 6:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:12 AM
Points: 1,610, Visits: 5,482
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!
Post #1194333
Posted Friday, October 21, 2011 6:31 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:34 AM
Points: 897, Visits: 1,486
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?

Best regads,


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1194334
Posted Friday, October 21, 2011 6:34 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 8,573, Visits: 9,081
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
Post #1194336
Posted Friday, October 21, 2011 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
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)
Post #1194371
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse