資料表值參數(Table Value Parameters)簡介與實作
主題： |
資料表值參數(Table Value Parameters)簡介與實作 |
文章簡介： |
透過簡單的範例，利用資料表值參數，來大批匯入資料至資料庫。 |
作者： |
蔡雅勤 |
版本/產出日期： |
V1.0/2016.12.21 |
1. 前言
• 偶爾會遇到大批匯入新增或更新的功能，當資料量大的時候，使用傳統的方式一筆筆新增，很耗費效能，且會對SQL Server造成一定程度的效能影響。這時就可利用資料表值當作參數紀錄要異動的資料，傳給SQL的預存程序做一次性的執行，可大大提高執行效能。
2. 目的
• 了解如何利用資料表值參數(Table Value Parameters) 進行大批匯入資料至資料表。
3. 開始前準備
本範例建立於以下版本的環境：
● Visual Studio 2013
● Microsoft SQL Server 2014
4. TVP概述
4.1 資料表值參數(Table Value Parameters) 簡稱TVP，SQL Server 2008中導入的新功能，讓使用者透過單一參數化SQL語法，就能向SQL Server傳送大量資料。
4.2 資料表值參數提供更大的彈性，而且在某些情況下，其效能優於暫存資料表或是傳遞參數清單的其他方法。資料表值參數提供下列好處：
● 不需要從用戶端鎖定初始擴展的資料。
● 提供簡單的程式設計模型。
● 可讓將複雜的商務邏輯併入單一常式內。
● 減少與伺服器之間的往返次數。
● 可以有一個不同基數的資料表結構。
● 具有強型別。
● 可讓用戶端指定排序次序和唯一索引鍵。
4.3 資料表值參數有下列限制：
●SQL Server 不會維護資料表值參數之資料行上的統計資料。
●資料表值參數必須當做輸入 READONLY 參數傳遞給 Transact-SQL 常式。
●不能使用資料表值參數當做 SELECT INTO 或 INSERT EXEC 陳述式的目標。
5. 範例實作
5.1 建立TVP TYPE，作為SP參數：
CREATE TYPE [dbo].[TVP_MVCSG01] AS TABLE(
[EMPID] [int] NOT NULL,
[EMPNAME] [varchar](50) NOT NULL,
[MARKS] [varchar](10) NOT NULL,
[GRADE] [varchar](10) NOT NULL
)
GO
5.2 建立SP
● TVP要設定為READONLY
5.3 建立Excel資料放到Data Table 相關程式
● DataTable 欄位順序及型別要與TVP一致
5.4 建立執行SP 程式
5.5 範例DEMO
● 準備EXCEL檔案格式如下
● 瀏覽檔案後按匯入按鈕
● 查詢結果，成功匯入300筆資料
5.6 開啟SQL Server Profiler 來監看
● 利用TVP 參數的範例，與資料庫伺服器往返僅一次，且執行一秒鐘內就結束。
● 使用傳統方式執行，與資料庫伺服器往返300次，且執行超過10秒鐘。
6. 參考來源
• MSDN資料表值參數 －https://msdn.microsoft.com/zh-TW/Library/bb510489(SQL.100).aspx
• 利用資料表值參數(Table Valued)及預存程序來提升批次更新資料的速度－
