SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Order by in a VIEW
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Johnph
Yak Posting Veteran

89 Posts

Posted - 10/05/2012 :  09:09:59  Show Profile  Reply with Quote
Hello, I recently discovered that the order by clause cannot be used in creating views. Is there some type of bypass to this?

My query looks like this

SELECT A, B, C from TABLE
ORDER BY A,B

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 10/05/2012 :  09:29:58  Show Profile  Visit robvolk's Homepage  Reply with Quote
Use TOP 100 PERCENT. However, ORDER BY is meaningless in a view and will not guarantee results will be returned in order, unless you also specify ORDER BY when selecting from the view. You're better off without it.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 10/05/2012 :  09:40:10  Show Profile  Reply with Quote
You can create like this.. But it will give one more extra column

CREATE VIEW my_View
AS
SELECT a, b, c, row_number() over(order by a,b) as rn FROM table

--
Chandu
Go to Top of Page

Johnph
Yak Posting Veteran

89 Posts

Posted - 10/05/2012 :  09:43:32  Show Profile  Reply with Quote
Ok, I will give both a try. What do you mean when you say:

specify ORDER BY when selecting from the view?

orderby clause in another view?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 10/05/2012 :  09:54:50  Show Profile  Reply with Quote
Check this link

http://blog.sqlauthority.com/2009/11/24/sql-server-interesting-observation-top-100-percent-and-order-by/

--
Chandu
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 10/05/2012 :  10:04:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
Here's another demonstration:
CREATE TABLE base(a INT, b INT, c INT)
GO
CREATE VIEW base_view AS SELECT TOP 100 PERCENT a,b,c FROM base ORDER BY a,b
GO

TRUNCATE TABLE base
INSERT base VALUES(1,1,3)
INSERT base VALUES(1,2,2)
INSERT base VALUES(1,3,1)
INSERT base VALUES(3,0,1)
INSERT base VALUES(3,0,2)
INSERT base VALUES(3,0,3)
INSERT base VALUES(2,1,3)
INSERT base VALUES(2,2,2)
INSERT base VALUES(2,3,1)

SELECT *, 'Table - no index' FROM base
SELECT *, 'View - no index, no order' FROM base_view
SELECT *, 'View - no index, order' FROM base_view ORDER BY a,b

--drop index c on base
CREATE CLUSTERED INDEX c ON base(c,a)

SELECT *, 'Table - index c,a' FROM base
SELECT *, 'View - index c,a, no order' FROM base_view
SELECT *, 'View - index c,a, order' FROM base_view ORDER BY a,b

DROP INDEX c ON base
CREATE CLUSTERED INDEX c ON base(c,b DESC)

SELECT *, 'Table - index c,b desc' FROM base
SELECT *, 'View - index c,b desc, no order' FROM base_view
SELECT *, 'View - index c,b desc, order' FROM base_view ORDER BY a,b

DROP VIEW base_view;
DROP TABLE base;
On SQL 2008 R2, I got different orders for both the table and the view without using ORDER BY. By changing the clustered index, the view tends to return data in the same order as selecting from the table, even though the view has an ORDER BY in its definition. The only time I get ordered results is by specifying ORDER BY in the SELECT.

Even if it's unlikely that the clustered index will change, it still demonstrates that ORDER BY is only guaranteed on the SELECT. Deleting data, or updating key values, can also change the order of the data returned if you don't specify ORDER BY.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4352 Posts

Posted - 10/05/2012 :  12:09:03  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

<snip>Even if it's unlikely that the clustered index will change, it still demonstrates that ORDER BY is only guaranteed on the SELECT. Deleting data, or updating key values, can also change the order of the data returned if you don't specify ORDER BY.

This!

Although, you MIGHT be able to get ordered results from your view by using TOP and such. The ONLY way to guarantee order is to use an ORDER BY on the SELECT from the view.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000