5

Is there an upper limit to the number of rows\total size of the SqlDbType.Structured type when sending data to a Table parameter in a sproc?

I can't find anything to suggest there is so I'm assuming that there'd just be a time-out if a giant data set was sent.

I'm looking at 2000-ish rows, so nothing frightening. I just want to preempt any potential explosions.

4

There is no inherent limit to the size of a table-valued parameter. However, you could run into client memory constraints if you pass the value without streaming, such as using a DataTable or IEnumerable<SqlDataRecord> (without a stream source implementation) instead of a DataReader.

On the server side, the limit is tempdb storage.

2K rows won't be a problem as I've passed millions of rows without issues. One consideration, though, is to be sure to specify the MaxLength for string columns with a DataTable value. See my blog post SQL Server TVP Performance Gotchas.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.