Table valued functions

Question

Table valued functions

buddy__a

Hall of Fame

Points: 3222
More actions
October 4, 2007 at 10:48 pm

#70774

I have an application that has a table valued function and the database that it is installed has a collation sequence that is different from the server default and it generates an error when it's run.
So, do table valued functions create temp tables in tempdb?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Everyone's favorite answer: it depends. If you have an inline function that is returing a table value, it effectively works like a view and the query processor blows out the query within the execution plan of the query it's running in. A multistatement UDF returns a table variable. Table variables do get written to tempdb.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

So, do table valued functions create temp tables in tempdb?

Yes, they do... Please refer to Q4/A4 at the following URL...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

--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)