SQL Server Performance Forum – Threads Archive
View Sorting Problem SQL Server 2005 & Solution !!
————————–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 fromhttp://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html RIC Software Specialist
www.rajibul.com
Go ahead and read the comments in the article.
Roji. P. Thomas
http://toponewithties.blogspot.com
Technically, you should not use order by in a view definition. The optimizer reserves the right to ignore the order by clause in a view definition, so if you do it, you can get into trouble. Better is to put the order by clause in the query that targets the view. Then you are guaranteed the result set will come back ordered. See: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=79de45cb-2da6-4510-ae43-7e5e42e4c02e
SqlSpec – a fast, cheap, and comprehensive data dictionary generator
for SQL Server 2000 and 2005 and Analysis Server 2005 – www.elsasoft.org
Yes I agree you for some case. But in context of report development using CR, its better to process the SQL 100% at database site just caz of performance. You have to impl sorting as many group/details as u need. Here my purpose served. Ovbiously this is a bug/limitation of microsoft in a way that they put this feature in sql server 2000 and our company product 2.1 was built in sql server 2000, But all the reports are not working in version 3.1 which is based on 2005. Though it is not written anywhere in microsoft MSDN, rather its written, order by support by TOP 100 PERCENT ?? In this case, only my way can work for view, if somebody wants to use view directly. thanx,for the reply. Software Specialist
www.rajibul.com
Even in 2000, the order by in a view was not documented and guaranteed. Just because it works, you assumed that it’ll always works. Never use an undocumented feature in production. Roji. P. Thomas
http://toponewithties.blogspot.com
]]>