詳細瞭解SQL Server 2008效能和效能優化

iSQlServer發表於2008-12-18
SQLServer優化

在SQL Server 2005或更早的版本中的資料庫中，表變數是不能作為儲存過程的引數的。當多行資料到SQL Server需要傳送多行資料到SQL Server ，開發者要麼每次傳送一列記錄，或想出其他的變通方法，以滿足需求。雖然在.net 2.0中提供了個SQLBulkCopy物件能夠將多個資料行一次性傳送給SQL Server，但是多行資料仍然無法一次性傳給儲存過程。

SQL Server 2008中的T-SQL功能新增了表值引數。利用這個新增特性，我們可以很方便地通過T-SQL語句，或者通過一個應用程式，將一個表作為引數傳給儲存過程。

1、使用者自定義表型別

當第一次看看新的表值引數，我認為使用此功能有點複雜。有幾個步驟。要做的第一件事是定義表型。在Management Studio 2008中的“Programmability”“Type”節點，您可以看到“User-Defined Table Types(使用者自定義表型別)”，如圖1所示 。

SQLServer2008資料庫中如何使用表值引數

圖 1：使用者自定義表型別

點選右鍵，在彈出選單中選擇“新使用者定義的表型... ” ，會新建一個模板中的查詢視窗，如圖2所示 。

SQLServer2008資料庫中如何使用表值引數

圖2：使用者自定義表型別建立語句

點選“Specify Values for Template Parameters（指定值為模板引數）”按鈕，將探出一個對話方塊，如圖3所示。

SQLServer2008資料庫中如何使用表值引數

圖 3：指定模板引數列的數值

在填寫在適當的數值之後，點選確定按鈕，一個“CREATE TYPE”的宣告取代了範本。這時，你也可以手動增加一些列，或者增加一些限制條件，最後點選確定按鈕。

以下是最終的程式碼： 

-- ================================
-- Create User-defined Table Type
-- ================================
USE Test
GO

-- Create the data type
CREATE TYPE dbo.MyType AS TABLE 
(
	col1 int NOT NULL, 
	col2 varchar(20) NULL, 
	col3 datetime NULL, 
    PRIMARY KEY (col1)
)
GO


 

在執行程式碼之後，物件的定義就建立好了，你可以在“User-Defined Table Type（使用者自定義表型別”中檢視屬性，如圖4所示，但沒法修改它們。如果要修改的型別，你只能將其刪除，然後按照修改後的屬性再次建立它。

SQLServer2008資料庫中如何使用表值引數

圖4：檢視使用者自定義表型別的屬性

2、使用使用者自定義的表型別

如果打算在T-SQL程式碼中使用，您還必須建立一個新型別的變數，然後將具體的表的名稱賦值給該變數。一旦賦值後，您可以在其他的T-SQL語句中使用它。因為它是一個變數，在批處理完成後，它也自動失效，結束生命週期。

請注意下面的程式碼，MyType是我們之前剛剛建立的資料型別。 

DECLARE @MyTable MyType

INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
	(2,'def','1/1/2001'),
	(3,'ghi','1/1/2002'),
	(4,'jkl','1/1/2003'),
	(5,'mno','1/1/2004')
	
SELECT * FROM @MyTable


 

在變數的有效範圍內，你可以象操作正常的表一樣來操作這個變數，如與另一個表象關聯或者將變數中的記錄填充到另一個表。對於表變數來說，你無法修改表定義。

正如前面提到的，變數不能超出它的有效的範圍。如果T-SQL指令碼由多個批處理組成，變數只有在批處理內才能建立並有效使用。

3、使用變數作為引數

到目前為止，我們還沒有看到經常表變數無法實現的功能。其好處是能夠將變數作為引數傳給儲存過程。當然一個儲存過程必須先建立，使用新的型別作為其中的一個引數。

下面這個例子，通過程式碼建立一個常規表，並對其填充記錄。 

USE [Test]
GO

CREATE TABLE [dbo].[MyTable] (
	[col1] [int] NOT NULL PRIMARY KEY,
	[col2] [varchar](20) NULL,
	[col3] [datetime] NULL,
	[UserID] [varchar] (20) NOT NULL
	)	

GO

CREATE PROC usp_AddRowsToMyTable @MyTableParam MyType READONLY,
	@UserID varchar(20) AS
	
	INSERT INTO MyTable([col1],[col2],[col3],[UserID])
	SELECT [col1],[col2],[col3],@UserID	
	FROM @MyTableParam
	
GO


 

請注意表值引數後面帶了個READONLY引數。這是必需的，不能在例程體中對錶值引數執行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。

最後，我們對建立表值變數，對變數進行賦值，並呼叫儲存過程。 

DECLARE @MyTable MyType

INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
	(2,'def','1/1/2001'),
	(3,'ghi','1/1/2002'),
	(4,'jkl','1/1/2003'),
	(5,'mno','1/1/2004')

EXEC usp_AddRowsToMyTable @MyTableParam = @MyTable, @UserID = 'Kathi'

SELECT * FROM MyTable


 

為了讓使用者使用自定義表型別，執行或控制許可權必須是理所當然的。以下是授權命令： 

GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser;


 

4、通過.net應用程式呼叫

表值引數這一特性最大的亮點在於可以在.net應用中使用表值引數。為了做到這一點，你必須要先安裝.NET 3.5框架，並確保應用程式中已經引用了 System.Data.SqlClient名稱空間。建立表值引數時需要用到一些新的SQL資料型別（如DataTable、DataColumn等）。

首先建立一個本地資料表，並插入一些記錄。肯定的是， DataTable中建立符合使用者定義的表型的列計數和資料型別。 

'Create a local table
Dim table As New DataTable("temp")
Dim col1 As New DataColumn("col1", System.Type.GetType("System.Int32"))
Dim col2 As New DataColumn("col2", System.Type.GetType("System.String"))
Dim col3 As New DataColumn("col3", System.Type.GetType("System.DateTime"))
table.Columns.Add(col1)
table.Columns.Add(col2)
table.Columns.Add(col3)
        
'Populate the table
For i As Integer = 20 To 30
    Dim vals(2) As Object
    vals(0) = i
    vals(1) = Chr(i + 90)
    vals(2) = System.DateTime.Now
    table.Rows.Add(vals)
Next


 

我們在程式碼中採用儲存過程：建立一個命令物件，並新增兩個引數。程式碼如下圖所示： 

'Create a command object that calls the stored proc
Dim command As New SqlCommand("usp_AddRowsToMyTable", conn)
command.CommandType = CommandType.StoredProcedure

'Create a parameter using the new type
Dim param As SqlParameter = command.Parameters.Add
("@MyTableParam", SqlDbType.Structured)
command.Parameters.AddWithValue("@UserID", "Kathi")


 

請注意@ MyTableParam引數的資料型別(SqlDbType.Structured)，這是.Net 3.5中新增的功能。最後，將當地表賦值給表值引數，並執行該命令。 

'Set the value of the parameter
param.Value = table

'Execute the query
command.ExecuteNonQuery()


 

5、小結

SQL Server 2008中新增的表值引數特性，減少了應用程式與SQL Server資料庫伺服器之間的互動，提升了程式效能。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-515940/，如需轉載，請註明出處，否則將追究法律責任。

相關文章