?
Solved

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

Posted on 2008-02-11
9
Medium Priority
?
273 Views
Last Modified: 2008-09-20
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 ???
0
Comment
Question by:javilmer
  • 5
  • 2
8 Comments
 
LVL 18

Expert Comment

by:brejk
ID: 20865177
You can't count on any sort order when you don't use ORDER BY clause.
0
 
LVL 18

Expert Comment

by:brejk
ID: 20865181
Sorry, ignore my previous post (did not notice order by ;-)).
0
 
LVL 18

Expert Comment

by:brejk
ID: 20865186
Why would you order by records withing the function and not within the query that uses the function:

SELECT * FROM YourUDF() ORDER BY ...
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:javilmer
ID: 20865212
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
 
LVL 18

Expert Comment

by:brejk
ID: 20865245
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
 

Author Comment

by:javilmer
ID: 20865824
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
 
LVL 18

Accepted Solution

by:
brejk earned 1000 total points
ID: 20865841
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 20866194
>>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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question