Okay. Taking this just a little bit further, I have added one more test to the suite, I hard-coded the calculation being done by the functions. I have also added some code from my 5 year old blog post where I compared hard-coded functions, scalar functions, and itvf's.

Have a look:

--I changed the order of these functions to the order in which

--they appear in the output.

--===== Alan's orignal code.

-- This actually is NOT an "iTVF". It's an "mTVF"

-- which is as bad or worse than a scalar UDR>

CREATE FUNCTION dbo.nsq_iTVF (@int int)

RETURNS @rs TABLE (n bigint) WITH SCHEMABINDING

AS

BEGIN

INSERT @rs SELECT @int/2;

RETURN;

END

GO

--===== Alan's orignal code

-- This is a scalar UDF

CREATE FUNCTION dbo.nsq_iSVF (@int int)

RETURNS bigint WITH SCHEMABINDING

AS

BEGIN

RETURN @int/2

END

GO

--===== Lynn's true iTVF code being used

-- as if a scalar UDF. (changed the name for testing)

CREATE FUNCTION dbo.Lynn_iTVF (@int bigint)

RETURNS TABLE WITH SCHEMABINDING

AS

return (SELECT cast(@int/2 as bigint) divby2);

GO

--===== Create some test data (Alan's original code)

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

DROP TABLE #tmp;

CREATE TABLE #tmp (n bigint);

INSERT #tmp

SELECT TOP 100000 ABS(CHECKSUM(NEWID()))

FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;

GO

RAISERROR('

======================================================================

Now, do the exact same tests adding hard-coding of the calculation

being done with the various functions with a simple DURATION calculation

instead of using SET STATISTICS, plus calculations from sys.db_exec_requests.

Also clearing proccache and systemcache.

======================================================================

',0,1) WITH NOWAIT

;

GO

RAISERROR('========== nsq_iTVF (mTVF) ===========================================',0,1) WITH NOWAIT

;

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @StartTime DATETIME,

@Bitbucket BIGINT;

Declare @cpu_ int,

@lreads_ int,

@eMsec_ int;

declare @CpuMs int,

@LogRds int,

@Elapsed int;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

Select

@StartTime = GETDATE(),

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

--===== Test the code

SELECT @Bitbucket = x.n,

@Bitbucket = s.n

FROM #tmp x

CROSS APPLY dbo.nsq_iTVF(x.n) s

;

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10)) +

' CPU(ms): ' + cast(@CpuMs as varchar(10)) + ' Logical Reads: ' + cast(@LogRds as varchar(10)) + ' Elapsed(ms): ' + cast(@Elapsed as varchar(10))

;

GO

RAISERROR('========== nsq_iSVF (scalar) =========================================',0,1) WITH NOWAIT

;

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @StartTime DATETIME,

@Bitbucket BIGINT;

Declare @cpu_ int,

@lreads_ int,

@eMsec_ int;

declare @CpuMs int,

@LogRds int,

@Elapsed int;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

Select

@StartTime = GETDATE(),

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

--===== Test the code

SELECT @Bitbucket = n,

@Bitbucket = dbo.nsq_iSVF(n)

FROM #tmp x

;

--===== Test the code

SELECT @Bitbucket = x.n,

@Bitbucket = s.n

FROM #tmp x

CROSS APPLY dbo.nsq_iTVF(x.n) s

;

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10)) +

' CPU(ms): ' + cast(@CpuMs as varchar(10)) + ' Logical Reads: ' + cast(@LogRds as varchar(10)) + ' Elapsed(ms): ' + cast(@Elapsed as varchar(10))

;

GO

RAISERROR('========== Lynn_iTVF (real iTVF) =====================================',0,1) WITH NOWAIT

;

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @StartTime DATETIME,

@Bitbucket BIGINT;

Declare @cpu_ int,

@lreads_ int,

@eMsec_ int;

declare @CpuMs int,

@LogRds int,

@Elapsed int;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

Select

@StartTime = GETDATE(),

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

--===== Test the code

SELECT @Bitbucket = x.n,

@Bitbucket = s.divby2

FROM #tmp x

CROSS APPLY dbo.Lynn_iTVF(x.n) s

;

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10)) +

' CPU(ms): ' + cast(@CpuMs as varchar(10)) + ' Logical Reads: ' + cast(@LogRds as varchar(10)) + ' Elapsed(ms): ' + cast(@Elapsed as varchar(10))

;

GO

RAISERROR('========== Hard-coded computation =====================================',0,1) WITH NOWAIT

;

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @StartTime DATETIME,

@Bitbucket BIGINT;

Declare @cpu_ int,

@lreads_ int,

@eMsec_ int;

declare @CpuMs int,

@LogRds int,

@Elapsed int;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

Select

@StartTime = GETDATE(),

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

--===== Test the code

SELECT @Bitbucket = x.n,

@Bitbucket = x.n/2

FROM #tmp x

;

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10)) +

' CPU(ms): ' + cast(@CpuMs as varchar(10)) + ' Logical Reads: ' + cast(@LogRds as varchar(10)) + ' Elapsed(ms): ' + cast(@Elapsed as varchar(10))

;

GO

DROP FUNCTION dbo.nsq_iTVF;

DROP FUNCTION dbo.nsq_iSVF;

DROP FUNCTION dbo.Lynn_iTVF;

GO

Here are my results on my 8 year old single processor hyper-threaded system:

(100000 row(s) affected)

======================================================================

Now, do the exact same tests adding hard-coding of the calculation

being done with the various functions with a simple DURATION calculation

instead of using SET STATISTICS, plus calculations from sys.db_exec_requests.

Also clearing proccache and systemcache.

======================================================================

========== nsq_iTVF (mTVF) ===========================================

Duration (ms): 14710 CPU(ms): 13984 Logical Reads: 1000393 Elapsed(ms): 14710

========== nsq_iSVF (scalar) =========================================

Duration (ms): 14356 CPU(ms): 13766 Logical Reads: 1000631 Elapsed(ms): 14354

========== Lynn_iTVF (real iTVF) =====================================

Duration (ms): 63 CPU(ms): 62 Logical Reads: 289 Elapsed(ms): 65

========== Hard-coded computation =====================================

Duration (ms): 63 CPU(ms): 63 Logical Reads: 253 Elapsed(ms): 64