the table valued parameter is good unless they cross 1000 rows but what if i increase columns.

rajesh yadav 211 Reputation points
2021-02-05T08:20:37.367+00:00

https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver15

in above link , i found
"table-valued parameters perform well for inserting less than 1000 rows."

the question is if i add more cols up to 60 to 100 will there be any problem, because artical does not say any thing
about columns.

and i feel that inserting more cols might do the damage as far as the performace is concerned.

Developer technologies | Transact-SQL
SQL Server | Other
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2021-02-05T23:07:42.657+00:00

    I am sure that I am guilty of having sent more than 1000 rows to a TVP.

    But I guess the gist of what they are saying is that the more rows you will be inserting at a time, BULK INSERT or the SqlBulkCopy class will be better choices for performance.

    0 comments No comments

  2. CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
    2021-02-12T09:38:49.14+00:00

    Hi @rajesh yadav ,

    Quote from the blog, the results are as follows, with the fastest time highlighted in green and the slowest time highlighted in red for each data volume. (Note that all times are in milliseconds.)

    67300-screenshot-2021-02-12-173742.jpg


    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.