sql server 2008 的 table value parameter

今天試了 sql server 2008 的 table value parameter，似乎還不錯，省下了不少的功夫
首先要在資料庫寫如下的 script

use tempdb;
create type t_tvptable as table (i int)
create table t ( a int)
go
create proc usp_tvpinsert(@p t_tvptable readonly) as
insert t
select * from @p
go

然後，寫一個 Console application (with VS2008)

using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      SqlConnection c = new SqlConnection("data source=sql2008rc0;user id=sa; password=pwd;database=tempdb");
      SqlCommand cmd = c.CreateCommand();

      cmd.CommandText = "usp_tvpinsert";
      cmd.CommandType = System.Data.CommandType.StoredProcedure;

      SqlParameter p = cmd.Parameters.Add("p", System.Data.SqlDbType.Structured);
      Stopwatch sw = new Stopwatch();
      sw.Start();
      DataTable t = new DataTable("temp");
      t.Columns.Add("a", typeof(int));
      for (int i = 0; i < 100000; i++) t.Rows.Add(i);

      p.Value = t;
      sw.Stop();
      System.Console.WriteLine(sw.ElapsedMilliseconds.ToString());

      sw.Start();
      c.Open();
      cmd.ExecuteNonQuery();
      c.Close();
      sw.Stop();
      System.Console.WriteLine(sw.ElapsedMilliseconds.ToString());
    }
  }
}

這樣一來，一次就可以新增多筆資料了，而且是以 datatable 來傳入參數的。
以 SQL Server profiler 錄出來的結果如下

declare @p1 dbo.t_tvptable
insert into @p1 values(0)
insert into @p1 values(1)
insert into @p1 values(2)
insert into @p1 values(3)
insert into @p1 values(4)
.....
insert into @p1 values(99998)
insert into @p1 values(99999)
exec usp_tvpinsert @p=@p1

而且，效能當然超好了。因為此段 sql 並而由 client side 產生的，因此 sql server 不必花時間 parse 且為此 script 製作 execution plan。

Code download