Opening View does not reflect SQL statement

I am having trouble with a VIEW. I modify the view to add a sort criteria. I can Execute the SQL and get the results I am looking for. I save the VIEW. Then if I open the VIEW using the OPEN VIEW menu option(right clicking the VIEW name) the sort order I set does not work. Please help.

Using Microsoft SQL Server Management Studio Express to access the SQL Server 2005

Hi,

this is by design. SQL Server does not guarantee to give back ordered results in a view, unless you specify the TOP clause (e.g. TOP 100 PERCENT).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am having trouble with this issue, too. When I use the TOP (100) clause, the sort order is reflected when I use Open View. When I use the TOP 100 PERCENT clause, I don't get the sort order. I need to display all of the records in the view and need the sort order. Any suggestions?

Thanks!

|||

I apply Service Pack 1 for microsoft SQL Server 2005

Version : 9.00.2047

and facing the same problem and the TOP 100 Percent doesn't resolve the problem. and the sorting option (ORDER BY) working fine in preview pane , but when Open the View the sorting option doesn't work.

the workaround for this is not using the View and write direct SQL statements with ORDER BY.

Thanks

|||

Hi Jens,

Sorry to say u that ur post was not helpfull, still same problem on using SELECT TOP 100 PERCENT

I have found one link where it was written if we use SELECT TOP (100) PERCENT it will work, actually it worked but

only for NUMBER and DATE.

Why not for STRING(varchar). I am working at tokyo and my database has japanese data. what about japanese sorting..

Please give us some solution or downloadable patch to overcome this BUG of SQL Server 2005 ?

RICz
Software Specialist
Tokyo,Japan
www.rajibul.com

|||

Yes, I found a funny way to fix the sorting problem of Character in SQL Server 2005.

--It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views-

Please just use the SELECT TOP (2147483647) in the SQL to support sorting of ORDER BY

It will work until microsoft fix it to work automatically

Information from
http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html

RICZ
www.rajibul.com