The inline TVF

Question

Post reply

The inline TVF

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
December 14, 2016 at 8:14 pm

#72661

Comments posted to this topic are about the item The inline TVF

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22600 More actions · Answer 1

Carlo Romagnano

SSC-Insane

Points: 22600

December 15, 2016 at 1:10 am

#1918088

I love a lot the inline TVF.

😛

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

The "correct answer" (This can return a table from a query inside of a RETURN statement surrounded by parenthesis) is sneaky. Using ssc's most popular inline function as an example, where are those pesky parentheses?

ALTER FUNCTION [dbo].[DelimitedSplit8K_LEAD]

--===== Define I/O parameters

(@pString VARCHAR(8000), @pDelimiter CHAR(1))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== "Inline" CTE Driven "Tally Table” produces values from 0 up to 10,000...

-- enough to cover VARCHAR(8000)

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT 0 UNION ALL

SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

SELECT t.N+1

FROM cteTally t

WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)

)

--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.

SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),

Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))

FROM cteStart s

;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

Umer Akram

SSC-Insane

Points: 22209

December 15, 2016 at 6:00 am

#1918147

Invisible Parentheses 😛

sknox SSChampion Points: 12388 More actions · Answer 4

Yes, the TechNet article is inaccurate. As per the MSDN documentation (https://msdn.microsoft.com/en-us/library/ms186755.aspx), the parentheses are optional.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

I love the ITVF because of its efficiency. Good question - let's see more on this topic. Maybe this is my prompting to write some. 😉 Thanks, Steve.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

Yeah I felt that none of the answers were totally accurate. I knew that you neither required parenthesis nor used begin/end. I had to guess between them and chose incorrectly. Meh, whatever.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

George Vobr SSCrazy Eights Points: 9916 More actions · Answer 7

sknox (12/15/2016)
Yes, the TechNet article is inaccurate. As per the MSDN documentation (https://msdn.microsoft.com/en-us/library/ms186755.aspx), the parentheses are optional.

+1

and still it is necessary to read in the MSDN https://msdn.microsoft.com/en-us/library/ms191007.aspx

slowly and carefully the paragraph Types of functions and his part of the Table-Valued Functions,

then You are close to the estimate of the correct answer ... 😉

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

I've never written an iTVF using parenthesis around the query. However, the other answers were completely wrong and the correct answer states that it can not that it must.

After answering, I just had to try it, but still don't like it.

CREATE FUNCTION itvf_ParenthesisTest(

@Param1 int

)

RETURNS TABLE

AS

RETURN (SELECT @Param1 AS Param1)

GO

SELECT *

FROM itvf_ParenthesisTest(1);

GO

DROP FUNCTION itvf_ParenthesisTest;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Luis Cazares (12/15/2016)
I've never written an iTVF using parenthesis around the query. However, the other answers were completely wrong and the correct answer states that it can not that it must.
After answering, I just had to try it, but still don't like it.
CREATE FUNCTION itvf_ParenthesisTest(
@Param1 int
)
RETURNS TABLE
AS
RETURN (SELECT @Param1 AS Param1)
GO
SELECT *
FROM itvf_ParenthesisTest(1);
GO
DROP FUNCTION itvf_ParenthesisTest;

Why did you rule this one out, Luis?

"This returns a table from a function where the last line of the function must be a SELECT query."

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

timwell SSCertifiable Points: 5315 More actions · Answer 10

George Vobr (12/15/2016)
and still it is necessary to read in the MSDN https://msdn.microsoft.com/en-us/library/ms191007.aspx
slowly and carefully the paragraph Types of functions and his part of the Table-Valued Functions,
then You are close to the estimate of the correct answer ... 😉

And if you click to view the example of a TVF from that paragraph it doesn't have parentheses on the select after the RETURN....

https://msdn.microsoft.com/library/bb386954(v=vs.110).aspx

Luis Cazares SSC Guru Points: 183698 More actions · Answer 11

ChrisM@Work (12/15/2016)
Luis Cazares (12/15/2016)
I've never written an iTVF using parenthesis around the query. However, the other answers were completely wrong and the correct answer states that it can not that it must.
After answering, I just had to try it, but still don't like it.
Why did you rule this one out, Luis?
"This returns a table from a function where the last line of the function must be a SELECT query."

I interpreted it as being a multi-statement function. Mentioning the last line, implies that it's different from the first line. It might be confusing, but seemed clear to me.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Luis Cazares (12/15/2016)
ChrisM@Work (12/15/2016)
Luis Cazares (12/15/2016)
I've never written an iTVF using parenthesis around the query. However, the other answers were completely wrong and the correct answer states that it can not that it must.
After answering, I just had to try it, but still don't like it.
Why did you rule this one out, Luis?
"This returns a table from a function where the last line of the function must be a SELECT query."
I interpreted it as being a multi-statement function. Mentioning the last line, implies that it's different from the first line. It might be confusing, but seemed clear to me.

Oh right. I hadn't considered a multi-statement function. I took it as being the function declaration part - params, RETURNS... then RETURN, followed by the last line - a SELECT (which might include WITH...)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286573 More actions · Answer 13

Luis Cazares (12/15/2016)
ChrisM@Work (12/15/2016)
Luis Cazares (12/15/2016)
I've never written an iTVF using parenthesis around the query. However, the other answers were completely wrong and the correct answer states that it can not that it must.
After answering, I just had to try it, but still don't like it.
Why did you rule this one out, Luis?
"This returns a table from a function where the last line of the function must be a SELECT query."
I interpreted it as being a multi-statement function. Mentioning the last line, implies that it's different from the first line. It might be confusing, but seemed clear to me.

That is the same reason I ruled that one out. Even if it didn't imply or suggest it was a MVTF the verbiage allowed for it and since the question title was ITVF it was an easy one to exclude.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 14

That option's opening the door to multi-statement functions also made me suspicious, but ultimately I rejected it for a simpler reason.

There simply is no requirement around what the last line (note we're not even talking about statements here; we're talking about the last line of characters in the function definition) of the function must be.

Nothing stops me from throwing some lines of comments at the end of the function definition, and those most definitely are not SELECT queries 🙂