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)
AS
BEGIN
  INSERT INTO @result
  SELECT FirstColumn from SomeTable order by FirstColumn
  RETURN
END

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

Published under:  ·  ·  ·  ·