Date: 09/07/06 (SQL Server)    Keywords: sql

Hi all.
There is a strange problem I found. I upgraded SQL Server 2000 to 2005, I am a novice there. It seems that Views and inline functions do not support ORDER BY clause (The Select clause of a view may contain ORDER BY clause, but it seems to be ignored when running). More specifically, let dbo.Table1 be a table with columns ID, Name and a dbo.View1 created as:

CREATE dbo.View1
AS
SELECT TOP (100) PERCENT ID, Name
FROM dbo.Table1
ORDER BY Name

Then, if I run the query:

SELECT * FROM dbo.View1

it gives me no ordering (by [Name] field, neither in Management studio, neither in client applications (ADO 2.8 is used), while there WAS ordering in the same query results in MS SQL 2000. To get ordering, I must write again

SELECT * FROM dbo.View1 ORDER BY Name

What's wrong here? (I tried to find something in documentation, but could not find any useful things. The only essential note was that ORDER BY clause can't be used in views and inline function unless TOP attribute is specified in SELECT clause. But I knew that :-) ).

Source: http://community.livejournal.com/sqlserver/52104.html