1

I'm finding that there are several points in the stored procedures that I'm creating where I want a stored procedure to accept a list of IDs in various tables that the SP should perform an operation on. I'm considering using Table Value Parameters. What I'm wondering about is whether it's a good practice to create a single Type which just contains a single "ID int" column, and use that as the data type for all of my TVP's, or whether it's a better practice to define a type for each SP, in case I end up in the future wanting to expand the values passed.

Does anyone have experience with this?

edit In case it makes any difference to your recommendation, the ID lists that I'm passing may be on the order of 150,000 entries.

Adam Ness
  • 6,224
  • 4
  • 27
  • 39

2 Answers2

2

What I can offer you in the way of experience is that if you find you need to change the definition of a user defined table type, you will need to drop every reference to the type before you can diddle with it. Down in the Cons section mentioned that great annoyance. So for us, it was worth the code maintenance to define unique table types based on expected usage, even if it matched existing types. YMMV

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • 1
    +1 This is the same issue that has plagued alias types (the types formerly known as user-defined types until SQLCLR came along). In order to change the type "EmailAddress" from VARCHAR(255) to VARCHAR(320), you have to alter every single table where the type was used. Very quickly this cost outweighed the benefits of a consistent type, and we pulled out our rulers instead (to smack the hand of anyone who varied from the standard). :-) – Aaron Bertrand Aug 24 '11 at 21:57
  • I believe that the implementation of TVP's in Sql Server requires you to define a custom type for them, so using TVPs, it's not possible to avoid custom types altogether. – Adam Ness Aug 25 '11 at 18:42
  • @Adam right, my comment was just confirming that TVPs (which require `CREATE TYPE ... AS TABLE`) suffer similar limitations as alias types (`CREATE TYPE ... FROM` - formerly `sp_addtype`). – Aaron Bertrand Aug 25 '11 at 18:48
1

Why not just use an inline table-valued function that splits the list and returns a table? If you need to use it multiple times you can just call it once and stuff the results into a local table variable. To me TVP is most beneficial when you need to get complex arrays from client applications into the database - I'm not sure if it's worth the trouble for a single column containing IDs.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • So you're suggesting sending a varchar parameter containing a list of IDs in comma separated (or other structured) format, and then parsing it using an inline function? I haven't run the numbers yet, but my gut instinct is that will work reasonably well for short lists, but large lists will take a long time to parse. – Adam Ness Aug 25 '11 at 18:40
  • That depends on the function. I did some pretty extensive performance comparisons and while some methods flew off the charts like you suggest others remained relatively static even up to 50,000 elements. https://sqlblog.org/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx – Aaron Bertrand Aug 25 '11 at 18:43