0

I want to know which option is better or faster and why?

Basically I am looking to create a function and implement some logic but I having problems with the performance. I want to understand why returning a table as a variable is taking more time that a select.

CREATE FUNCTION [dbo].[ufn_GetID]  
(
    @Par BIT
)   
RETURNS TABLE 
AS RETURN
(
     Select * from dbo.Mytable where a=@Par
)
GO




or 

CREATE FUNCTION [dbo].[ufn_GetID]  
(
    @Par BIT
)  
RETURNS @Return Table 
( 
[Id] bigint  ,
[a] BIT  NULL
)
BEGIN

insert into @Return
Select * from dbo.Mytable where a=@Par

    RETURN
end

Thanks

sublue
  • 13
  • 4
  • 1
    Search for “Inline Table Value Function” (ITVF). Basically, it allows SQL Server more room for optimizations, similar to using a View (SQL server can expand the view when generating the query plan). Of course “better” depends on suitability and “faster” depends on context and doesn’t always matter. The performance of ITVF vs TVF depends heavily on the USAGEs of such; in particular, the number of executions and suitability of query plan optimizations. – user2864740 Apr 14 '20 at 02:57
  • 1
    Functions is slow than view (Select's) but in SQL2019 optimize inline TVF - Read this, Helpful https://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function – Amirhossein Apr 14 '20 at 05:16

0 Answers0