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.
| Author |
Topic |
|
MrOnge
Starting Member
3 Posts |
 Posted - 2009-02-27 : 05:47:30
|
| I am in the process of upgrading from SQL 2000 to SQL 2005 and one user defined function is very confusing.It is a function called by a view that returns a string that is used for sorting a table of numbers.In SQL 2000 the view works perfectly returning the numbers sorted correctly.In SQL 2005 the view works in design mode. But when you save and then right click on the view and select 'open view', the values are returned as they are in the table unsorted. I have also tried pulling the data into Excel and again the data is unsorted.When I restored the DB into 2005 the function has appeared in 'Scalar-valued functions'Is there something I am missing here?Any help/ideas appreciated.Andre |
|
|
subhash chandra
Starting Member
40 Posts |
Posted - 2009-02-27 : 05:55:06
|
| Actually this is a bit change in the way of query process in SQL 2000 and SQL 2005. In SQL 2000 if a function called in side a view returned sorted result then the view also returned sorted result but in SQL 2005 internally sorted result is not returned by view. To get sorted result write the order by clause in the view itself. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-27 : 06:02:07
|
The only way to guarantee a sorted resultset is to use ORDER by at the consumer end. Period.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
MrOnge
Starting Member
3 Posts |
Posted - 2009-02-27 : 06:09:18
|
| Thankyou for a very prompt reply! The order by clause is already set in the view. If I unset order by, the design view then returns unsorted as well. If I add the order by clause back in, the design view returns the sorted list, and again just opening the view returns unsorted values!Just read your post Peso, will give that a try. Thank you both. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-27 : 06:32:57
|
This behaviour is documented in Books Online.See http://msdn.microsoft.com/en-us/library/ms143179(SQL.90).aspxand section Transact-SQL and paragraph ORDER BY in a view definition.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
MrOnge
Starting Member
3 Posts |
Posted - 2009-02-27 : 11:35:20
|
| I have set the ORDER BY clause at the consumer end now. All is well.Thanks for both of your inputs. |
|
|
|
|
|
|
User defined function SQL 2005