In one of my recent articles, I mentioned the use of Table Valued Function (TVF) instead of Stored Procedure (SP). I received a follow up email asking what type of SP can be converted into a TVF. This is indeed a very interesting question! In fact, not all the SPs qualify to be converted to a TVF.
Please note that I am not encouraging to convert all the SPs to TVFs. Each SPs have their own usage and need. Here, I shall discuss about the type of SP that can be converted to a TVF.
First of all, you need to convert the SP that you are using to retrieve data and to insert it into any other table. Please read my original post wherein I have explained the scenario for the same. If you do not have to insert the data from the SP into another table, then there is no need to convert the SP to a TVF. This conversion will not necessarily enhance the performance.
In general, SP that returns only one resultset qualifies for conversion to TVF. If there are more than one resultset, then the SP cannot be converted to TVF as the TVF always returns one resultset. Moreover, if the SP has DML statements such as DELETE, UPDATE, INSERT or MERGE, it cannot be converted to a TVF. The usage of TVF is limited to the SELECT statement only. If the SP must be executed by using EXEC, it cannot be converted to TVF.
Do you know any other reason why a SP needs to be converted to a TVF?
Reference: Pinal Dave (https://blog.sqlauthority.com)
14 Comments. Leave new
Well, this is indeed a good discussion. according to me, if we are having just one SELECT statement which is returning one result sets than it would be good to convert it into TVF because we can join that result set to other SELECT statement.
while using SP, you can’t join it with anything else directly.
Hi Pinal,
Can you provide a link to the original article about converting stored procedures to TMVs?
how to insert data in two tables to use one Store Procedure
You said “Moreover, if the SP has DML statements such as DELETE, UPDATE, INSERT or MERGE, it cannot be converted to a TVF. The usage of TVF is limited to the SELECT statement only.”
That is true of an in-line TVF, but not true of a multi-statement TVF. If you could not INSERT into the table defined in the return of a multi-statement TVF there would not be anything to RETURN.
It would be correct if you added the exception stated in Books Online, “except to table variables”.
Thanx :)
Hello! Could you give me a link to the original article about converting a stored procedure into a table valued function? I can’t find any obvious links to it from this page…
Thank you!
I have a question about the TVF. When performing a SELECT on the TVF, the result was return in less than a second. But when the TVF is used in a join statement, it took about 4 minutes to run! I was wondering why it’s so slow when used in the JOIN?
On the other hand, if the TVF is loaded first into a temp table, then use this temp table in the JOIN, the result was under 15 seconds.
Hello Ting,
I think when used in JOIN, the TVF executed multiple times (depends on the number of records in other joined tables)while in SELECT statement, it is executed once.
So using the temporary table is better way to process its result.
Regards,
Pinal Dave
Hi Pinal Dave,
I have an issue with a Table valued fuction in SQL Server 2005.
In this function contain 4 CTEs and return around 35000 rows.
Now the issue is, sometimes it wont produce any result and got hang, but most of the times it will return the result within 2-5 seconds. I droped and recreated the funcion When I got the issue and that time it was working fine. Now daily one or two times this is happening.
I have checked the memory, TempDB, server space and all are fine.
Can you please suggest a solution for this issue.
Thanks and Regards
Thomas
Hi All I need to cionvert below SP to SQL Query..please help on this how to do that…
CREATE PROCEDURE [dbo].[usp_rpt_SourceAttribComp] (@WellLevelType Varchar(100),@Attributes varchar(2000) )
AS
BEGIN
–execute dbo.usp_rpt_SourceAttribComp ‘Well’ ,’BOTTOM_HOLE_LATITUDE,BOTTOM_HOLE_LONGITUDE’
SET NOCOUNT ON;
TRUNCATE TABLE dbo.SRCAttrbCompStage;
DECLARE @SQLEnertia AS VARCHAR(2000)
DECLARE @SQLProcount AS VARCHAR(2000)
DECLARE @WellMaster AS VARCHAR(2000)
DECLARE @MasterCol AS VARCHAR(2000)
DECLARE @APICode AS VARCHAR(2000)
DECLARE @SQLWellView AS VARCHAR(2000)
DECLARE @SQLIHS AS VARCHAR(2000)
DECLARE @RowsToProcess AS INT
DECLARE @CurrentRow AS INT
–declare @Attributes varchar(2000)
SET @CurrentRow = 0
–set @Attributes= ‘ABANDONMENT_DATE,ACQUISITION_DATE,ACTIVE_IND’
DECLARE @Attrib TABLE ( Name VARCHAR (200),seq int);
INSERT INTO @attrib
SELECT val,seq FROM [dbo].[f_split](@Attributes,’,’)
SET @RowsToProcess = @@ROWCOUNT
INSERT INTO dbo.SRCAttrbCompStage (PPDM_GUID, Attrib)
SELECT [PPDM_GUID],
name
FROM (SELECT DISTINCT [PPDM_GUID],
a.Name
FROM [Naveego_Breitburn].[Master].[WELL_VERSION] WV CROSS JOIN @Attrib AS a
WHERE WV.WELL_LEVEL_TYPE=@WellLevelType
) AS T;
WHILE @CurrentRow < @RowsToProcess
BEGIN
SET @CurrentRow = @CurrentRow + 1
SELECT @SQLEnertia ='UPDATE STG SET Enertia = WELL.' + Name + ' FROM dbo.SRCAttrbCompStage AS STG INNER JOIN [Naveego_Breitburn].[Master].[WELL_VERSION] AS WELL ON STG.[PPDM_GUID] = WELL.[PPDM_GUID] WHERE STG.Attrib = ' +'''' +Name+ ''''+ 'AND WELL.[Source] = '+ ''''+'ENERTIA'+''''
FROM @Attrib
WHERE seq = @CurrentRow
SELECT @SQLProcount ='UPDATE STG SET PROCOUNT = WELL.' + Name + ' FROM dbo.SRCAttrbCompStage AS STG INNER JOIN [Naveego_Breitburn].[Master].[WELL_VERSION] AS WELL ON STG.[PPDM_GUID] = WELL.[PPDM_GUID] WHERE STG.Attrib = ' +'''' +Name+ ''''+ 'AND WELL.[Source] = '+ ''''+'PROCOUNT'+''''
FROM @Attrib
WHERE seq = @CurrentRow
SELECT @WellMaster ='UPDATE STG SET WellMaster = WELL.' + Name + ' FROM dbo.SRCAttrbCompStage AS STG INNER JOIN [Naveego_Breitburn].[Master].[WELL] AS WELL ON STG.[PPDM_GUID] = WELL.[PPDM_GUID] WHERE STG.Attrib = ' +'''' +Name+ ''''
FROM @Attrib
WHERE seq = @CurrentRow
SELECT @MasterCol ='UPDATE STG SET MasterCol = WELL.Well_name,ApiCode =Well.WELL_GOVERNMENT_ID FROM dbo.SRCAttrbCompStage AS STG INNER JOIN [Naveego_Breitburn].[Master].[WELL] AS WELL ON STG.[PPDM_GUID] = WELL.[PPDM_GUID] '
–WHERE STG.Attrib = ' +'''' +Name+ ''''
FROM @Attrib
WHERE seq = @CurrentRow
EXECUTE (@SQLEnertia)
EXECUTE (@SQLProcount)
EXECUTE (@WellMaster)
EXECUTE (@MasterCol)
print @SQLEnertia
print @SQLProcount
print @WellMaster
print @MasterCol
END
—————————–Final query——————————–
SELECT
PPDM_GUID,
PA.DisplayValue AS Attrib,
Enertia,
Procount,
WellMaster,
MasterCol,
ApiCode
FROM dbo.SRCAttrbCompStage S
Left Outer JOIN [dbo].[PrimaryAttribtues] PA
ON S.Attrib = PA.Attributes
WHERE isnull(Procount,'') != isnull(Enertia,'') COLLATE SQL_Latin1_General_CP1_CS_AS
ORDER BY PPDM_GUID, Attrib;
SET NOCOUNT OFF;
END
GO
declare and assign the value to variables which are input parameters,
I would love to see your original post on this topic. I’m still trying to find compelling reasons to to use TVFs over Stored Procs. The ability to JOIN or APPLY directly in another statement seems about the only advantage I can find – even then I’ve often found that for the volume of data I manage in my queries, inserting the procedure results into a #temptable and applying indexes is more performant in the long run, even if the code is a bit more complex to write.
Hi Pinal – I wish you had a “Related Posts” section somewhere so I could find your original post on TVFs. I’ve been writing code for just over 10 years and have yet to find a compelling use case for these… and not for trying. In nearly EVERY case I have tried to implement TVFs I have written the equivalent VIEW or STORED PROCEDURE as a benchmark; and as I type this – I am watching my view execute in <=1 second to return 2864 rows, while the equivalent function takes over 6 minutes!
Yes – in the end it's longer to write out the inline code, or wrap up the procedure by inserting it into a temp table, but then the performance is really what counts here isn't it?… especially when I am querying 286,400 rows or more.
Please help me identify the use cases where a TVF is the ONLY thing that will do the job – or tell me that doesn't exist.
Thanks