I am getting some interesting (read 'undesirable') results in SSME.
I am attaching two scripts, one that creates a TestTable and the second creates a TestView, based on TestTable.
When TestTable is created, enter the following rows (format is TestID, Sequence):
1 - 99
2 - 98
3 - 97
4 - 1
5 - 2
6 - 3
When the data is entered, highlight TestTable from the list of tables, right-click and select Open Table. The result should be that all entries are shown, in the TestID order. The actual result (at least for me) is that the order is by Sequence. If I look at the SQL, it is a simple select statement (Select * from TestTable), so there is nothing that should result in an order.
Highlight TestView in the list of Views, then right-click to Open View.The result should be that all entries are shown in TestID order. The actual result (at least for me) is that the order is by Sequence. If I look at the SQL, it is a simple select statement (Select * from TestView), so there is nothing that should prevent the Order by clause executing.
If you then Highlight TestView in the list of Views, then right-click to Modify, the query is displayed. If you then execute the query, the result is as expected (sorted by TestID).
Any explanation would be greatly appreciated.
thanks,
Flavelle
Code to create TestTable is shown below:
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable](
[TestID] [int] NOT NULL,
[Sequence] [int] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [UQ_TestTable_Sequence] UNIQUE NONCLUSTERED
(
[Sequence] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is an identifier' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TestTable', @level2type=N'COLUMN', @level2name=N'TestID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is a sort order' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TestTable', @level2type=N'COLUMN', @level2name=N'Sequence'
GO
Code to create TestTable is shown below: SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[TestView]
AS
SELECT TOP (100) PERCENT TestID, Sequence
FROM dbo.TestTable
ORDER BY TestID
GO
Strange Results in executing views in SSME
randy.liden
The order of a result set is determined by the order by clause on the outermost select statement. When you issue a Select * from TestView there is no order specified on the outermost select, so the engine is free to return the results in any order.
Using order by in a view only determines the rows that qualify for the TOP clause, not the order the rows will be output in. For a more complete description of this behavior see this article http://support.microsoft.com/kb/841845/en-us
mahricky
Jerome:
Thanks for the explanation. Although the information fully explains what I am seeing (thanks for that), I wonder if I'm the only one to whom this does not make intuitive sense.
If I write a View, and I include a sort order in the view, I would intuitively expect that the result would be in the specified sort order, unless I changed it within a query. For example:
select * from TestView order by Sequence would order the list by Sequence
select * from TestView should result in a list that is ordered as specified in TestView.
Still, I do have an explanation, for which I'm grateful, and I will obviously have to watch the gotchas when I use the View.
Many thanks,
Flavelle