I just stumbled across a weird misconception that I'd like to help you to avoid:
If you consume the results of a (non-inline) table valued function, the results are not in the same order as if you take the content óf the TVF in your query.
So, if you have a query like
select * from SomeTable order by FirstColumn
and you put it into a table valued function like
CREATE FUNCTION myFunction()
RETURNS @result TABLE(FirstColumn int)
INSERT INTO @result
SELECT FirstColumn from SomeTable order by FirstColumn
and call it as
SELECT * from myFunction()
you will probably, but not always, get the FirstNumber in order. But you cannot rely on it, as it could change with different execution plans. As soon as the plan generator chooses a parallel plan you might get a partially sorted data stream.
This conforms to MS who say that a SELECT from anything is not guaranteed to be sorted in any way unless you specify an explicit ORDER BY clause. So in my case you get the correct result by using
SELECT * from myFunction() ORDER BY FirstColumn
· · · ·