/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

Sunday T-SQL Tip: Inline vs. Multistatement Table Valued Functions
2011-10-23
2011-12-18 - Update - include Multistatement implementation in addition to Scalar UDF
*****************************************************/

set nocount on
set xact_abort on
go

if exists
(
	select * 
	from 
		sys.tables t join sys.schemas s on
			t.schema_id = s.schema_id
	where 
		t.name = 'OrderItems' and s.name = 'dbo'
) 
	drop table dbo.OrderItems
go

if exists
(
	select * 
	from 
		sys.tables t join sys.schemas s on
			t.schema_id = s.schema_id
	where 
		t.name = 'Orders' and s.name = 'dbo'
) 
	drop table dbo.Orders
go

create table dbo.Orders
(
	OrderId int not null,
	OrderDate datetime not null,
	OrderNum varchar(32) not null,
	Amount money not null,
	OtherColumns char(100) not null
		constraint DEF_Orders_OtherColumns
		default 'This is placeholder',
	
	constraint PK_Orders
	primary key clustered(OrderId)
)
go

create table dbo.OrderItems
(
	OrderId int not null,
	OrderItemId int not null identity(1,1),
	Product varchar(32) not null, 
	Quantity smallint not null,
	UnitPrice money not null,
	OtherColumns char(100) not null
		constraint DEF_OrderItems_OtherColumns
		default 'This is placeholder',
	
	constraint PK_OrderItems
	primary key clustered(OrderId, OrderItemId),
	
	constraint FK_OrderItems_Orders
	foreign key(OrderId)
	references dbo.Orders(OrderId)
	on update cascade
	on delete cascade
)
go



begin tran
	;with OrdersCTE(Id)
	as
	(
		select 1
		union all
		select Id + 1
		from OrdersCTE 
		where Id < 1000
	)
	insert into dbo.Orders(OrderId, OrderDate, Amount, OrderNum)
		select Id, DATEADD(day, -Id % 150, GetDate()), 
			Id % 200, 'Order ' + CONVERT(varchar(6),Id)
		from OrdersCTE
	option (maxrecursion 0)

	;with OrderItemsCTE(Id)
	as
	(
		select 1
		union all
		select Id + 1
		from OrderItemsCTE 
		where Id < 100000
	)
	insert into dbo.OrderItems(OrderId, Product, Quantity, UnitPrice)
		select Id % 999 + 1, 'Product ' + CONVERT(varchar(6),Id % 100), 
			ID % 10, ID % 25
		from OrderItemsCTE
	option (maxrecursion 0)
commit
go

if exists(
	select * 
	from sys.objects 
	where object_id = OBJECT_ID(N'[dbo].[AvgItemCost]') and type in (N'FN')
)
	drop function [dbo].[AvgItemCost]
GO

create function dbo.AvgItemCost
(
	@OrderId int
)
returns money
as
begin
	declare
		@Result money
	
	select @Result = Sum(Quantity * UnitPrice) / Sum(Quantity)
	from dbo.OrderItems
	where OrderId = @OrderId

	return @Result
end
go

declare
	@DT datetime = getDate()

select Sum(dbo.AvgItemCost(OrderId))
from dbo.Orders 

select DATEDIFF(millisecond,@DT,getDate()) as [Exec Time]
go


if exists(
	select * 
	from sys.objects 
	where object_id = OBJECT_ID(N'[dbo].[AvgItemCostInline]') and type in (N'IF')
)
	drop function [dbo].AvgItemCostInline
GO

create function dbo.AvgItemCostInline
(
	@OrderId int
)
returns table
as
return
(
	select Sum(Quantity * UnitPrice) / Sum(Quantity) as [AvgCost]
	from dbo.OrderItems
	where OrderId = @OrderId
)
go

declare
	@DT datetime = getDate()

select Sum(a.AvgCost)
from 
	dbo.Orders o cross apply
		dbo.AvgItemCostInline(o.OrderId) a

select DATEDIFF(millisecond,@DT,getDate()) as [Exec Time Inline]
go

/* Enable Execution Plan */

select Sum(dbo.AvgItemCost(OrderId))
from dbo.Orders 

select Sum(a.AvgCost)
from 
	dbo.Orders o cross apply
		dbo.AvgItemCostInline(o.OrderId) a
go

-- UPDATE 2011-12-18 

if exists(
	select * 
	from sys.objects 
	where object_id = OBJECT_ID(N'[dbo].[AvgItemCost2]') and type in (N'TF')
)
	drop function [dbo].[AvgItemCost2]
GO

create function dbo.AvgItemCost2
(
	@OrderId int
)
returns @Result table
(
	AvgCost money
)
as
begin
	insert into @Result(AvgCost)
		select Sum(Quantity * UnitPrice) / Sum(Quantity)
		from dbo.OrderItems
		where OrderId = @OrderId
	return
end
go

declare
	@DT datetime = getDate()
	,@S money

select @S = Sum(dbo.AvgItemCost(OrderId))
from dbo.Orders 

select DATEDIFF(millisecond,@DT,getDate()) as [Exec Time Scalar]
select @DT = GETDATE()

select @S = Sum(a.AvgCost)
from 
	dbo.Orders o cross apply
		dbo.AvgItemCost2(o.OrderId) a

select DATEDIFF(millisecond,@DT,getDate()) as [Exec Time MultiStatement]
select @DT = GETDATE()

select @S = Sum(a.AvgCost)
from 
	dbo.Orders o cross apply
		dbo.AvgItemCostInline(o.OrderId) a

select DATEDIFF(millisecond,@DT,getDate()) as [Exec Time Inline]
go



