在SQL Server 2005或更早的版本中的中，表變量是不能作為存儲過程的參數(shù)的。當(dāng)多行數(shù)據(jù)到SQL Server需要發(fā)送多行數(shù)據(jù)到SQL Server ，開發(fā)者要么每次發(fā)送一列記錄，或想出其他的變通方法，以滿足需求。雖然在.net 2.0中提供了個(gè)SQLBulkCopy對象能夠?qū)⒍鄠€(gè)數(shù)據(jù)行一次性傳送給SQL Server，但是多行數(shù)據(jù)仍然無法一次性傳給存儲過程。

SQL Server 2008中的T-SQL功能新增了表值參數(shù)。利用這個(gè)新增特性，我們可以很方便地通過T-SQL語句，或者通過一個(gè)應(yīng)用，將一個(gè)表作為參數(shù)傳給存儲過程。

1、用戶自定義表類型

當(dāng)?shù)谝淮慰纯葱碌谋碇祬?shù)，我認(rèn)為使用此功能有點(diǎn)復(fù)雜。有幾個(gè)步驟。要做的第一件事是定義表型。在Management Studio 2008中的“Programmability”?“Type”節(jié)點(diǎn)，您可以看到“User-Defined Table Types(用戶自定義表類型)”，如圖1所示 。

圖 1：用戶自定義表類型

點(diǎn)擊右鍵，在彈出菜單中選擇“新用戶定義的表型... ” ，會(huì)新建一個(gè)模板中的查詢窗口，如圖2所示 。

圖2：用戶自定義表類型創(chuàng)建語句

點(diǎn)擊“Specify Values for Template Parameters(指定值為模板參數(shù))”按鈕，將探出一個(gè)對話框，如圖3所示。

圖 3：指定模板參數(shù)列的數(shù)值

在填寫在適當(dāng)?shù)臄?shù)值之后，點(diǎn)擊確定按鈕，一個(gè)“CREATE TYPE”的聲明取代了范本。這時(shí)，你也可以手動(dòng)增加一些列，或者增加一些限制條件，最后點(diǎn)擊確定按鈕。

以下是最終的代碼：

-- ================================

-- 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

在運(yùn)行代碼之后，對象的定義就建立好了，你可以在“User-Defined Table Type(用戶自定義表類型”中查看屬性，如圖4所示，但沒法修改它們。如果要修改的類型，你只能將其刪除，然后按照修改后的屬性再次創(chuàng)建它。

圖4：查看用戶自定義表類型的屬性

2、使用用戶自定義的表類型

如果打算在T-SQL代碼中使用，您還必須創(chuàng)建一個(gè)新類型的變量，然后將具體的表的名稱賦值給該變量。一旦賦值后，您可以在其他的T-SQL語句中使用它。因?yàn)樗且粋€(gè)變量，在批處理完成后，它也自動(dòng)失效，結(jié)束生命周期。

請注意下面的代碼，MyType是我們之前剛剛創(chuàng)建的數(shù)據(jù)類型。

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

在變量的有效范圍內(nèi)，你可以象操作正常的表一樣來操作這個(gè)變量，如與另一個(gè)表象關(guān)聯(lián)或者將變量中的記錄填充到另一個(gè)表。對于表變量來說，你無法修改表定義。

正如前面提到的，變量不能超出它的有效的范圍。如果T-SQL腳本由多個(gè)批處理組成，變量只有在批處理內(nèi)才能創(chuàng)建并有效使用。

3、使用變量作為參數(shù)

到目前為止，我們還沒有看到經(jīng)常表變量無法實(shí)現(xiàn)的功能。其好處是能夠?qū)⒆兞孔鳛閰?shù)傳給存儲過程。當(dāng)然一個(gè)存儲過程必須先建立，使用新的類型作為其中的一個(gè)參數(shù)。

下面這個(gè)例子，通過代碼創(chuàng)建一個(gè)常規(guī)表，并對其填充記錄。

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

請注意表值參數(shù)后面帶了個(gè)READONLY參數(shù)。這是必需的，不能在例程體中對表值參數(shù)執(zhí)行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。

最后，我們對創(chuàng)建表值變量，對變量進(jìn)行賦值，并調(diào)用存儲過程。

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

為了讓用戶使用自定義表類型，執(zhí)行或控制權(quán)限必須是理所當(dāng)然的。以下是授權(quán)命令：

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

4、通過.net應(yīng)用程序調(diào)用

表值參數(shù)這一特性最大的亮點(diǎn)在于可以在.net應(yīng)用中使用表值參數(shù)。為了做到這一點(diǎn)，你必須要先安裝.NET 3.5框架，并確保應(yīng)用程序中已經(jīng)引用了 System.Data.SqlClient命名空間。創(chuàng)建表值參數(shù)時(shí)需要用到一些新的SQL數(shù)據(jù)類型(如DataTable、DataColumn等)。

首先創(chuàng)建一個(gè)本地?cái)?shù)據(jù)表，并插入一些記錄。肯定的是， DataTable中創(chuàng)建符合用戶定義的表型的列計(jì)數(shù)和數(shù)據(jù)類型。

'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

我們在代碼中采用存儲過程：創(chuàng)建一個(gè)命令對象，并新增兩個(gè)參數(shù)。代碼如下圖所示：

'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參數(shù)的數(shù)據(jù)類型(SqlDbType.Structured)，這是.Net 3.5中新增的功能。最后，將當(dāng)?shù)乇碣x值給表值參數(shù)，并執(zhí)行該命令。

'Set the value of the parameter

param.Value = table

'Execute the query

command.ExecuteNonQuery()

5、小結(jié)

SQL Server 2008中新增的表值參數(shù)特性，減少了應(yīng)用程序與SQL Server服務(wù)器之間的交互，提升了程序性能。

總結(jié)

以上是生活随笔為你收集整理的sql server linux性能,详细了解SQL Server 2008性能和性能优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。