SQL Server 2008 表值類型參數
目錄
准備工作1
練習：處理表值類型參數2
准備工作
預計完成本實驗所需的時間
40 分鐘
目標
在完成本實驗後，您將可以：
處理SQL Server 2008當中的表值類型參數。
先決條件
在完成本實驗前，您必須具有：
編寫Transact-SQL 腳本與使用SQL Server Management Studio的相關經驗。
實驗場景
在許多客戶場景中，都需要將一個表結構的值（一些數據行）傳遞到服務器中的一個存儲過程/函數當中。這些值可以用來直接更新一個數據表，或根據業務邏輯進行更為復雜的處理操作。表值類型參數提供了一個更為簡單的方式來定義一個數據類型，並且允許應用程序創建、更新並向存儲過程和函數中傳遞表值類型的參數。
隨著在SQL Server 2008中引入的MERGE語句，開發人員可以更為高效的處理常見的數據倉庫場景，如檢查一個數據行是否存在，然後進行插入或更新。
虛擬機環境
從開始菜單或桌面上啟用Microsoft Virtual PC 。如果Virtual PC 控制台沒有啟用，請查看系統托盤，然後雙擊系統托盤當中的Microsoft Virtual PC 。
選擇Sql08 然後點擊Start。
在虛擬機運行起來後，可以通過點擊右Alt+Del 來向虛擬機發送一個Ctrl+Alt+Del 命令。
在登錄窗口中，輸入以下信息：
User name: administrator
Password: passWord01!
練習：處理表值類型參數
表值類型參數是SQL Server 2008當中的一個新的數據類型。表值類型參數可以使用一些用戶定義的表類型來聲明。您可以使用表值類型參數向一個T－SQL語句、存儲過程或函數中發送多行數據，並且無需創建臨時表或創建多個參數。
表值類型參數就像在OLE DB和ODBC中的參數數組一樣，但是它可以提供更多的靈活性，並與T－SQL更加集成。表值類型參數也可以更加方便的進行基於集合的操作。
在本練習中，您的目標是使用單個存儲過程，使用多個數據行插入一整個數據集合。
在SQL Server 2008以前，沒有任何集成的功能，可以支持調用存儲過程來完成類似的目標。
您將實現一個存儲過程，並使用表值類型參數完成這個功能。
啟動 SQL Server Management Studio
點擊Start | All Programs | Microsoft SQL Server 2008 | SQL Management Studio ，啟動SQL Server Management Studio。
在Connect to Server對話框中輸入下列信息，然後點擊 Connect 按鈕：
Server type: Database Engine
Server name: (local)
Authentication: Windows Authentication
點擊File | Open | File。
打開C:SQLHOLS 文件夾，然後打開Table Valued Parameters目錄當中的Labscript.sql 腳本文件。
使用存儲過程中的多個參數插入數據
查看並選中 下列代碼並點擊Execute：
USE SQL2008DEMO
GO
CREATE TABLE dbo.Employee(
EmpID int NOT NULL,
EmpName nvarchar(100) NOT NULL,
EmpEmail nvarchar(100) NOT NULL)
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
GO
CREATE PROCEDURE NewEmployeeMS(@EmpID int,@EmpName nvarchar(100),@EmpEmail nvarchar(100))
As
BEGIN
INSERT INTO dbo.Employee
values(
@EmpID, @EmpName, @EmpEmail)
END
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
GO
execute NewEmployeeMS 1,'John McLean','[email protected]'
execute NewEmployeeMS 2,'Bob Smith','[email protected]'
execute NewEmployeeMS 3,'Ted Connery','[email protected]'
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
select * from dbo.Employee;
GO
注意: 以上解決方案的缺點在於：
1.使用了太多的與服務器的往返行程
2.存儲過程需要多次重復執行
3.低效的代碼執行
使用本地臨時表插入數據
查看並選中 下列代碼並點擊Execute：
USE SQL2008DEMO
GO
Truncate table dbo.Employee
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
GO
CREATE PROCEDURE NewEmployeeTempTable
As
BEGIN
INSERT INTO dbo.Employee
SELECT * FROM #EmployeeTempTable
ENDINSERT INTO dbo.Employee
values(
@EmpID, @EmpName, @EmpEmail)
END
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
GO
CREATE TABLE dbo.#EmployeeTempTable(
EmpID int NOT NULL,
EmpName nvarchar(100) NOT NULL,
EmpEmail nvarchar(100) NOT NULL)
Go
注意: 這種臨時表是在運行在客戶端進行創建，這將會導致在客戶端沒有定義的情況下，服務器端的存儲過程將會執行失敗。
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
INSERT INTO #EmployeeTempTable
VALUES(1,'John McLean','[email protected]')
INSERT INTO #EmployeeTempTable
VALUES(2,'Bob Smith','[email protected]')
INSERT INTO #EmployeeTempTable
VALUES(3,'Ted Connery','[email protected]')
注意: 插入的行在客戶端的臨時表中產生。
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
GO
exec dbo.NewEmployeeTempTable
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
GO
SELECT * FROM dbo.Employee
GO
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
GO
Drop table dbo.#EmployeeTempTable
GO
注意:
1.臨時表是在磁盤上創建並操作的，這將會導致大量的I/O操作。
2.它們在tempdb數據庫中進行創建，並非常會導致鎖定與阻礙問題。
3.在使用完成後，您必需手動清理數據，並刪除臨時表。
4.臨時表的使用將會導致頻繁的存儲過程的重新編譯。
使用表值類型參數插入數據
查看並選中 下列代碼並點擊Execute：
USE SQL2008DEMO
GO
Truncate table dbo.Employee
查看並選中 下列代碼並點擊Execute：
USE SQL2008DEMO
GO
CREATE TYPE EmployeeTableType AS TABLE
(EmpID INT, EmpName nvarchar(100), EmpEmail nvarchar(100))
注意: 在這裡需要創建一個表類型來處理表值類型參數。
查看並選中 下列代碼並點擊Execute：
USE SQL2008DEMO
GO
CREATE PROCEDURE NewEmployee(@EmployeeDetails EmployeeTableType READONLY)
As
BEGIN
INSERT INTO dbo.Employee
SELECT * FROM @EmployeeDetails
END
注意: 表值類型參數必須作為輸入類型的只讀（READONLY）參數傳遞到過程當中。在過程體當中，您不能在表值類型參數上執行DML操作，如UPDATE, DELETE, 或INSERT操作。
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
GO
DECLARE @NewEmployees EmployeeTableType
INSERT INTO @NewEmployees
VALUES(1,'John McLean','[email protected]')
INSERT INTO @NewEmployees
VALUES(2,'Bob Smith','[email protected]')
INSERT INTO @NewEmployees
VALUES(3,'Ted Connery','[email protected]')
EXECUTE NewEmployee @NewEmployees
Go
注意: 在過程操作完成後，表值類型參數將不會再存在。
查看並選中 下列代碼並點擊Execute：
USE SQL2008Demo
GO
select * from dbo.Employee
注意: 使用表值類型參數的優勢
表值類型參數提供了更好的靈活性，並且在許多情況下，可以提供比臨時表或其它傳遞列表類型類型的方式更好的性能。表值類型參數提供了下列一些優勢：
擁有一個良好定義的范圍，在這個執行范圍結束後，它將會自動清除。
不需要在客戶端鎖定正在處理的數據。
不會導致語句的重新編譯。
提供了一個非常簡單的編程模型。
可以讓您在一個單一的過程當中引入復雜的業務邏輯。
減少與服務器的返回行程。
可以擁有不同粒度的表結構。
是一個強類型的類型定義。
允許客戶端指定排序順序和唯一鍵。
關閉所有應用程序並不要保存所有更改。
關閉Virtual PC 並不要保存更改。
