sql_variant (Transact-SQL)sql_variant (Transact-SQL)

• 09/12/2017
• • 
  • 
  • 

適用於： SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) 平行處理資料倉儲 APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

儲存各種 SQL ServerSQL Server 支援之資料類型值的資料類型。A data type that stores values of various SQL ServerSQL Server-supported data types.

Transact-SQL 語法慣例 Transact-SQL Syntax Conventions

語法Syntax

sql_variant  

RemarksRemarks

sql_variant 可用在資料行、參數、變數及使用者定義函式的傳回值中。sql_variant can be used in columns, parameters, variables, and the return values of user-defined functions. sql_variant 可讓這些資料庫物件支援其他資料類型的值。sql_variant enables these database objects to support values of other data types.

sql_variant 類型的資料行可包含不同資料類型的資料列。A column of type sql_variant may contain rows of different data types. 例如，定義為 sql_variant 的資料行可儲存 int、binary，及 char 值。For example, a column defined as sql_variant can store int, binary, and char values.

sql_variant 的最大長度是 8016 位元組。sql_variant can have a maximum length of 8016 bytes. 其中包括基底類型資訊和基底類型值。This includes both the base type information and the base type value. 實際基底類型值的最大長度是 8,000 位元組。The maximum length of the actual base type value is 8,000 bytes.

sql_variant 資料類型必須先轉換成其基底資料型別值，之後才能參與加法和減法之類的作業。A sql_variant data type must first be cast to its base data type value before participating in operations such as addition and subtraction.

您可以指派預設值給 sql_variant。sql_variant can be assigned a default value. 這項資料類型也可以用 NULL 作為基礎值，但 NULL 值並沒有相關的基底類型。This data type can also have NULL as its underlying value, but the NULL values will not have an associated base type. 此外，sql_variant 的基底類型不可為另一個 sql_variant。Also, sql_variant cannot have another sql_variant as its base type.

唯一、主要或外部索引鍵可以包括 sql_variant 類型的資料行，但組成特定資料列的索引鍵之資料值總長度，不應超出索引的最大長度。A unique, primary, or foreign key may include columns of type sql_variant, but the total length of the data values that make up the key of a specific row should not be more than the maximum length of an index. 這是 900 位元組。This is 900 bytes.

一份資料表可以有任意數目的 sql_variant 資料行。A table can have any number of sql_variant columns.

在 CONTAINSTABLE 和 FREETEXTTABLE 中，無法使用 sql_variant。sql_variant cannot be used in CONTAINSTABLE and FREETEXTTABLE.

ODBC 不完全支援 sql_variant。ODBC does not fully support sql_variant. 因此，當您使用 Microsoft OLE DB Provider for ODBC (MSDASQL) 時，會將 sql_variant 資料行的查詢當作二進位資料傳回。Therefore, queries of sql_variant columns are returned as binary data when you use Microsoft OLE DB Provider for ODBC (MSDASQL). 例如，包含 'PS2091' 字元字串資料的 sql_variant 資料行會以 0x505332303931 傳回。For example, a sql_variant column that contains the character string data 'PS2091' is returned as 0x505332303931.

比較 sql_variant 值Comparing sql_variant Values

sql_variant 資料類型屬於轉換的資料類型階層清單頂端。The sql_variant data type belongs to the top of the data type hierarchy list for conversion. 為比較 sql_variant，SQL ServerSQL Server 資料類型階層順序會分組成資料類型家族。For sql_variant comparisons, the SQL ServerSQL Server data type hierarchy order is grouped into data type families.

下列規則適用於 sql_variant 比較：The following rules apply to sql_variant comparisons:

• 當比較不同基底資料型別的 sql_variant 值，且基底資料型別是在不同的資料類型家族中時，資料類型家族在階層圖表中較高位置的值，會被視為兩個值中的較大者。When sql_variant values of different base data types are compared and the base data types are in different data type families, the value whose data type family is higher in the hierarchy chart is considered the greater of the two values.
• 當比較不同基底資料型別的 sql_variant 值，且基底資料型別是在相同的資料類型家族中，會先將基底資料型別在階層圖表中較低位置的值隱含轉換成其他資料類型，之後再進行比較。When sql_variant values of different base data types are compared and the base data types are in the same data type family, the value whose base data type is lower in the hierarchy chart is implicitly converted to the other data type and the comparison is then made.
• 當比較 char、varchar、nchar 或 nvarchar 資料類型的 sql_variant 值時，將會先根據下列準則來比較其定序：LCID、LCID 版本、比較旗標和排序識別碼。When sql_variant values of the char, varchar, nchar, or nvarchar data types are compared, their collations are first compared based on the following criteria: LCID, LCID version, comparison flags, and sort ID. 每一個準則都會以整數值的形式來比較，而且會根據所列的順序來比較。Each of these criteria are compared as integer values, and in the order listed. 如果所有的準則都相同，將會根據此定序來比較實際的字串值。If all of these criteria are equal, then the actual string values are compared according to the collation.

轉換 sql_variant 資料Converting sql_variant Data

處理 sql_variant 資料類型時，SQL ServerSQL Server 支援將有其他資料類型的物件隱含轉換成 sql_variant 類型。When handling the sql_variant data type, SQL ServerSQL Server supports implicit conversions of objects with other data types to the sql_variant type. 但 SQL ServerSQL Server 不支援從 sql_variant 資料隱含轉換成其他資料類型的物件。However, SQL ServerSQL Server does not support implicit conversions from sql_variant data to an object with another data type.

限制Restrictions

下表列出無法使用 sql_variant 來儲存的值類型：The following table lists the types of values that cannot be stored by using sql_variant:

¹ SQL Server 2012 和更新版本不會限制 datetimeoffset。¹ SQL Server 2012 and greater do not restrict datetimeoffset.

範例Examples

A.A. 在資料表中使用 sql_variantUsing a sql_variant in a table

下列範例會使用 sql_variant 資料類型建立資料表。The following example, creates a table with a sql_variant data type. 接著範例會擷取有關 colA 值 46279.1 的 SQL_VARIANT_PROPERTY 資訊，如果 tableA 有 colB 和 sql_variant 類型的 colA，則 colB =1689。Then the example retrieves SQL_VARIANT_PROPERTY information about the colA value 46279.1 where colB =1689, given that tableA has colA that is of type sql_variant and colB.

CREATE   TABLE tableA(colA sql_variant, colB int)  
INSERT INTO tableA values ( cast (46279.1 as decimal(8,2)), 1689)  
SELECT   SQL_VARIANT_PROPERTY(colA,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(colA,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(colA,'Scale') AS 'Scale'  
FROM      tableA  
WHERE      colB = 1689  

以下為結果集：Here is the result set. 請注意，這三個值的每一個都是 sql_variant。Note that each of these three values is a sql_variant.

Base Type    Precision    Scale  
---------    ---------    -----  
decimal      8           2  
  
(1 row(s) affected)  

B.B. 使用 sql_variant 作為變數Using a sql_variant as a variable

下列範例會使用 sql_variant 資料類型建立變數，然後擷取關於名為 @v1 之變數的 SQL_VARIANT_PROPERTY 資訊。The following example, creates a variable using the sql_variant data type, and then retrieves SQL_VARIANT_PROPERTY information about a variable named @v1.

DECLARE @v1 sql_variant;  
SET @v1 = 'ABC';  
SELECT @v1;  
SELECT SQL_VARIANT_PROPERTY(@v1, 'BaseType');  
SELECT SQL_VARIANT_PROPERTY(@v1, 'MaxLength');  

另請參閱See also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
SQL_VARIANT_PROPERTY (Transact-SQL)SQL_VARIANT_PROPERTY (Transact-SQL)