Creating a generic type to use table value as a parameter

Question

I found that there are several points in the stored procedures that I create where I want the stored procedure to accept a list of identifiers in different tables that the SP must execute. I am considering using table options. I am wondering if it is correct to create a single type that contains only one column "ID int" and use it as a data type for all my TVPs or is it better to determine the type for each SP in case I ultimately want to expand the transferred values.

Does anyone have any experience?

to change . If this is relevant to your recommendation, the list of identifiers that I pass can be around 150,000 entries.

Answer 1

What I can offer you in practice is that if you find that you need to change the definition of the type of table defined by the user, you will need to drop all references to the type before you can handle it. The Cons section mentioned that this is very unpleasant. Therefore, it was advisable for us that the code service determine the unique types of tables based on the expected use, even if it matches existing types. Ymmv

Answer 2

Why not just use the built-in function of the table function, which splits the list and returns the table? If you need to use it several times, you can simply call it once and add the results to a local table variable. TVP is very useful for me when you need to get complex arrays from client applications to the database - I'm not sure if it is worth standing for one column containing identifiers.