Order by in a 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

General SQL Server Forums

New to SQL Server Programming

Order by in a VIEW

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2012-10-05 : 09:09:59

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

15732 Posts

Posted - 2012-10-05 : 09:29:58

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 09:40:10

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

Johnph
Posting Yak Master

103 Posts

Posted - 2012-10-05 : 09:43:32

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?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 09:54:50

Check this link

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

--
Chandu

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-05 : 10:04:35

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-05 : 12:09:03

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources