2

If all I'm doing is inserting multiple rows of data into a single table in SQL Server 2008, which is faster?

A Table-Valued Parameter or a single insert statement with multiple values?

Where in this simple scenario would you prefer one over the other?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dugan
  • 908
  • 2
  • 13
  • 30
  • possible duplicate of [SQL Server: Table-valued Functions vs. Stored Procedures](http://stackoverflow.com/questions/4254814/sql-server-table-valued-functions-vs-stored-procedures) – OMG Ponies Nov 29 '10 at 22:02
  • http://stackoverflow.com/search?q=[sql-server]+table+valued+function+vs – OMG Ponies Nov 29 '10 at 22:02
  • I'm not actually talking about stored procs or Table-Valued Functions at all, but rather Table-Valued Parameters. Which could potentially be passed to a stored proc, or a function, or used in an ad-hoc query. – Dugan Nov 29 '10 at 22:10
  • What did you ever come up with here? – jcolebrand Jan 20 '11 at 06:06
  • I ended up going the Table-Valued parameter route, just for sanity's sake. I suppose I should run some speed tests to get a better answer – Dugan Jan 21 '11 at 19:25

2 Answers2

1

If I understand the question correctly, I'd go with the Table-Valued Parameter. Otherwise I'd think the list of parameters would quickly become unmanageable. You wouldn't want to end up with something like:

insert into YourTable
    (col1, col2, ..., colN)
    values
    (@Row1Col1, @Row1Col2, ..., @Row1ColN),
    (@Row2Col1, @Row2Col2, ..., @Row2ColN),
    ...
    (@RowMCol1, @RowMCol2, ..., @RowMColN)
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Well no, I wouldn't want to type it up, but it would be programmatically assembled anyways. I'm trying to figure out which one, if either, is actually more efficient. – Dugan Nov 29 '10 at 22:38
0

Since both operations will insert data into a table the question becomes; "Is the overhead of using a stored procedure to make table inserts to much for my system to handle?"

Does your system allow direct inserts into tables from the app now? If yes then just go with the direct insert.

I prefer to use stored procs as it allows me to add auditing, error logic, etc which just makes me feel better than dumping data directly into a table.

RC_Cleland
  • 2,274
  • 14
  • 16