Ordered Common Language Runtime Table-Valued User-Defined Functions

SQL Server 2008 enhances support for CLR table-valued user-defined functions by letting you define the order in which the rows are returned from the function. When you use the CREATE FUNCTION statement to register the CLR function in the database, you use an ORDER clause to specify the order. As an example, the CLR function fn_split whose definition is provided in Listing 1 always returns the rows ordered by the pos column. You can indicate this option when you register the function in the database. The following code registers two versions of the function, one without the ORDER clause and one with it:

CREATE FUNCTION dbo.fn_split_no_order

  (@string AS NVARCHAR(2000), @separator AS NCHAR(1))

RETURNS TABLE(pos INT, element NVARCHAR(2000))

EXTERNAL NAME SplitCLR.SplitCLR.fn_split;

GO

CREATE FUNCTION dbo.fn_split_order_by_pos

  (@string AS NVARCHAR(MAX), @separator AS NCHAR(1))

RETURNS TABLE(pos INT, element NVARCHAR(2000))

ORDER (pos)

EXTERNAL NAME SplitCLR.SplitCLR.fn_split;

GO

The ORDER clause indicates to the optimizer that the rows are returned from the function ordered by pos. Knowing that the rows are preordered can help in processing operations such as ORDER BY, GROUP BY, DISTINCT, and merge join. Note that you can’t trick SQL Server; at runtime, SQL Server will ensure that the rows are returned in the specified order and will fail the code and generate an error message if they aren’t.

As an example for using the two versions of the fn_split function, suppose you need to split an array and return the elements sorted by the pos column. If you use the fn_split_no_order function, SQL Server has no guarantee that the rows are presorted by pos and will therefore add a sort operation to the execution plan. You can see this by examining the plan for the following query:

SELECT *

FROM dbo.fn_split_no_order(

  N'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',

  N',') AS T

ORDER BY pos;

If you use the fn_split_order_by_pos function, SQL Server won’t bother to sort the values because pos presorts them:

SELECT *

FROM dbo.fn_split_order_by_pos(

  N'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',

  N',') AS T

ORDER BY pos;