Stairway to U-SQL Level 10: Table-Valued Functions and UDTs

Question

Post reply

Stairway to U-SQL Level 10: Table-Valued Functions and UDTs

Mike McQuillan

SSCertifiable

Points: 6020
More actions
October 12, 2016 at 12:08 am

#313794

Comments posted to this topic are about the item Stairway to U-SQL Level 10: Table-Valued Functions and UDTs

Viewing 6 posts - 1 through 6 (of 6 total)

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

Michael Rys SSC Enthusiast Points: 152 More actions · Answer 1

Great article and examples.

Some additional comments:

You can let the TVF infer the return schema by leaving out the TABLE type. E.g., RETURNS @result
Unlike T-SQL, U-SQL always inlines TVFs. This allows the optimizer to push predicates and column pruning into, through and out of the TVFs.
TVFs allow you to return multiple results: CREATE FUNCTION Tvf(...) RETURNS @res1, @res2 AS .... The TVF then gets invoked with (@r1, @r2) = Tvf(...);

Cheers

Michael

Mike McQuillan SSCertifiable Points: 6020 More actions · Answer 2

Hi Mike

Thanks for these comments - much appreciated.

I will try to work these into a future article.

Regards,

Mike.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

akljfhnlaflkj

SSC Guru

Points: 76202

October 12, 2016 at 6:58 am

#1906100

It was nice just being reminded about TVFs, thanks.

Jeff Moden SSC Guru Points: 1004592 More actions · Answer 4

Good article especially considering the interplay between UDTs and TVFs.

Shifting gears, does "U-SQL" allow for the concept of iTVFs (inline TVFs) rather than mTVFs (multi-statement TVFs)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Rys SSC Enthusiast Points: 152 More actions · Answer 5

Hi Jeff

U-SQL only has one type of TVFs: imTVFs: inlineable, multi-statement TVFs. No "stupid" differentiation.

Cheers

Michael

Stairway to U-SQL Level 10: Table-Valued Functions and UDTs

Cookies on SQLServerCentral