SQL Prompt根據(jù)數(shù)據(jù)庫(kù)的對(duì)象名稱、語(yǔ)法和代碼片段自動(dòng)進(jìn)行檢索，為用戶提供合適的代碼選擇。自動(dòng)腳本設(shè)置使代碼簡(jiǎn)單易讀--當(dāng)開(kāi)發(fā)者不大熟悉腳本時(shí)尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點(diǎn)擊下載SQL Prompt免費(fèi)版

本篇文章說(shuō)明了SQL_VARIANT數(shù)據(jù)類型的“怪癖”，以及為什么最好調(diào)查SQL Prompt何時(shí)提醒您使用它。如果在使用之前將其顯式轉(zhuǎn)換為真實(shí)類型，那么將數(shù)據(jù)存儲(chǔ)為SQL_VARIANT才是唯一安全的。本文是該教程的后半部分，內(nèi)容緊接上文~

比較

您不能用LIKE過(guò)濾sql_variant列，因?yàn)長(zhǎng)IKE它不支持sql_variant參數(shù)。

SELECT    f.ValueAsVariant, f.ValueAsInt, f.ValueAsString
    FROM
      (
      VALUES (Convert(SQL_VARIANT,'one'), 1, Convert(VARCHAR(5),1)),
             ('two', 2, 2),
             ('three', 3, 3),
             ('four', 4, 4), 
             ('five', 5, 5)
      ) AS f(ValueAsVariant, ValueAsInt, ValueAsString)
      WHERE ValueAsVariant like 't%'

錯(cuò)誤時(shí)候這樣的：

Msg 8116，Level 16，State 1，Line 4

參數(shù)數(shù)據(jù)類型sql_variant對(duì)于LIKE函數(shù)的參數(shù)1無(wú)效。

實(shí)際上，沒(méi)有任何字符串函數(shù)接受sql_variant，并且不會(huì)嘗試對(duì)字符串進(jìn)行隱式轉(zhuǎn)換。相反，他們只是拒絕參數(shù)。相反，如果我們聲明它到底是什么類型的數(shù)據(jù)類型，它的工作原理如下：

SELECT    f.ValueAsVariant, f.ValueAsInt, f.ValueAsString
    FROM
      (
      VALUES (Convert(SQL_VARIANT,'one'), 1, Convert(NVARCHAR(5),1)),
             ('two', 2, 2),
             ('three', 3, 3),
             ('four', 4, 4), 
             ('five', 5, 5)
      ) AS f(ValueAsVariant, ValueAsInt, ValueAsString)
      WHERE Convert(VARCHAR(20),ValueAsVariant) like 't%'

除非您sql_variant在WHERE子句中顯式轉(zhuǎn)換數(shù)據(jù)類型，否則在隱藏在漫長(zhǎng)且曲折的過(guò)程中時(shí)，可能會(huì)得到不正確的結(jié)果，其原因很難檢測(cè)到。例如，這只返回第4行和第5行，這是您所期望的：

DECLARE @ParameterAsINT INT
  SELECT @ParameterAsINT = 3
  SELECT    f.ValueAsString, f.ValueAsInt, f.ValueAsVariant
    FROM
      (
      VALUES ('one', 1, Convert(SQL_VARIANT, 1)),
             ('two', 2, 2),
             ('three', 3, 3),
             ('four', 4, 4), 
             ('five', 5, 5)
      ) AS f (ValueAsString, ValueAsInt, ValueAsVariant)
      WHERE ValueAsVariant > @ParameterAsInt

但是，如果我們將參數(shù)更改為a sql_variant并為其提供字符串值，會(huì)發(fā)生什么？

DECLARE @ParameterAsVariant sql_variant 
  SELECT @ParameterAsVariant ='3'
  SELECT    f.ValueAsString, f.ValueAsInt, f.ValueAsVariant
    FROM
      (
      VALUES ('one', 1, Convert(SQL_VARIANT, 1)),
             ('two', 2, 2),
             ('three', 3, 3),
             ('four', 4, 4), 
             ('five', 5, 5)
      ) AS f (ValueAsString, ValueAsInt, ValueAsVariant)
      WHERE ValueAsVariant > @ParameterAsVariant

現(xiàn)在它返回所有你可能不會(huì)想到的行。這里的問(wèn)題是，為了評(píng)估表達(dá)式，SQL Server檢查它的基類型或類型族，并將其與我們的變量類型進(jìn)行比較。sql_variant的基類型系列可以是Unicode、精確數(shù)字、近似數(shù)字、日期和時(shí)間、二進(jìn)制或唯一標(biāo)識(shí)符，我們的ValueAsVariant列包含精確數(shù)字。

在第一個(gè)僅返回第4行和第5行的示例中，我們的參數(shù)類型與ValueAsVariant列的類型屬于同一族。SQL Server執(zhí)行隱式轉(zhuǎn)換，代碼可以正常工作。但是，在第二個(gè)示例中，我們使用sql_variant帶有字符串值的參數(shù)，其中@ParameterAsVariant包含Unicode。而不是將Unicode類型隱式轉(zhuǎn)換為精確數(shù)字（即“高級(jí)”數(shù)據(jù)類型），SQL Server判斷高級(jí)數(shù)據(jù)類型為“更大”，因此我們的搜索條件對(duì)每一行的計(jì)算結(jié)果為true。

這顯然是sql_variant的一個(gè)怪癖。如果我們比較完全相同的基本數(shù)據(jù)類型的兩個(gè)sql_variant值，它將“工作”。如果我們將sql_variant與同一系列中的另一種數(shù)據(jù)類型進(jìn)行比較，隱式轉(zhuǎn)換將允許它工作。除此之外，一切都不可能了。

ODBC支持

ODBC不完全支持sql_variant。當(dāng)使用與包含sql_variant類型的表的連接時(shí)，您會(huì)注意到這一點(diǎn)，因?yàn)?sql_variant當(dāng)您使用Microsoft OLE DB Provider for ODBC（MSDASQL）時(shí)，列中的數(shù)據(jù)將作為二進(jìn)制數(shù)據(jù)（例如0x32303931）返回。

限制在索引中使用sql_variant

sql_variant僅當(dāng)索引的總長(zhǎng)度小于900字節(jié)的最大值時(shí)，才可以在索引中包含列。這意味著如果值的長(zhǎng)度超過(guò)900個(gè)字節(jié)，則索引sql_variant列上的插入操作將失敗。如果我們創(chuàng)建表或表變量：

DECLARE @MyTableVariable TABLE (MyProperty sql_Variant PRIMARY KEY)

我們得到一個(gè)警告：

警告！聚簇索引的最大密鑰長(zhǎng)度為900字節(jié)。索引“PK __＃B2961DC__8E45D1198BEEA325”的最大長(zhǎng)度為8016字節(jié)。對(duì)于某些大值組合，插入或更新操作將失敗。

如果我們忽略警告......

DECLARE @MyTableVariable TABLE (MyProperty sql_Variant PRIMARY KEY)
  INSERT INTO @MyTableVariable (MyProperty) 
   VALUES (N'Abbán moccu Corbmaic'),
    (N'Abel of Reims'),
    (N'Buíte [Boetius] mac Brónaig'),
    (N'Buriana'),
    (Replicate(N'Caillín [Caillén] mac Niataig  Crom mac Feradaig, Comgall mac Sétnai, Comgán mac Dá Cherda, Commán mac Fáelchon, Mo ChommócCrónán of Balla, see Mo Chua mac Bécáin',3))

我們得到錯(cuò)誤......

Ms 1946，Level 16，State 3，Line 45

操作失敗。索引“PK __＃B72883F__8E45D1191C112AAE”的長(zhǎng)度為980字節(jié)的索引條目超過(guò)了聚簇索引的最大長(zhǎng)度900字節(jié)。

結(jié)論

sql_variant在用戶表中使用數(shù)據(jù)類型是一種代碼味道，因?yàn)樗鼘⒎穷愋突瘮?shù)據(jù)類型引入強(qiáng)類型語(yǔ)言，并且需要進(jìn)行調(diào)查，就像您在家聞到燒焦的味道一樣。它可能只是燒烤，但它可能更令人擔(dān)憂。

sql_variant 具有合法用途，但總有一種風(fēng)險(xiǎn)，即盡管您可能確切知道如何使用它們，但是其他必須維護(hù)或調(diào)試代碼的人可能不知道，并且如果您除了純粹使用它們之外做任何其他事情，則最有可能導(dǎo)致問(wèn)題用于存儲(chǔ)。

你絕不能依賴sql_variant的隱式轉(zhuǎn)換，因?yàn)樗?jīng)常失敗，要么是因?yàn)樗鼪](méi)有實(shí)現(xiàn)，要么是因?yàn)樗瞧婀值摹Ｏ喾矗谶M(jìn)行比較、表達(dá)式或聚合之前，將它們顯式轉(zhuǎn)換為SQL數(shù)據(jù)類型。如果你不完全確定你理解了最后一句話，那么最好永遠(yuǎn)不要使用sql_variant。

本教程內(nèi)容完結(jié)了，相關(guān)內(nèi)容請(qǐng)看下面的文章，后續(xù)還會(huì)更新內(nèi)容哦~

半島外圍網(wǎng)上直營(yíng)相關(guān)的文章：

SQL語(yǔ)法提示工具SQL Prompt教程：使用SQL_VARIANT數(shù)據(jù)類型引起的問(wèn)題（上）

SQL語(yǔ)法提示工具SQL Prompt教程：避免使用@@IDENTITY函數(shù)的原因

SQL語(yǔ)法提示工具SQL Prompt教程：忽略使用或?yàn)E用RETURN關(guān)鍵字（BP016）

SQL語(yǔ)法提示工具SQL Prompt教程：添加NOT NULL列或使可空列NOT NULL的問(wèn)題（上）

SQL語(yǔ)法提示工具SQL Prompt教程：添加NOT NULL列或使可空列NOT NULL的問(wèn)題（下）

想要購(gòu)買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請(qǐng)點(diǎn)擊

掃描關(guān)注慧聚IT微信公眾號(hào)，及時(shí)獲取最新動(dòng)態(tài)及最新資訊