0

I have used table valued parameter to do so. But I am not aware of the restrictions (if any) of SQL Server on the size of DataTable that we are passing.

Additionally I need to know if there are hidden cons of doing this by TVP method and any other better method of doing the same.

Note: the number 5 million is not the constant here, it is subjected to change every time.

SQL Server table definition:

(
    ID bigint;
    Size float;
)
Preben Huybrechts
  • 5,853
  • 2
  • 27
  • 63
Sangram Patil
  • 98
  • 1
  • 8
  • You can start [here](https://www.brentozar.com/blitzcache/table-variables/) or [here](https://www.brentozar.com/archive/2014/02/using-sql-servers-table-valued-parameters/) and read through the links. In general table valued parameters are table variables, and they **can** screw up your execution plans. Since they don't have statistics. Coping to a temp table could improve performance but it depends. – Preben Huybrechts Jul 09 '20 at 06:27
  • You could also consider `OPTION(RECOMPILE)` on the queries that use the table valued parameter. – Preben Huybrechts Jul 09 '20 at 06:37
  • Thanks @PrebenHuybrechts, the information is valuable, but it takes me far far away from my scenario to the corner cases of TVP's. My question is straight forward: I just want to pass 5+M id's from C# and willing to get single value from sql. What will be the best approach to achieve that. – Sangram Patil Jul 09 '20 at 08:08
  • [SQL Bulk Copy](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=dotnet-plat-ext-3.1) allows you to insert huge amounts of data into sql server, you can insert to table variable or temp table as well. – Preben Huybrechts Jul 09 '20 at 08:21

0 Answers0