Problem with sort in view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with sort in view

Author

Topic

milamilos
Starting Member

7 Posts

Posted - 2008-09-23 : 10:39:12

I have problem in SQL Server 2005 that I didn't have in SQL Server 2000. When I create a view with ORDER BY and then select data from it, I get unsorted records. For example:
CREATE VIEW View1
AS
SELECT Id, Name
FROM Table1
ORDER BY Id

and then

SELECT * FROM View1

return unsorted records. In that way sorting inside views became useless. In SQL Server 2000 that was not the case.

Is there a solution for that problem? We have lot of code that is relying on sorting inside views and we want to migrate to SQL Server 2005 without changing it.

Thanks in advanced,
Mila

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-23 : 11:11:23

You can't use Order by Clause while creating view unless there is top clause in select statement. It is same in SQL 2000 as well.
It depends on whether you have Primary key Clustered on table. Or specify Order by .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 11:13:29

There is no use of using ORDER BY inside view. Unless you use ORDER BY explicitly in select statement using view you cant guarantee the order of retrieval of records from view.So it should be
SELECT * FROM View1 ORDER BY Id

milamilos
Starting Member

7 Posts

Posted - 2008-09-23 : 11:18:53

I have TOP 100 PERCENT in select statement, I just forgot to write it. So the body of view is:
SELECT TOP 100 PERCENT Id, Name
FROM Table1
ORDER BY Id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 11:29:47

quote:
Originally posted by milamilos

I have TOP 100 PERCENT in select statement, I just forgot to write it. So the body of view is:
SELECT TOP 100 PERCENT Id, Name
FROM Table1
ORDER BY Id

As i told earlier it doesnt make any sense to add the order by in body of view.Its always better to use order by in select list where you retrieve the value from view.

milamilos
Starting Member

7 Posts

Posted - 2008-09-23 : 11:36:26

quote:
Originally posted by visakh16

quote:
Originally posted by milamilos

I have TOP 100 PERCENT in select statement, I just forgot to write it. So the body of view is:
SELECT TOP 100 PERCENT Id, Name
FROM Table1
ORDER BY Id

As i told earlier it doesnt make any sense to add the order by in body of view.Its always better to use order by in select list where you retrieve the value from view.

But a I told earlier we have hole information system that is relying on sorting inside views, so if it isn't possible in SQL 2005, we can't migrate on it without very serious problems.

Thank you all.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-23 : 11:38:56

Then use same logic as SQL 2000 for SQL 2005 .There is no difference in it.

milamilos
Starting Member

7 Posts

Posted - 2008-09-23 : 11:42:13

But there is. It works well in 2000! Same views.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 11:43:43

this is from books online. just look at lines in blue.

The SELECT clauses in a view definition cannot include the following:
COMPUTE or COMPUTE BY clauses
An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statementNote:
The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

milamilos
Starting Member

7 Posts

Posted - 2008-09-23 : 11:49:38

I can't poste pictures to show you that, regardlessly what is sad in books online, in SQL 2000 when you query sorted view you get sorted records. So, we definitely have problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 11:54:32

quote:
Originally posted by milamilos

I can't poste pictures to show you that, regardlessly what is sad in books online, in SQL 2000 when you query sorted view you get sorted records. So, we definitely have problem.

that may be by a matter of chance or because of query plan.

milamilos
Starting Member

7 Posts

Posted - 2008-09-24 : 09:55:01

quote:
Originally posted by visakh16

this is from books online. just look at lines in blue.

The SELECT clauses in a view definition cannot include the following:
COMPUTE or COMPUTE BY clauses
An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statementNote:
The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

In Books Online for SQL Server 2000 there is only:

The SELECT clauses in a view definition cannot include the following:
COMPUTE or COMPUTE BY clauses
An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement.

without any Note.

And I discover that if you put TOP 100 PERCENT inside the view, then when you query it you get unsorted records, but if you put TOP 99 PERCENT or TOP 100 (or 100000, or any other number of records) and then you query it, you get sorted records. And definitely it seams like a BUG.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-24 : 12:27:40

How do you know which TOP 100 rows to return if you do not specify an ORDER BY? Obviously, you don't. That is why you need to specify one. Even when you specify an ORDER BY in the view you are not 100% guaranteed to get the results in that order. Thus, specifying an ORDER BY on a view that returns all the tuples (TOP 100 PERCENT) doesn't have any meaning. So, not a bug, just basic set theory.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources