Order By In a SQL Table Valued Function

Question

I've read about this problem on a few different sites, but I still don't understand the solution. From what I understand, SQL will optimize the query in the function and sometimes the Order By clause will be ignored. How can you sort results?

How can I sort results in a simple table valued function like this?

Create function [dbo].fTest

--Input Parameters
(@competitionID int)

--Returns a table
RETURNS @table TABLE (CompetitionID int )

as

BEGIN
    Insert Into @table (CompetitionID)
    select CompetitionID from Competition order by CompetitionID desc
    RETURN
END

UPDATE

I found inserting a primary key identity field seems to help (as mentioned in the answer posted Martin Smith). Is this a good solution?

--Returns a table
RETURNS @table TABLE
(
    SortID int IDENTITY(1,1) PRIMARY KEY,
    CompetitionID int 
)

In reference to Martin's answer below, sorting outside of the select statement isn't that easy in my situation. My posted example is a stripped down version, but my real-life issue involves a more complicated order by case clause for custom sorting. In addition to that, I'm calling this function in an MVC controller with a LINQ query, which means that custom sorting would have to be added to the LINQ query. That's beyond my ability at this point.

If adding the identity field is a safe solution, I'm happy to go with that. It's simple and easy.

Answer 1

The order by needs to be in the statement that selects from the function.

SELECT CompetitionId
FROM [dbo].fTest()
ORDER BY CompetitionId

This is the only way to get reliable results that are assured to not suddenly break in the future.