Search

Management Studio generates invalid TOP 100 PERCENT ... ORDER BY in views by Hugo Kornelis

Closed
as Fixed Help for as Fixed

38
0
Sign in
 to vote
Type: Bug
ID: 249248
Opened: 1/2/2007 5:54:48 AM
Access Restriction: Public
3
Workaround(s)
10
User(s) can reproduce this bug
If you build a view in Management Studio view designer and select a column to order the results by, a view such as the below is generated:
CREATE VIEW xxx AS
SELECT TOP (100) PERCENT ....
(...)
ORDER BY SortCol;

Due to optimizer improvements, this "trick" (which has never been documented or supported anyway) doesn't even work anymore on SQL Server 2005. The user is given the idea that his requested ordering will be satisfied, whereas it won't.
Details (expand)
Product Language
English

Version

SQL Server 2005 - Enterprise Edition

Category

Tools

Operating System

Windows XP SP2 Professional
Operating System Language
Dutch
Steps to Reproduce
Open SQL Server Management Studio.
Navigate object explorer to Database / AdventureWorks / Views.
Right-click and select "New view".
Add the Address(Person) table to the design grid.
Click the AddressID column.
Select sort type descending for the AddressID column.
Check the SQL in the SQL pane.
Close view designer and save the view.
Open the view (either with right-click or with a query without ORDER BY clause).
Actual Results
No error or warning message given, but results are returned unordered.
Expected Results
Error or warning message when saving the view that the results of a view can not be ordered. Or, better yet, no "Sort type" and "Sort order" columns in the view designer.

Platform

32
File Attachments
0 attachments
Sign in to post a comment.
Posted by Constantijn Enders on 8/21/2012 at 6:47 AM
Test script (found it somewhere on the internet )

CREATE TABLE t1 (x INT PRIMARY KEY, y INT UNIQUE);
GO

INSERT INTO t1 (x,y) VALUES (1,3);
INSERT INTO t1 (x,y) VALUES (2,2);
INSERT INTO t1 (x,y) VALUES (3,1);
GO

CREATE VIEW v1 AS
SELECT TOP 100 percent x FROM t1 ORDER BY x
GO

SELECT x FROM v1
GO

expected result
1
2
3

actual result
3
2
1

on SQL server 10.50.277
Posted by aaronbertrand on 2/25/2012 at 10:34 AM
Am I coming to the right conclusion that by "fixed" you mean that I am still encouraged by the view designer to apply sort type / sort order to the view, but I am not greeted with a warning about this fallacy until I click Save. What if I'm using the view designer, which generates this bogus and misleading code for me, only to derive the SQL that I'm then going to copy and paste into a CREATE/ALTER view script elsewhere? Or if I'm not using it for a view but trying to get the view designer's "help" to code up a derived table, CTE etc.?

I think the true "fix" in this case should not be to pop up a warning that tells me the thing you just allowed me to do is bad and wrong. The FIX is to hide those columns and make them a no-op (behavior that already exists if I have created a view and didn't touch them).
Posted by Hugo Kornelis on 12/28/2010 at 2:32 AM
Thanks for the explanation, Chandramouli. I'm looking forward to testing the fix in the next release. (Maybe already in the next CTP?)

Hugo
Posted by Microsoft on 12/27/2010 at 6:42 PM
Hugo:

Quick note - when we say it is fixed, we actually mean, that it is fixed in the internal builds - and the fix will be reflected in the next available SQL Server release.

Cheers,

Chandramouli
Posted by Microsoft on 12/27/2010 at 6:40 PM
Hi Aaron/all

Greetings from the SQL Server Manageability team.

Thanks for writing in to Microsoft. We greatly value your feedback.

We were able to reproduce this issue, and we have fixed this issue. This will reflect in an upcoming release of SQL Server.

Thanks again for providing feedback and making SQL Server the greatest Database server.

Regards

Chandramouli
Posted by aaronbertrand on 12/26/2010 at 9:32 PM
Yes, please, I would love to hear why this keeps getting re-closed as fixed, when the latest version of the tool does the exact same broken thing it did almost three years when this item was first re-filed.
Posted by Hugo Kornelis on 12/25/2010 at 1:31 PM
And Microsoft has again closed this, as "fixed".
Could the MS employee who made this change please post a comment and tell us in which CTP of Denali we can test the fix?
Posted by Hugo Kornelis on 12/24/2010 at 4:14 PM
Reopened once more.
I don't know who gave this bug the status "closed - fixed", or why, but it is obviously not fixed, so the issue can not be closed yet.
Posted by Bill Ramos on 12/24/2010 at 8:53 AM
As Aaron points out, this is one of those important things to fix that makes the View designer look silly after the engine dropped support for the feature. I recommend that in the view designer, you hide the SORT column when launched in View mode. You still need the Sort column for the Query Designer. In the event that a VIEW has the TOP clause in it with a SORT condition, then launching the View designer, launch a message box indicating that the syntax is not supported and prompt the user to open up the SQL pane and remove the ORDER BY clause manually. This would be lower risk that removing the Order By clause for the user.
Posted by aaronbertrand on 12/23/2010 at 4:21 PM
Yep, can you believe this crap still happens in Denali? Neither could I, but here is proof:

http://twitpic.com/3iw0d8
Posted by Rahul Sherawat on 5/13/2009 at 11:13 PM
Microsoft................... this bug still exist in SQL SEVER 2008
Posted by Microsoft on 2/14/2008 at 2:26 AM
We will definitely consider your suggestion. Having an option to change the Sort Type in View Designer is something we would want to relook at in a future release.

I have reactivated your bug and marked it for a subsequent release.

Regards,
Karthik
Posted by Hugo Kornelis on 12/28/2007 at 3:27 PM
Hi Karthik,

You write "ORDER BY clause within a VIEW does not ensure that the result is in sorted order. The ORDER BY clause should be used in the outer query using the View."

How does this answer my complaint that Management Studio generates this code, that by your own admission won't ensure sorted results and should not be used??

Please remove the option to specify sort order for a view from Management Studio in the next service pack.

Best, Hugo
Posted by Microsoft on 12/20/2007 at 9:08 AM
Hello Hugo Kornelis,

Thanks for the prompt followup. ORDER BY clause within a VIEW does not ensure that the result is in sorted order. The ORDER BY clause should be used in the outer query using the View.

Thanks
Karthik
Posted by aaronbertrand on 12/11/2007 at 5:50 AM
I agree that the proper solution is to remove the sort functionality altogether. Why let them check a box that does not accomplish anything other than perpetuate bad habits and prolong myths?
Posted by Hugo Kornelis on 12/11/2007 at 1:55 AM
Forgot to add:

I reopened this bug. I expect a better explanation than this if you intend to close it once more.

Best, Hugo
Posted by Hugo Kornelis on 12/11/2007 at 1:54 AM
I just got a note that this item has been closed as "by design". I fail to see the logic in this move.

Microsoft itself has documented, in several places, that the "trick" to order a view by using TOP 100 PERCENT and ORDER BY was never supported, and won't work on SQL Server 2005. The most visible documentation of this is in the knowledge base article that describes a "fix" for this "problem" - a fix that requires a trace flag to be set and the database to be put into backwards compatibility mode 80. See http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b926292&sd=rss&spid=2855

Closing this issue as "by design" means that you are telling your customers that you expose a method to order a view in the view designer, which generates code that you yourself have documented as not supported and not working.
"Yes, you can click there to request the view to be ordered.
"No, it won't actually order the view.
"No, this is not a bug; we call this 'by design'."

Puh-lease!!
Posted by David Portas on 1/7/2007 at 1:20 AM
"we investigate the possibility of fixing either the Query Optimizer to honor the TOP clause or remove the functionality from the View designer."

I seriously hope it's the latter rather than the former. The "sort order" property should never have been allowed for the view designer. Attempting to support it as a valid "feature" couldn't be anything other than a total disaster IMO.
Posted by Dave Markle on 1/3/2007 at 4:58 AM
Bill:

I'll second Hugo's comments. The problem truly is in SSMS, not the optimizer. People often forget that TOP...ORDER BY in views is used for *filtering* not for ordering of the result set. If there's a bug anywhere else, it would be that

CREATE VIEW dbo.View1 SELECT TOP 100 PERCENT * FROM Orders ORDER BY OrderID

does not return the a Msg 1033 like you'd get if you specified this query:

CREATE VIEW dbo.View1 SELECT * FROM Orders ORDER BY OrderID

Yeah, you'd have people who thought they were "smart" who'd change the "100" to TOP 99.9999999 or something like that, but they would deserve what they got. You don't want people stumbling on this. And SSMS makes them do it.

Really, if you think about it, even "SELECT TOP 80 PERCENT * FROM Orders ORDER BY OrderID" shouldn't be considered (in a view) to produce sorted output -- it's just a filtering condition. The "Sort" field in the SSMS designer needs to go away.




Posted by Hugo Kornelis on 1/2/2007 at 11:53 AM
Hi Bill,

Thanks for the swift reply.

Please do NOT attempt to fix this in the Query Optimizer. Ordering a view goes against all basic principles of relational theory. Please remove the ordering functionality from the view designer. Ordering should only be done when retrieving data, period.

As for your remark about TOP (large number) sometimes working: I am aware of it, but, frankly, not interested. This is undocumented and unsupported behaviour, so I could never use this in my work. My clients expect me to deliver products that *always* work.
Posted by Microsoft on 1/2/2007 at 11:06 AM
Hi Hugo,
    Thank you for your feedback regarding the View Designer against SQL 2005 instances. In a future release of SQL Server, we investigate the possibility of fixing either the Query Optimizer to honor the TOP clause or remove the functionality from the View designer. Note, in some cased for SQL 2005, you can use TOP <n> where is a large number and it will sometimes work.
Thank you,
Bill Ramos
Sign in to post a workaround.
Posted by AXENAJA on 12/25/2010 at 10:06 PM
*
Posted by AXENAJA on 12/25/2010 at 10:06 PM
*
Posted by Steve Kass on 12/11/2007 at 6:33 AM
The only acceptable workaround is to specify ORDER BY in the query that selects from the view, whenever ordered results are desired. The idea of an "ordered view" is wrong.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement