\nAnother 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.\n<\/p>\n
\ncreate assembly orderedtvf from 'C:\\temp\\OrderedTVF.dll'
\ngo\n<\/p>\n
\n— no order clause
\ncreate function FibonacciUnOrdered (@prevstart int, @start int, @rows int)
\nreturns table (next int, prev int)
\nas external name orderedtvf.FData.Fibonacci
\ngo\n<\/p>\n
\nThe 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.\n<\/p>\n
\ncreate function FibonacciByNext (@prevstart int, @start int, @rows int)
\nreturns table (next int, prev int)
\norder (next asc) — this is new
\nas external name orderedtvf.FData.Fibonacci
\ngo\n<\/p>\n
\ncreate function FibonacciByPrev (@prevstart int, @start int, @rows int)
\nreturns table (next int, prev int)
\norder (prev asc) — same concept, different ordering column
\nas external name orderedtvf.FData.Fibonacci
\ngo\n<\/p>\n
\nNow let's do some testing. The query plan iterators and plan cost are shown as comments.\n<\/p>\n
\n— cost: 0.0279081
\n— TVF -> Sort -> Select
\nselect * from dbo.FibonacciUnordered(3,4,5)
\norder by next\n<\/p>\n
\nWith 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.\n<\/p>\n
\n— cost: 0.0023802
\n— TVF -> Segment -> Sequence Project -> Assert -> Select
\nselect * from dbo.FibonacciByNext(3,4,5)
\norder by next\n<\/p>\n
\n— cost: 0.0023802
\n— TVF -> Segment -> Sequence Project -> Assert -> Select
\nselect * from dbo.FibonacciByPrev(3,4,5)
\norder by prev\n<\/p>\n
\nNote 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\n<\/p>\n
\n— Different order
\n— cost: 0.0292881 (more than Unordered = 0.0279081)
\n— TVF -> Segment -> Sequence Project -> Assert -> Sort -> Select
\nselect * from dbo.FibonacciByNext(3,4,5)
\norder by prev\n<\/p>\n
\nIn addition, the query plan guarentees that you don't lie in your order clause. Here's proof.\n<\/p>\n
\ncreate function FibonacciWrong (@prevstart int, @start int, @rows int)
\nreturns table (next int, prev int)
\norder (prev desc) — THEY ARE IN ASCENDING ORDER, NOT DESCENDING
\nas external name orderedtvf.FData.Fibonacci
\ngo\n<\/p>\n
\n— Error:
\n— The order of the data in the stream does not conform to the ORDER hint
\n— specified for the CLR TVF 'dbo.FibonacciWrong'.
\n— The order of the data must match the order specified in the ORDER hint for a CLR TVF.
\n— Update the ORDER hint to reflect the order in which the input data is ordered,
\n— or update the CLR TVF to match the order specified by the ORDER hint.
\nselect * from dbo.FibonacciWrong(3,4,5)
\norder by prev\n<\/p>\n
\nRemember, 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.\n<\/p>\n
\nSo, declaring ordered TVFs is worth it.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,38],"tags":[],"class_list":["post-740","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sqlclr"],"yoast_head":"\n