Inline table valued function (ITVF) and a multi-statement table valued function (MSTVF)

--http://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function

-- http://sqlblogcasts.com/blogs/simons/archive/2008/11/03/TSQL-Scalar-functions-are-evil-.aspx  

 

In researching Matt's comment, I have revised my original statement.
He is correct, there will be a difference in performance between an inline table valued function (ITVF) and a multi-statement table valued function (MSTVF)

even if they both simply execute a SELECT statement.

SQL Server will treat an ITVF somewhat like a VIEW in that it will calculate an execution plan using the latest statistics on the tables in question.
A MSTVF is equivalent to stuffing the entire contents of your SELECT statement into a table variable and then joining to that.Thus, the compiler cannot use any table statistics on the tables in the MSTVF.

So, all things being equal, (which they rarely are), the ITVF will perform better than the MSTVF.

 

Multi-statement functions are very very often a huge performance hit because they stop the query optimiser from using statistics. If I had $1 for every time I've seen multi-statement function use cause a very poor choice of execution plan (mostly because it usually estimates the returned row count as 1), I'd have enough to buy a small car

 

 

1.highlight line

2. nested loop -> 1 row table (function)

whole table pages
SET STATISTICS TIME ON
SET STATISTICS IO ON
select * from table1

performance user define funtion not use CPU Parallelizm
not visible in execution plan - always estimate 1 rows - > nested loop
too visible in sql profiler - filter by object type 18004
SQL SERVER 2008

Solution:

1) INNER HASH JOIN - more save
 merge join need pre-sorting
 
2) Inline Table Valued Functions - not function at all in execution plan

create funtion
as
return table

return (select 1 from ABC)

table value function - black box

3) CLR

 

http://sqlblogcasts.com/blogs/simons/archive/2008/11/03/TSQL-Scalar-functions-are-evil-.aspx  
--Scalar function from Inline Table Valued function

So how does that actually work. You create your table valued function to return 1 row. In your query you then use a subquery to get the value from the table valued function.

create function PadLeftTVF(@val varchar(100), @len int, @char char(1))

returns table

as

  return (select right(replicate(@char,@len) + @val, @len) val)

go

select max(val)

from (select  (select * from PadLeftTVF(o.name + c.name, 100,'0')) val

        from msdb.sys.columns o

  cross join msdb.sys.columns c) d

select max(val)

from (select (select val from PadLeftTVF(v1.col1+ v2.col2 , 100,'0')) val

from veryLargeTable v1

join veryLargeTable v2 on v2.col1 = v1.col2 ) d

 

 

Sample:

 

 

 

 

 

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512)) 

RETURNS table 

AS 

RETURN ( 

    WITH Pieces(pn, start, stop) AS ( 

      SELECT 1, 1, CHARINDEX(@sep, @s) 

      UNION ALL 

      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) 

      FROM Pieces 

      WHERE stop > 0 

    ) 

    SELECT  

      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS ID 

    FROM Pieces 

  ) 

GO 



SELECT * FROM dbo.Split (',','3,4,5,6,7')