SQL 205 Views

Question

SQL 205 Views

jdhouse

SSC Rookie

Points: 27
More actions
August 24, 2009 at 11:19 am

#138324

ALTER VIEW [dbo].[vFrmMainCboMessageItem]
AS
SELECT TOP (100) PERCENT itemId, messageTypeId, sequence, description, required
FROM dbo.messageItem
WHERE (messageTypeId IS NOT NULL)
ORDER BY messageTypeId, sequence
returns the expected row order when the view is executed in the design 'view' however when the view is 'opened' it reverts back to the natural order of the table, sorted by the identity field 'itemId'.
I don't think that is the case in SQL 2000. Anyone else encounter this? The work around is to create a stored procedure which works as expected however I've got a project that will be migrated to SQL 05 in a few months and with all the views I'm using this could get very ugly.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

jdhouse (8/24/2009)
ALTER VIEW [dbo].[vFrmMainCboMessageItem]
AS
SELECT TOP (100) PERCENT itemId, messageTypeId, sequence, description, required
FROM dbo.messageItem
WHERE (messageTypeId IS NOT NULL)
ORDER BY messageTypeId, sequence
returns the expected row order when the view is executed in the design 'view' however when the view is 'opened' it reverts back to the natural order of the table, sorted by the identity field 'itemId'.
I don't think that is the case in SQL 2000. Anyone else encounter this? The work around is to create a stored procedure which works as expected however I've got a project that will be migrated to SQL 05 in a few months and with all the views I'm using this could get very ugly.

Views are just like tables in SQL Server 2005 and forward. The only way to ensure the order of the output is to put an order by in the query pulling data from the view. With SELECT TOP (100) PERCENT, SQL Server 2005 iqnores the order by. This change in behaviour is by design and was noted in the documentation.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Additional Info from BOL (Books Online) with regard to CREATE VIEW:

Note:

The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

jdhouse SSC Rookie Points: 27 More actions · Answer 3

Thanks for the info, looks like I have job security for a while yet.

jdhouse SSC Rookie Points: 27 More actions · Answer 4

oops. just for the fun of it I decided to try the view without the "Top 100 percent" clause and got:

Msg 1033, Level 15, State 1, Procedure vMessageItem, Line 6

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Guess views are not very useful in 2005. I've been using 2005 for several years now but our IT department prevents us from creating permanent objects so I've not experience this one before. Hmmm.

So the appropriate solution is a SP?

The purpose of the table is a lookup for a drop-down box which may have additional items added by the users. The view was to sort them by the desired sequence rather than the order in which they were entered into the table. Seems like a reasonable thing to want to do.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Views are extremely useful. What it means is that instead of doing

SELECT

Col1,

Col2

FROM

dbo.MyView;

where the returned result is properly ordered, you have to do

SELECT

Col1,

Col2

FROM

dbo.MyView

ORDER BY

Col1; -- or whatever column in dbo.MyView the order needs to be accomplished by

The view can still restrict the columns available from the underlying table, include other columns from other tables, etc. It also allows access to the underlying table(s) to be restricted as well.

jdhouse SSC Rookie Points: 27 More actions · Answer 6

What I"m trying to do is have a view name that can be used as the rowsource property of the drop-down list so I don't have to spell out the whole query. The drop down list gets different row sources programatically depending on what the user is trying to do so it is a lot easier to write: cboItemList.rowsource= "vItemList" than cboItemList.rowsource= "SELECT ....." Of course having the items in the correct order is important also.

jdhouse SSC Rookie Points: 27 More actions · Answer 7

Anyway thanks for confirming that this wasn't just an oversight on my part. I was beginning to doubt my sanity.