Order By in View problem 2

[jbenson001]

Hi all,

I have a view defined as(SQL Server 2005):

SELECT TOP (100) PERCENT IsActiveFlg AS Active, Name, Address1 AS [Address 1], Address2 AS [Address 2], City, State, Zip, HomePhone AS [Home Phone], BusinessPhone AS [Business Phone], CellPhone AS [Cell Phone], CustomerId
FROM  dbo.Customer
ORDER BY Name

When I save and run this code in SQL Server Management Studio, the code runs fine and orders the rows by name as expected. However, I noticed when I select from the view in my VB app, the rows displayed in the order of the ID column, not by name. To check, I opened a query window and did a

Select * from CustomerView

It displays in ID order, not name order. I thought maybe the name of the "Name" column, but changed it and still the problem. It only displays corretly in the view designer window. The only way to make it work is to do:

Select * from CustomerView Order By Name

Can anyone explain this behavior? Why is my Order By being ignored?

Thanks everyone...

Jim

 

[SQLSister]

Even in tables with clustered indexes you may not alawys get the data back inthat order. If you want the results of a selct to be ordered, you always need to put in an order by column.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jbenson001]

The table is ordered by ID (Clustered).

I am wondering why the order by is being ignored in the view definition.

 

[gmmastros]

Jim

Take a look at this page...

http://msdn2.microsoft.com/en-us/library/ms188385.aspx

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

George, thanks for that, did not see it in BOL. Seems kind of silly, but now at least I understand.

Thanks again...

Jim

 

[SQLDenis]

IN SQL server 2005 the TOP 100% ORDER By in views is meaningless
if you need to order a view you need to specify ORDER BY

workaround would be TOP 99.9% on a table that has < 100 rows OR 99.99% on a table that has < 1000 rows, etc etc

Read more here

http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=79de45cb-2da6-4510-ae43-7e5e42e4c02e

becuae it has performance implications



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

Thanks for all the help guys.. I knew you would come through