User Defined Table type

Question

User Defined Table type

JKSQL

SSCrazy Eights

Points: 8481
More actions
February 13, 2013 at 9:28 am

#270097

I just can't seem to find an article in how User Defined table types are stored. Do these go into the TempDb? Also is there an advantage of using these over #Tables in Sprocs. I would think "Yes" because of the recompile issue with a DDL statement in your sproc. I like the fact that both of these allow indexes.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

JKSQL (2/13/2013)
I just can't seem to find an article in how User Defined table types are stored. Do these go into the TempDb? Also is there an advantage of using these over #Tables in Sprocs. I would think "Yes" because of the recompile issue with a DDL statement in your sproc. I like the fact that both of these allow indexes.

Yes the data could be stored in TempDB just like a temp table. The main advantage is that you can pass them as parameters. There is certainly no rule that says you should use this instead of temp tables. They both have their advantages and disadvantages.

_______________________________________________________________

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/

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 2

When you say "could be stored in the Tempdb" that is because a user defined table type is technically memory driven verse disk driven like #temp table?

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

JKSQL (2/13/2013)
When you say "could be stored in the Tempdb" that is because a user defined table type is technically memory driven verse disk driven like #temp table?

Take a look at this article from Wayne. He does a far better job explaining this than I could possibly do.

http://www.sqlservercentral.com/articles/Temporary+Tables/66720/[/url]