Views, order by - 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)

Views, order by

Author

Topic

iminore
Posting Yak Master

141 Posts

Posted - 2007-12-06 : 05:49:54

I've looked through the Forum but can find no reference to the 'order by' clause not working in SQL 2005.
There is now a hotfix available on request from Microsoft but am I alone at being donkered at this bug? Does nobody else order their views? Has anyone applied this fix?
I read somewhere that Microsoft have over 1,000 programmers working on SQL Server. How many does it take to order a view? Ho hum.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 05:55:18

It is not gauranteed that the result set is ordered even if you use order by when creating a view. The best approach is to create view without order by and order the result when querying

Madhivanan

Failing to plan is Planning to fail

iminore
Posting Yak Master

141 Posts

Posted - 2007-12-06 : 05:56:19

It was guaranteed in SQL 2000.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 05:59:48

quote:
Originally posted by iminore

It was guaranteed in SQL 2000.

In SQL Server 2000 you cant create a view that has Order by clause

Madhivanan

Failing to plan is Planning to fail

iminore
Posting Yak Master

141 Posts

Posted - 2007-12-06 : 06:11:13

Well I've done it many times.
CREATE VIEW dbo.View
AS
select top 100 percent col1 from Table order by col1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 07:18:38

quote:
Originally posted by iminore

Well I've done it many times.
CREATE VIEW dbo.View
AS
select top 100 percent col1 from Table order by col1

I meant without Top clause

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 07:21:24

Also refer http://sqlblog.com/blogs/tibor_karaszi/archive/2007/11/28/sorted-views.aspx

Madhivanan

Failing to plan is Planning to fail

iminore
Posting Yak Master

141 Posts

Posted - 2007-12-06 : 07:46:32

Hmm. OK I'll go away. Thanks for the link. Very interesting.

luggi
Starting Member

4 Posts

Posted - 2007-12-07 : 08:41:16

Hi,

Had the same problem. It should look like this: ... AS SELECT TOP (2147483647)... On this page I found the solution - please take a look into http://solutiondev.spaces.live.com/blog/cns!1060B9B1678804D2!261.entry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources