共用方式為

  • 發行項

使用 sql_variant 資料

sql_variant 資料類型的操作類似於 Microsoft Visual Basic 中的 variant 資料類型。sql_variant 可讓單一資料行、參數或變數儲存不同資料類型的資料值。例如，一個 sql_variant 資料行可以保存 int、decimal、char、binary 和 nchar 值。sql_variant 資料行的每個例項都會記錄資料值和中繼資料資訊。這包括基底資料類型、大小上限、小數位數、有效位數和定序。

sql_variant 的使用規則

使用 sql_variant 資料類型，適用下列規則。

一般值指派

  • sql_variant 物件可以保存 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml、timestamp 和 Microsoft .NET Framework Common Language Runtime (CLR) 使用者自訂類型之外任何 SQL Server 資料類型的資料。sql_variant 資料的例項也不能用 sql_variant 作為它的基礎基底資料類型。

  • 參考 sql_variant 資料行的述詞或指派中可以指定任何類型的常數。

  • 如果 sql_variant 值是 NULL，就當作它沒有基礎的基底資料類型。即使這個 Null 值是來自特定資料類型的變數或資料行，一樣適用這個規則。

    在下列範例中，VariantCol 的值設成沒有相關聯資料類型的 NULL，雖然 Null 值是來自一個 int 變數：

    DECLARE @IntVar int

    SET @IntVar = NULL

    UPDATE SomeTable SET VariantCol = @IntVar WHERE PriKey = 123

  • 將 sql_variant 物件指派給任何其他資料類型的物件時，必須明確將 sql_variant 值轉換成目的地的資料類型。將 sql_variant 值指派給其他資料類型的物件時，不支援任何隱含的轉換。

  • 為了與其他資料類型相容，報告 sql_variant 物件長度之目錄物件 (例如 DATALENGTH 函數)，會報告資料的長度。但不傳回 sql_variant 物件中所包含之中繼資料的長度。

  • sql_variant 資料行永遠以 ANSI_PADDING ON 的方式運作。如果從 ANSI_PADDING OFF 的來源指派 char、nchar、varchar、nvarchar 或 varbinary 值，則不會填補值。

  • 在訂閱者端更新單一資料行會導致另一個 sql_variant 資料行的基底類型變更。下列程序將說明這個概念：

    1. 建立合併的發行集/訂閱。發行的資料表應該具有 sql_variant 資料行和 c1 資料行。將某些資料加入至 sql_variant 資料行。資料的基底類型就是 datetime。

    2. 初始同步處理之後，訂閱者端的基底類型仍然是 datetime。

    3. 在訂閱者端更新 c1 資料行。

    4. 發行者端 sql_variant 資料行的資料已經變更為 datetime2。

資料表中的 sql_variant

  • sql_variant 資料行可用於索引與唯一索引鍵，但是索引鍵資料行中的資料長度不能超過 900 個位元組。

  • sql_variant 資料行不支援 IDENTITY 屬性，但 sql_variant 資料行可以作為主索引鍵或外部索引鍵的一部份。

  • sql_variant 資料行不能用於計算資料行。

  • 請使用 ALTER TABLE，將 text、ntext、image、timestamp 或 sql_variant 之外的任何資料類型的資料行變更為 sql_variant。現有的值全都會轉換成 sql_variant 值，其基底資料類型與執行 ALTER TABLE 陳述式之前資料行的資料類型一樣。ALTER TABLE 無法用來將 sql_variant 資料行的資料類型改成任何其他資料類型，因為不支援從 sql_variant 到其他資料類型的隱含轉換。

定序

  • COLLATE 子句不能用來為 sql_variant 資料行指派資料行定序。sql_variant 資料行中一般字元類型的值 (char、nchar、varchar 和 nvarchar)，可以屬於任何定序，並且單一 sql_variant 資料行可以保存混合定序之一般字元類型的值。

  • 為 sql_variant 例項指派值時，會同時指派來源的資料值與基底資料類型。如果來源值有定序的話，也指派該定序。如果來源值屬於使用者自訂資料類型，就指派使用者自訂資料類型的基底資料類型，而非使用者自訂資料類型。sql_variant 例項不繼承與使用者自訂資料類型繫結的任何規則或預設值。如果將具有識別屬性的資料行值指派給 sql_variant 例項，sql_variant 會接收來源資料行的基底資料類型，但不繼承 IDENTITY 屬性。將 text、ntext 或 image 值指派給 sql_variant 例項是錯誤的。將其他資料類型的物件值指派給 sql_variant 物件時，可支援隱含的轉換。

sql_variant 比較

sql_variant 資料行可以包含多種基底資料類型與定序的值，所以在比較 sql_variant 運算元時採用特殊的規則。這些規則適用於涉及比較的運算，如：

  • Transact-SQL 比較運算子

  • ORDER BY、GROUP BY

  • 索引

  • MAX 與 MIN 彙總函式

  • UNION (沒有 ALL)

  • CASE 運算式

若要比較 sql_variant，SQL Server 資料類型階層順序會分組成資料類型家族。sql_variant 家族有最高的家族優先順序。

資料類型階層

資料類型家族

sql_variant

sql_variant

datetime

日期及時間

smalldatetime

日期及時間

Float

近似數值

Real

近似數值

decimal

精確數值

money

精確數值

smallmoney

精確數值

bigint

精確數值

int

精確數值

smallint

精確數值

tinyint

精確數值

bit

精確數值

nvarchar

Unicode

nchar

Unicode

varchar

Unicode

char

Unicode

varbinary

二進位編碼

binary

二進位編碼

uniqueidentifier

Uniqueidentifier

以下規則適用於 sql_variant 比較：

  • 比較不同基底資料類型的 sql_variant 值，而且基底資料類型屬於不同資料類型家族時，資料類型家族在階層圖中較高的值就是兩個值中的較大者。

  • 比較不同基底資料類型的 sql_variant 值，而且基底資料類型屬於同一個資料類型家族時，基底資料類型在階層圖中較低的值會隱含轉換成其他資料類型，然後再進行比較。

  • 比較 char、varchar、nchar 或 varchar 資料類型的 sql_variant 值時，將會根據下列準則來評估這些值：LCID、LCID 版本、比較旗標和排序識別碼。每一個準則都會以整數值的形式來比較，而且會根據所列的順序來比較。

這些規則在 sql_variant 值之間產生的比較結果，可能不同於相同基底資料類型的值之間的比較結果。

運算元 A

運算元 B

非 Variant 比較結果

sql_variant 比較結果

'123' char

111 int

A > B

B > A

50000 int

5E1 float

A > B

B > A

因為不同資料類型家族的值必須明確轉換後，才能在比較述詞中被參考，所以只有將結果集以 sql_variant 資料行排序時才會看出這些規則的影響。下表中的這些值就是有關資料類型優先順序規則的範例。

PriKey

VariantCol

1

50.0 (基底類型為 float)

2

5000 (基底類型為 int)

3

'124000' (基底類型為 char(6))

下表顯示陳述式 SELECT * FROM VariantTest ORDER BY VariantCol ASC 的結果。

PriKey

VariantCol

3

'124000' (基底類型為 char(6))

2

5000 (基底類型為 int)

1

50.0 (基底類型為 float)

下表中的值是有關使用不同定序之定序優先順序規則的範例。

IntKey

VariantCol

1

qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

2

abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

3

qrs (varchar SQL_Latin1_General_CP1_CS_AS)

4

17.5 (decimal)

5

abc (varchar SQL_Latin1_General_CP1_CS_AS)

6

klm (varchar SQL_Latin1_General_CP1_CS_AS)

7

1.2 (decimal)

下表顯示陳述式 SELECT * FROM CollateTest ORDER BY VariantCol 的結果。下表將來自精確數字資料類型家族的值群組在一起，並將 varchar 值依個別的定序加以分組。

IntKey

VariantCol

5

abc (varchar SQL_Latin1_General_CP1_CS_AS)

6

klm (varchar SQL_Latin1_General_CP1_CS_AS)

3

qrs (varchar SQL_Latin1_General_CP1_CS_AS)

2

abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

1

qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

7

1.2 (decimal)

4

17.5 (decimal)

函數與 sql_variant 資料

下列 Transact-SQL 函數支援 sql_variant 參數，並在指定 sql_variant 參數時傳回 sql_variant 值。

COALESCE

MIN

MAX

NULLIF

下列函數支援對 sql_variant 資料行或變數的參考，但不使用 sql_variant 作為傳回值的資料類型。

COL_LENGTH

DATALENGTH

TYPEPROPERTY

COLUMNPROPERTY

ISNULL

 

下列 Transact-SQL 函數不支援 sql_variant 參數：

AVG

RADIANS

STDEV[P]

IDENTITY

ROUND

SUM

ISNUMERIC

SIGN

VAR[P]

POWER

 

 

CAST 與 CONVERT 函數支援 sql_variant。

新函數 SQL_VARIANT_PROPERTY() 可用來取得有關 sql_variant 值的屬性資訊，如資料類型、有效位數或小數位數。

其他 Transact-SQL 元素與 sql_variant 資料

LIKE 述詞中不支援 sql_variant 資料行。

全文檢索索引中不支援 sql_variant 資料行。全文檢索函數 (例如 CONTAINSTABLE 和 FREETEXTTABLE) 中不可指定 sql_variant 資料行。

下列 Transact-SQL 陳述式支援在指定其他整數資料類型的相同語法位置上指定 sql_variant：

  • ALTER PROCEDURE

  • ALTER TABLE

  • CREATE PROCEDURE

  • CREATE TABLE

  • DECLARE variable

SQL Server 目錄元件報告有關 sql_variant 資料行的資訊。

如果 CASE 運算式的任一個輸入或結果運算式評估為 sql_variant，結果就是 sql_variant。結果的基礎基底資料類型是在執行階段驗算結果之運算式的基底資料類型。

數值或字串串連運算子的運算元不可以是 sql_variant。例如，下列程式碼會產生錯誤：

SELECT VariantCol + @CharacterVar

FROM MyTable

不過，轉換 sql_variant 運算元後，即可執行此作業：

SELECT CAST(VariantCol AS varchar(25)) + @CharacterVar

FROM MyTable

應用程式與 sql_variant 資料

如果應用程式要求一個結果集，其中特定資料行傳回單一基礎基底資料類型的 sql_variant 資料，則應用程式可以在 Transact-SQL 陳述式中使用 CAST 或 CONVERT 函數，以基礎基底資料類型傳回 sql_variant 資料。在這種情況下，應用程式會將資料當成與基礎基底資料類型的結果集資料行一樣。

SQL Server Native Client OLE DB Provider for SQL Server 導入一種提供者特定的 OLE DB 類型 DBTYPE_SQLVARIANT，用於 sql_variant 資料行與參數。

SQL Server SQL Server Native Client ODBC 驅動程式導入一種提供者特定的 ODBC 資料庫資料類型 SQL_SS_VARIANT，用於 sql_variant 資料行與參數。

當您使用的應用程式透過下列介面連接時，SQL Server 會將 sql_variant 值轉換成 nvarchar(4000)：

  • OLE DB Provider for SQL Server 7.0 版。

  • SQL Server 7.0 的 SQL Server ODBC 驅動程式。

如果產生的字串超過 4,000 個字元，SQL Server 會傳回前 4,000 個字元。

當使用的應用程式透過下列介面連接時，SQL Server 會將 sql_variant 值轉換成 varchar(255)：

  • SQL Server 6.5 或舊版的 SQL Server ODBC 驅動程式。

如果產生的字串超過 255 個字元，SQL Server 會傳回前 255 個字元。