What are the downsides to Table Valued Parameters in Stored Proc?

Question

I have worked with various versions of MS SQL Server including 2000,2005,2008,R2,(Some)Denali. I have never been so excited about a new feature like the Table Valued parameters in stored proc. I do C# development as well and I'm digging TVP, I use it to minimize the number of database calls from my front end app.

Now my real question is what are the downsides to using Table Valued Parameters. Its almost too good to be true.

Please share your thoughts. Plus I dont want to get too deep into using it and have to change.

Answer 1

Table Value Parameters have few practical drawbacks.

Benefits

• Cached upon frequent use
• Facilitate bulk inserts very efficiently
• Reduce round trips to the server

Drawbacks:

• SQL Server does not maintain statistics on the TVP Columns
• Readonly
• Can not be used as the target of "Select Into" or "Insert Exec" statements
• Only available on Sql Server 2008 and above

References

• Table Value Parameters