2

I am using table-valued parameters stored procedure in SQL Server 2008/2012. In the past I had instance problems passing 50k rows (instance stop responding, memory issues) so now I limit the number of rows in tvp to 5-10k .

Is there a limit for the number of row to pass to table-valued parameters stored procedure in SQL Server 2012/2016? Is there a way to use tvp stored procedures passing >=100k in a safe way? Are there better way to work around this problem?

Thank you

Prabhat Sinha
  • 1,500
  • 20
  • 32
giacomo23
  • 21
  • 4
  • The question is: Pass in which direction? As input or Output? – FDavidov Jan 02 '17 at 11:00
  • I pass rows to the tvp, as input. – giacomo23 Jan 02 '17 at 11:11
  • I see... Have no idea what the limit would be. If it was for OUTPUT, you could manage any size being returned as a results-set, which could return quite large number of records. Sorry I can't help. – FDavidov Jan 02 '17 at 11:17
  • Switch to using temporary tables. These have statistics associated with them. You could try adding [query hint](https://msdn.microsoft.com/en-us/library/ms181714.aspx) `OPTION(RECOMPILE)` to queries using the table parameters. If that doesn't work, create and fill a temporary table before calling the SP, then use that temporary table in the SP. – TT. Jan 02 '17 at 12:32

0 Answers0