/*============================================================================
  File:     ordered_tvf.sql

  Summary:  This script shows the performance improvement gained by using
			SQL Server 2008's ordered table-value (CLR-based) function feature.
			
			NOTE: Using the ORDER BY keyword in CREATE FUNCTION is not 
			supported by Visual Studio autodeploy. Use this script and manual
			assembly and function deployment.

  Date:     August 2008

  SQL Server Version: 10.0.1600.22 (RTM)
------------------------------------------------------------------------------
  Written by Bob Beauchemin, SQLskills.com

  For more scripts and sample code, check out http://www.SQLskills.com

  This script is intended only as a supplement to SQL Server Jumpstart program.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'clr enabled', 1
reconfigure

if DATABASEPROPERTY('test', 'Version') is null
create database test
go

use test
go

create assembly orderedtvf from 'c:\temp\orderedtvf.dll'
go

create function Fibonacci (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (next asc)
as external name orderedtvf.FData.Fibonacci
go

create function Fibonacci2 (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev asc)
as external name orderedtvf.FData.Fibonacci
go

create function FibonacciUnOrdered (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
as external name orderedtvf.FData.Fibonacci
go

create function FibonacciWrong (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev desc)
as external name orderedtvf.FData.Fibonacci
go

-- cost: 0.0279081 
-- TVF -> Sort -> Select
select * from dbo.FibonacciUnordered(3,4,5)
order by next

-- cost: 0.0023802
-- TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.Fibonacci(3,4,5)
order by next

-- Wrong order
-- cost: 0.0292881
-- TVF -> Segment -> Sequence Project -> Assert -> Sort -> Select
select * from dbo.Fibonacci(3,4,5)
order by prev

-- cost: 0.0023802
-- TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.Fibonacci2(3,4,5)
order by prev

-- 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 desc
