Another cool SQLCLR feature in SQL Server 2008 is the ability to define a table-valued function with an ORDER clause. If you have intimate knowledge that the function always returns rows in sorted order, you declare your SQLCLR UDF with an ORDER clause in the CREATE FUNCTION DDL statement. Let's experiment with this using the cheap and easy Fibonacci sequence function from Dan Sullivan and my SQL Server 2005 Developer's Guide.
create assembly orderedtvf from 'C:\temp\OrderedTVF.dll'
go
— no order clause
create function FibonacciUnOrdered (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
as external name orderedtvf.FData.Fibonacci
go
The Fibonacci sequences generated are always in ascending order because that's how the function is implemented. In fact, they are in order by both the "next" and "prev" column. Because there is no limit to the number of method signatures we can have over the same SQLCLR UDF, we use the same SQLCLR method, just changing the DDL statement and function name.
create function FibonacciByNext (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (next asc) — this is new
as external name orderedtvf.FData.Fibonacci
go
create function FibonacciByPrev (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev asc) — same concept, different ordering column
as external name orderedtvf.FData.Fibonacci
go
Now let's do some testing. The query plan iterators and plan cost are shown as comments.
— cost: 0.0279081
— TVF -> Sort -> Select
select * from dbo.FibonacciUnordered(3,4,5)
order by next
With an ordered TVF, there is no SORT iterator, but there are extra query plan steps to operate on the ordered set. AND… the query cost is over 10x lower.
— cost: 0.0023802
— TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.FibonacciByNext(3,4,5)
order by next
— cost: 0.0023802
— TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.FibonacciByPrev(3,4,5)
order by prev
Note that it IS best to have a different TVF name for each sort order, and "no order", if you plan to use different ORDER BY clauses. This one has a Sort AND its cost is greater than the function that's declared UnOrdered
— Different order
— cost: 0.0292881 (more than Unordered = 0.0279081)
— TVF -> Segment -> Sequence Project -> Assert -> Sort -> Select
select * from dbo.FibonacciByNext(3,4,5)
order by prev
In addition, the query plan guarentees that you don't lie in your order clause. Here's proof.
create function FibonacciWrong (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev desc) — THEY ARE IN ASCENDING ORDER, NOT DESCENDING
as external name orderedtvf.FData.Fibonacci
go
— Error:
— The order of the data in the stream does not conform to the ORDER hint
— specified for the CLR TVF 'dbo.FibonacciWrong'.
— The order of the data must match the order specified in the ORDER hint for a CLR TVF.
— Update the ORDER hint to reflect the order in which the input data is ordered,
— or update the CLR TVF to match the order specified by the ORDER hint.
select * from dbo.FibonacciWrong(3,4,5)
order by prev
Remember, you're not only saving a SORT iterator in the query plan, you're saving memory too. The SORT iterator requires a memory grant. And because there are no stats for SQL Server to use in these "opaque to SQL" functions, the memory grant for the SORT iterator in FibonacciUnOrdered is 1024K. And, we hope that the rather generous memory grant is enough, else memory is being allocated during query execution.
So, declaring ordered TVFs is worth it.