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


Out of order


Out of order

Author
Message
Dave62
Dave62
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3060 Visits: 2717
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. Rolleyes
SanDroid
SanDroid
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: 1414 Visits: 1046
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?
Dave62
Dave62
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3060 Visits: 2717
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;


OzYbOi d(-_-)b
OzYbOi d(-_-)b
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1300 Visits: 778
thanks for the question. - nice easy one to round out the week!

cheers
SanDroid
SanDroid
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: 1414 Visits: 1046
Dave62 (10/21/2011)
....
I'm glad though that there hasn't been a lot of "trick question... I want my point back..." responses. Rolleyes

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. w00t Cool
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4069 Visits: 3648
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. Rolleyes


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.
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
Good one, thanks.
Ken Wymore
Ken Wymore
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4452 Visits: 2343
Nice question. Thanks!
SanDroid
SanDroid
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: 1414 Visits: 1046
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. Cool
CoolCodeShare
CoolCodeShare
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 311
Nice Question.
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