Inline Table Valued Function

Question

Inline Table Valued Function

soulchyld21

Mr or Mrs. 500

Points: 554
More actions
June 6, 2013 at 5:43 am

#294911

Hi, I would like some help with my first inline table valued function,
I have to create one that builds a table containing two columns, one with an integer value that has been derived through a loop, eg a list of integers from one to four and the other needs to have some text based on the integer in the previous column, so maybe odd next to the odd integers and even next to the even ones.
Would anyone be able to point me in the right direction, I have already come acrcoss a problem whereby the declaration of the parameter within the function is not allowed.
I would need to generate the numbers and perform some check to generate the text based on the number (that's my initial approach)
Would you be able to point me in the right direction and guide my approach to the problem?
Much appreciated
Soulchyld

Viewing 14 posts - 1 through 13 (of 13 total)

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

logitestus SSCrazy Points: 2928 More actions · Answer 1

Did you check out this blog: http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/? According to BOL, an inline function is a single statement, if you need to create variables then it sounds like you might need a multistatement function. If that doesn't work for you, have you thought of trying out a Common Table Expression so you can "loop" through your data.

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

This simple sample should get you started. Have a play, explain if it doesn't meet your requirements.

CREATE FUNCTION [dbo].[MyFirstITVF]

(

@StartNum INT

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

SELECT d.MyInt, x.MyString

FROM

(SELECT MyInt = @StartNum + 1 UNION ALL

SELECT MyInt = @StartNum + 2 UNION ALL

SELECT MyInt = @StartNum + 3 UNION ALL

SELECT MyInt = @StartNum + 4) d

CROSS APPLY (SELECT MyString = CASE WHEN d.MyInt%2 = 1 THEN 'Odd' ELSE 'Even' END) x

GO

SELECT * FROM [dbo].[MyFirstITVF] (0)

^{“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

soulchyld21 Mr or Mrs. 500 Points: 554 More actions · Answer 3

Ok, That helps a little, I thought I would be able to figure out the rest but alas, what I need is a counter to add the integers until we reach the input number, so if we run the function with 4 it will give us 1,2,3,4 and the text next to it, that is where I think a loop would be required? where would I include my loop in the function?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

No need for a loop, what you need is a Tally (numbers) table.

The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]

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

soulchyld21 Mr or Mrs. 500 Points: 554 More actions · Answer 5

Thanks, so the idea is to build a tally table on the fly and select from that for my query is that correct?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

I've heard the tally table described as the "Swiss Army Knife of SQL" by more than one person on this site. It can solve a lot of problems and avoid the performance degradation of looping constructs. The performance gains you can realize are through the roof. It also has the coolness factor.

Yes, you can build one on the fly, but a lot of people keep a tally table as a permanent table. See Jeff Moden's article Luis referenced above for building one.

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

Sean Lange SSC Guru Points: 286601 More actions · Answer 7

Ed Wagner (6/6/2013)
I've heard the tally table described as the "Swiss Army Knife of SQL" by more than one person on this site. It can solve a lot of problems and avoid the performance degradation of looping constructs. The performance gains you can realize are through the roof. It also has the coolness factor.
Yes, you can build one on the fly, but a lot of people keep a tally table as a permanent table. See Jeff Moden's article Luis referenced above for building one.

LOL I just posted this exact thing on another thread. In lieu of using a permanent table I use a view that creates the tally table on the fly. 0 reads to access it and I don't have to remember the syntax over and over.

create View Tally as

WITH

E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

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

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

select N from cteTally

_______________________________________________________________

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/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

Now that's a pretty cool idea I hadn't even considered. What's the performance like when compared to a physical table? I haven't tried it, but I'd imagine that, since everything's done in memory, you'd completely avoid the cost of disk I/O.

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

Sean Lange SSC Guru Points: 286601 More actions · Answer 9

Ed Wagner (6/6/2013)
Now that's a pretty cool idea I hadn't even considered. What's the performance like when compared to a physical table? I haven't tried it, but I'd imagine that, since everything's done in memory, you'd completely avoid the cost of disk I/O.

This discussion is almost an exact duplicate from the other thread.

As long as the definition is memory there is 0 reads. If it has to load the definition, it is still faster than a physical read from a table. Super duper fast.

_______________________________________________________________

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/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

Excellent - thanks very much for the tip. I'm going to have to try this one.

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

soulchyld21 Mr or Mrs. 500 Points: 554 More actions · Answer 11

Thanks everyone, I eventually managed to solve it and will stick the code up sometime soon, Hopefully it will be acceptable

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

Sean Lange (6/6/2013)

... In lieu of using a permanent table I use a view that creates the tally table on the fly. 0 reads to access it and I don't have to remember the syntax over and over....

It works well as an iTVF too!

ALTER FUNCTION [dbo].[InlineTally]

(@RowCount INT)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

(

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

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c), -- 10 x 10 x 10 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b) --1000 x 1000 rows max

SELECT TOP (@RowCount) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

GO

SELECT * FROM dbo.InlineTally (100000)

^{“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: 286601 More actions · Answer 13

ChrisM@Work (6/7/2013)

Sean Lange (6/6/2013)
... In lieu of using a permanent table I use a view that creates the tally table on the fly. 0 reads to access it and I don't have to remember the syntax over and over....
It works well as an iTVF too!
ALTER FUNCTION [dbo].[InlineTally]
(@RowCount INT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
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 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c), -- 10 x 10 x 10 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b) --1000 x 1000 rows max
SELECT TOP (@RowCount) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
GO
SELECT * FROM dbo.InlineTally (100000)

That is pretty cool too. Nice to have either option.

_______________________________________________________________

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/

Inline Table Valued Function

Cookies on SQLServerCentral