Result set of VIEWS not returned SORTED although view has a ''SORT BY''.
-
1) We migrated a 2000 DB (Dictionary - case sensitive) to SQL 2005 (dictionary - not case sensitive) but noticed the following anomaly with the views. In SQL 2000 the results sets returned from querying a view was all sorted (as intended), but when we use the same view on a SQL 2005 server the result sets were returned unsorted. We couldn't figure out why it was doing it. We discovered it when we migrated the database, and the application that was using all the views (the data from the views were being used in pull down menus, pick list, etc.) was returning unsorted data when users click click on the pull downs, and pick list. Are we suppose to do any conversion on the DB for this to work properly.
Thanks
Chris
-
Do a search on this site and you will find many threads on this topic. Here is one.
-
This is as expected actually. A view is a virtual table. In SQL 2000, the order by declared in a view definition was honored, however, SQL does not guarantee order unless you specify the order in the select itself. SQL 2005 will honor the order by if you are returning less than 100 percent of the result set possible (top 10 or top 50 percent for example).
If I remember correctly, this may be mentioned in BOL, but I don't remember where exactly. You will also find several, if not numerous, threads on this site regarding this behaviour.
-
Thank you everyone I will do as per your recommendations.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply