Parameter passing Vs Table Valued Parameters Vs XML to SQL 2008 from .Net Application

The Table-Valued Parameter approach will most likely be the best approach, since you can update a whole batch of rows at once; after all, you get a table which you can join against easily.

The other approaches both are either row-by-row which is inherently slower, or require a fair bit of mucking on the SQL Server side of thing; this is not only not really fun, usually, but also more error-prone and typically less performant than just simply joining two tables.

This is exactly the scenario the TVP have been introduced for - to solve that "row-by-row" or "messing-around-with-XML" problem. I would believe there's a good reason for Microsoft to introduce this, and if they do, you should definitely give it a good try and see if it works.

But again: that's just a "gut feeling" without really knowing all your details. Only you can really find this out, for yourself, by testing all three options. There are a plethora of other effects and parameters that might come into play that anyone answering can't possibly know....

If you use TVP's you can use the merge statement to manage insert/update/delete.(Cool new feature of SQL2008).

The only limitation of the TVP that I've seen is the 1000 row limit. As were this isn't any issue with xml or doing row by row.

I implemented a row by row and TVP solutions for a data import I was doing (thousands of rows). TVP's won hands down (minutes down to seconds). So I would suggest do both - it will also give you metrics for telling your boss how cool you are, by making stuff run faster!

Things you need to consider;What's the processor over head of converting for data from row by row, to TVP or XML. This is based on your data. (Going to involve more loops or serialization)

Check parallelism - I've seen examples of where table parameters will only run over one core!! - So in test the speed increase you see, might not be reflected in live.