Careful now. What I said is correct but the code you wrote isn't. As Lynn suggested, the nsq_iTVF function is actually an "mTVF" or "Multi-Line Table Valued Function" and, especially for purposes of calculating a scalar value, is much worse than an actual iTVF (Inline Table Valued function) that is used to return a scalar value.

There's another problem here, at least in SQL Server 2005. Like I said in the article on the subject, the use of SET STATISTICS will add a huge amount of overhead and make scalar UDFs and mTVFs look painfully slow. You need to use a difference in times to fairly measure duration when either is involved.

Last but not least, the display is also known as the "Great Equalizer" because it takes more time to display a row than it does to calculate the row. When you return something like 100,000 rows, many methods will look like they take virtually the same amount of time because the amount of time to display the rows will overshadow the actual differences. To wit, you need to dump the output of such testing to a variable to take the display times out of the picture.

I've incorporated all of that in the following code. I've also added a true iTVF and used it as a scalar fuction. Last but not least, there are two sets of identical tests... one with SET STATISTICS and one with a smple duration timer.

First, here's the original 2 functions and test table that Alan was nice enough to build. It also includes Lynn's function which is the only tru iTVF of the 3.

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

Here's the test code. Again, there are two sets of identical tests. The first set uses SET STATISTICS which makes all but Lynn's function (an iTVF) look really bad. Do notice the use of the @Bitbucket variable which keeps the display from becoming the "Great Equalizer".

RAISERROR('

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

Test the functions with SET STATISTICS TIME ON so we can see how much

it actually affects scalar and mTVF functions but not the iTVF. Each

test is in its own batch so we can run individual tests if we want.

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

',0,1) WITH NOWAIT

;

--===== Setup test environment

SET STATISTICS TIME, IO OFF; --Well turn TIME on for each test for a nice, clean output.

SET NOCOUNT ON;

GO

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

;

--===== Create bit-bucket variable

DECLARE @Bitbucket BIGINT

;

--===== Test the code

SET STATISTICS TIME ON;

SELECT @Bitbucket = x.n,

@Bitbucket = s.n

FROM #tmp x

CROSS APPLY dbo.nsq_iTVF(x.n) s;

SET STATISTICS TIME OFF;

GO

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

;

--===== Create bit-bucket variable

DECLARE @Bitbucket BIGINT

;

--===== Test the code

SET STATISTICS TIME ON;

SELECT @Bitbucket = n,

@Bitbucket = dbo.nsq_iSVF(n)

FROM #tmp x;

SET STATISTICS TIME OFF;

GO

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

;

--===== Create bit-bucket variable

DECLARE @Bitbucket BIGINT;

--===== Test the code

SET STATISTICS TIME ON;

SELECT @Bitbucket = x.n,

@Bitbucket = s.divby2

FROM #tmp x

CROSS APPLY dbo.Lynn_iTVF(x.n) s;

SET STATISTICS TIME OFF;

GO

RAISERROR('

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

Now, do the exact same tests with a simple DURATION calculation

instead of using SET STATISTICS. Again, each test is in its own

batch so we can run individual tests if we want.

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

',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;

SELECT @StartTime = GETDATE()

;

--===== Test the code

SELECT @Bitbucket = x.n,

@Bitbucket = s.n

FROM #tmp x

CROSS APPLY dbo.nsq_iTVF(x.n) s

;

--===== Display the duration

PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) 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;

SELECT @StartTime = GETDATE()

;

--===== Test the code

SELECT @Bitbucket = n,

@Bitbucket = dbo.nsq_iSVF(n)

FROM #tmp x

;

--===== Display the duration

PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) 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;

SELECT @StartTime = GETDATE()

;

--===== Test the code

SELECT @Bitbucket = x.n,

@Bitbucket = s.divby2

FROM #tmp x

CROSS APPLY dbo.Lynn_iTVF(x.n) s

;

--===== Display the duration

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

;

GO

Here are the results from my 11 year old single cpu 2005 desktop box. Notice the HUGE differences when you compare the first run to the second. Also notice that Lynn's true iTVF code (more than 7 times faster) blows the doors off the previous fastest code... just like it did in the article. 😉

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

Test the functions with SET STATISTICS TIME ON so we can see how much

it actually affects scalar and mTVF functions but not the iTVF. Each

test is in its own batch so we can run individual tests if we want.

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

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

SQL Server Execution Times:

CPU time = 58750 ms, elapsed time = 103897 ms.

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

SQL Server Execution Times:

CPU time = 10828 ms, elapsed time = 34761 ms.

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

SQL Server Execution Times:

CPU time = 94 ms, elapsed time = 94 ms.

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

Now, do the exact same tests with a simple DURATION calculation

instead of using SET STATISTICS. Again, each test is in its own

batch so we can run individual tests if we want.

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

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

Duration (ms): 24640

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

Duration (ms): 733

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

Duration (ms): 90

Here are the results from my more modern quad core I5 laptop running 2008. As you can see, the duration for the scalar function is still drastically affected by SET STATISTICS. I'll also point out that Lynn's function absolutely smokes compared to the other functions.

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

Test the functions with SET STATISTICS TIME ON so we can see how much

it actually affects scalar and mTVF functions but not the iTVF. Each

test is in its own batch so we can run individual tests if we want.

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

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

SQL Server Execution Times: CPU time = 4664 ms, elapsed time = 4733 ms.

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

SQL Server Execution Times: CPU time = 406 ms, elapsed time = 430 ms.

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

SQL Server Execution Times: CPU time = 15 ms, elapsed time = 20 ms.

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

Now, do the exact same tests with a simple DURATION calculation

instead of using SET STATISTICS. Again, each test is in its own

batch so we can run individual tests if we want.

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

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

Duration (ms): 4353

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

Duration (ms): 286

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

Duration (ms): 20

This code is full of lessons on how to test code. For example, if you turn the Actual Execution Plan on and rerun the 2nd half of the test code, you'll see that the 2nd and 3rd test have identical execution plans and the % of batch is identical even though we just proved that nothing could be further from the truth. The lesson here is to never use an Execution Plan to determine the winner in a performance race. You actually have to conduct the race to find out for sure.