locked
Table Parameter of Store Procedure Limitation RRS feed

  • Question

  • I am working on a system to matching data. The group data need match is vary from few to several hundred thousands rows. Since the id/matchid of records are identity column. I am thinking to passing matched records with id issued in application server as table parameter into store procedures. Inside store procedure, convert data with real identify value in id/matchid and save into table.

    My question is , what is maximum number of rows(or size) I can pass into store procedure with table parameter? Or there have a better way to do this.

    Thanks,

    Tuesday, May 31, 2011 2:31 PM

Answers

  • I have passed 50000 rows to a TVP in performance tests with very good results.

    I think it sounds like a good solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Adam Haines Tuesday, May 31, 2011 3:06 PM
    • Marked as answer by Kalman Toth Saturday, June 11, 2011 12:02 AM
    Tuesday, May 31, 2011 2:51 PM
  • Just run the test and able to pass 10M records without issue.
    • Marked as answer by Kalman Toth Saturday, June 11, 2011 12:02 AM
    Thursday, June 2, 2011 9:58 PM

All replies

  • I have passed 50000 rows to a TVP in performance tests with very good results.

    I think it sounds like a good solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Adam Haines Tuesday, May 31, 2011 3:06 PM
    • Marked as answer by Kalman Toth Saturday, June 11, 2011 12:02 AM
    Tuesday, May 31, 2011 2:51 PM
  • Just run the test and able to pass 10M records without issue.
    • Marked as answer by Kalman Toth Saturday, June 11, 2011 12:02 AM
    Thursday, June 2, 2011 9:58 PM