The new row constructor syntax of SQL Server 2008 to insert several rows is a quiet neat feature, unfortunately the performance is quiet bad. I really thought this would be a great performance optimization and a replacement for the old UNION ALL concatenated rows to add all rows in one bulk. In addition I really don’t understand the restriction to 1000 rows.
If this becomes optimized in future versions this might be a optimization (for time duration and network traffic) for client libraries like ADO.NET. Currently - including .NET 4.0 - ADO creates single insert statements to send a table-valued parameter from client to server.
My tests showed me that single INSERT statements are currently more than ten times (!) faster than using the row constructor. Due to this I don’t really understand the reason for the introduction of the row constructor, since it was already available by using UNION ALL.