ORDER BY WITHIN UDF FUNCTION WORKING IN 2005 AND NO MORE IN 2005

I have a very simple UDF Function doing a :

select TOP 100 PERCENT CLI_Name from CLI_Clients order by CLI_Name

The order by works well with SQL 2000 and does not work anymore after migrating to 2005.

Any clue ???
javilmerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brejkCommented:
You can't count on any sort order when you don't use ORDER BY clause.
0
brejkCommented:
Sorry, ignore my previous post (did not notice order by ;-)).
0
brejkCommented:
Why would you order by records withing the function and not within the query that uses the function:

SELECT * FROM YourUDF() ORDER BY ...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

javilmerAuthor Commented:
because actually the order by can be different depending on the customer (somtimes by name, sometimes by company), and we do not want to modify the code.
0
brejkCommented:
Ok, this behavoiur is now a default behaviour for views and UDFs in SQL Server 2005. What you can do is one of two things (both require code modification): add a column generated with ROW_NUMBER() function to the resultset or change TOP 100 PERCENT to TOP <something_very_close_to_100_like_99.9999999999> PERCENT. The second approach is not very safe (as the UDF can return some incomplete resultset), so the first one is preferable.
0
javilmerAuthor Commented:
Effectively, select TOP 99.99999999 PERCENT works, and not TOP 100 PERCENT.
This is so logical :)
Could you specify a little bit about adding a column generated with row number ? Actually, our UDF function is a little more complex, and does include the index :

SELECT TOP 100 PERCENT CLI_ID, CLI_NAME from CLI_Clients order by CLI_Name

where CLI_ID is an auto number and is the key.
0
brejkCommented:
SELECT
  ROW_NUMBER() OVER(ORDER BY CLI_NAME) AS Rn,
  CLI_ID,
  CLI_NAME
FROM CLI_Clients

Then you should have rows ordered by CLI_Name but of course with one more column in the resultset.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>Actually, our UDF function is a little more complex, and does include the index<<
So why don't you post the real function and then we can help you better.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.