0

I was trying to search it everywhere but could not find what is the limit of the data that I can pass in user defined table types to stored procedure.

Also, is it a good practice to pass data like this in stored procedure? Specially if we want to pass some good amount of data.

Pawan Nogariya
  • 8,330
  • 12
  • 52
  • 105
  • 2
    There isn't an upper limit, though if you are inserting millions of rows into a table type then you are very likely to get poor performance, as SQL Server makes certain assumptions about row counts with table type variables/parameters, and you'll be better off using a physical table. – Thom A Sep 15 '20 at 13:41
  • @Larnu - Okay, so it will just be performance issues but it will still work, right? And for thousands of rows it should work fine? – Pawan Nogariya Sep 15 '20 at 13:48
  • A 1,000 or so rows will likely be fine (best way it to test), but it's best to test. Like i mentioned SQL Server makes assumptions on how many rows; it used to assume there was only a **single** row in them but I recall that newer versions assume higher than that (I can't recall is it's 100 or 1,000). – Thom A Sep 15 '20 at 13:52

0 Answers0