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 7:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 2,103, Visits: 2,110
Sean Lange (10/21/2011)
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.


Yes, that was quite a surprise to me. It got edited out but I included in my explanation that I expected 90 percent or higher correct answers.

I'm glad though that there hasn't been a lot of "trick question... I want my point back..." responses.
Post #1194379
Posted Friday, October 21, 2011 7:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
Mighty (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.

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.

Did that work for you when there was less than 99999999 rows in the source data?
Post #1194380
Posted Friday, October 21, 2011 8:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 2,103, Visits: 2,110
SanDroid (10/21/2011)
Mighty (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.

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.

Did that work for you when there was less than 99999999 rows in the source data?


It should work wether there are more or less records. You can even use a variable to limit the number of records returned or return them all.

USE AdventureWorks;
GO

DECLARE @TopRows AS int;

-- Set the variable for the number of rows to return based on the rows in the table
-- and give an extra 100
SET @TopRows = (Select COUNT(*) From HumanResources.Employee) + 100;

-- All rows are returned regardless of the extra 100
SELECT TOP(@TopRows) LoginID, Title, HireDate, @TopRows As "TopRows"
FROM HumanResources.Employee
ORDER BY HireDate;

-- Now set the variable to exclude the last 100 records in the table
SET @TopRows = (Select COUNT(*) From HumanResources.Employee) - 100;

SELECT TOP(@TopRows) LoginID, Title, HireDate, @TopRows As "TopRows"
FROM HumanResources.Employee
ORDER BY HireDate;

Post #1194413
Posted Friday, October 21, 2011 8:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
thanks for the question. - nice easy one to round out the week!

cheers
Post #1194426
Posted Friday, October 21, 2011 8:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
Dave62 (10/21/2011)
....
I'm glad though that there hasn't been a lot of "trick question... I want my point back..." responses.

That's most likely becuase there is a big difference between a trick question and a tricky subject.
You wrote a very good straight forward well worded question about a tricky subject.
Now, if you had made the answer True, and explained this was becuase ORDER BY is only used to define the column used to the Filter select Top statement you might have seen a lot of those responses.
Post #1194434
Posted Friday, October 21, 2011 8:39 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: Thursday, October 16, 2014 11:48 AM
Points: 3,956, Visits: 3,644
Dave62 (10/21/2011)
Sean Lange (10/21/2011)
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.


Yes, that was quite a surprise to me. It got edited out but I included in my explanation that I expected 90 percent or higher correct answers.

I'm glad though that there hasn't been a lot of "trick question... I want my point back..." responses.


Drat, I forgot all about the Top thingy! After 20 years of doing this I guess it is back to the basics for me. I don't deserve my point back (good question) but I do want to take a mulligan.
Post #1194438
Posted Friday, October 21, 2011 9:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
Good one, thanks.
Post #1194511
Posted Friday, October 21, 2011 9:50 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 @ 11:29 AM
Points: 3,354, Visits: 2,001
Nice question. Thanks!
Post #1194516
Posted Friday, October 21, 2011 11:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
Dave62 (10/21/2011)
[quote]SanDroid (10/21/2011)
......
It should work wether there are more or less records. You can even use a variable to limit the number of records returned or return them all.
......[code="sql"]

I should have been more specific in my question.
Does using a "TOP 99999999" statement with an "order by" in a VIEW against an actual table with less than 99.9 Million rows keep the query optimizer from ignoring the Order by statement since nothing will be filtered?

I know in a basic select with a variable it will not, but Views are different.
Post #1194589
Posted Friday, October 21, 2011 12:03 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 7:06 AM
Points: 1,216, Visits: 308
Nice Question.
Post #1194608
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse