August 28, 2011 at 5:26 pm
Hi,
Just was wondering, from the best practices point of view, are there any disadvantages in using table valued parameters for your SPs in the application. E.g.: table valued parameters requires declaration of types, which may be used by developers for something else.
Thanks.
August 29, 2011 at 8:53 am
Roust_m (8/28/2011)
Hi,Just was wondering, from the best practices point of view, are there any disadvantages in using table valued parameters for your SPs in the application. E.g.: table valued parameters requires declaration of types, which may be used by developers for something else.
Thanks.
They are great when you need to pass what is effectively an array. It is best to keep your types as generic as possible so you don't have to define types more than needed. I wrote up an example a few months ago about how to use a table valued parameter as a parameter from asp.net page. From both the database performance side and the developer it is a good way to pass this type of dynamically sized info.
I wrote up this Proof of concept a few months ago in response to a discussion the forum. This is by no means the only way you could use a TVP but it certainly demonstrates one method that works pretty well.
_______________________________________________________________
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/
August 29, 2011 at 7:11 pm
Sean Lange (8/29/2011)
Roust_m (8/28/2011)
Hi,Just was wondering, from the best practices point of view, are there any disadvantages in using table valued parameters for your SPs in the application. E.g.: table valued parameters requires declaration of types, which may be used by developers for something else.
Thanks.
They are great when you need to pass what is effectively an array. It is best to keep your types as generic as possible so you don't have to define types more than needed. I wrote up an example a few months ago about how to use a table valued parameter as a parameter from asp.net page. From both the database performance side and the developer it is a good way to pass this type of dynamically sized info.
I wrote up this Proof of concept a few months ago in response to a discussion the forum. This is by no means the only way you could use a TVP but it certainly demonstrates one method that works pretty well.
You say to keep the types as generic as possible, but what if, after the type got used in many places you want to change the type so to affect only one place? You would have to define a new type. Would it be better to keep the types isolated to one object in the DB, e.g. the table valued parameter used in a SP?
August 30, 2011 at 7:36 am
If you noticed in my example my type is a table with a single integer column. That allows the flexibility to be used anytime I need a list of integers. You can of course make your table more complex and specific to a particular object or process. My guess is you will find more usage out of a more generic datatype centric approach but the more specific objects may provide some very granular detail when it is needed. The upside of a generic type means it doesn't need to change to meet business requirements. Play around with them and figure out what works best for you.
_______________________________________________________________
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/
August 31, 2011 at 7:18 am
There is a performance implication in that TVPs cannot have statistics on them so you wind up with the same occassionally suboptimal plans that you can get with table variables.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy