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.
Order by in a VIEW
Posted - 10/05/2012 : 09:09:59
