SQL Prompt根據(jù)數(shù)據(jù)庫的對(duì)象名稱、語法和代碼片段自動(dòng)進(jìn)行檢索，為用戶提供合適的代碼選擇。自動(dòng)腳本設(shè)置使代碼簡(jiǎn)單易讀--當(dāng)開發(fā)者不大熟悉腳本時(shí)尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。
本教程說明了SQL_VARIANT數(shù)據(jù)類型的“怪癖”，以及為什么最好調(diào)查SQL Prompt何時(shí)提醒您使用它。如果在使用之前將其顯式轉(zhuǎn)換為真實(shí)類型，那么將數(shù)據(jù)存儲(chǔ)為SQL_VARIANT才是唯一安全的。
sql_variant數(shù)據(jù)類型來自幾個(gè)不同數(shù)據(jù)類型的值，并由SQL Server在內(nèi)部使用。它不是SQL標(biāo)準(zhǔn)的一部分，在關(guān)系數(shù)據(jù)庫中的用途有限。需要小心處理它，因?yàn)樗恼`用會(huì)導(dǎo)致難以追蹤的性能問題和bug。sql_variant不能直接傳遞給某些SQL運(yùn)算符和函數(shù)，例如LIKE、SUM()或者AVG()，并且在比較或表達(dá)式中使用時(shí)會(huì)產(chǎn)生誤導(dǎo)性結(jié)果。除二進(jìn)制數(shù)據(jù)外，它不能通過ODBC返回到應(yīng)用程序。
SQL Server是一種強(qiáng)類型語言，這樣做是為了確保數(shù)據(jù)完整性、高效存儲(chǔ)和有效檢索。由于這個(gè)原因，使用sql_variant有點(diǎn)奇怪，因此通過不明智地使用它會(huì)無意中造成問題也就不足為奇了。出于這些原因，SQL Prompt強(qiáng)制執(zhí)行“最佳實(shí)踐”代碼分析規(guī)則（BP024），該規(guī)則將提醒您使用sql_variant數(shù)據(jù)類型。
與許多“最佳實(shí)踐”規(guī)則一樣，這些建議有時(shí)聽起來像是告訴人們?cè)谀弥舻稌r(shí)不要跑。在這種情況下，只有在使用數(shù)據(jù)sql_variant之前將其顯式轉(zhuǎn)換為真實(shí)類型，才能將數(shù)據(jù)存儲(chǔ)為安全。
為什么有sql_variant？
sql_variant數(shù)據(jù)類型是在微軟從Sybase開發(fā)的SQL Server時(shí)首次引入的。他們需要能夠從微軟首次進(jìn)入數(shù)據(jù)庫市場(chǎng)的Microsoft Access將數(shù)據(jù)庫導(dǎo)入SQL Server，該市場(chǎng)支持變體數(shù)據(jù)類型。它仍然在SQL Server內(nèi)部用于系統(tǒng)存儲(chǔ)過程的參數(shù)以及擴(kuò)展屬性等數(shù)據(jù)。
sql_variant傾向于作為用戶定義函數(shù)返回的列、變量、參數(shù)或值的catch-all數(shù)據(jù)類型。它最多可以容納8000個(gè)字節(jié)，并且可以存儲(chǔ)基本數(shù)據(jù)類型，如整數(shù)、小數(shù)、字符串和日期。它不能存儲(chǔ)其他一些數(shù)據(jù)類型，例如（MAX）數(shù)據(jù)類型、CLR數(shù)據(jù)類型或XML。
有時(shí)，sql_variant可能是一個(gè)有用的工具，例如在處理不一致或未指定的數(shù)據(jù)類型時(shí)，這通常是因?yàn)閿?shù)據(jù)庫支持允許用戶定義數(shù)據(jù)的應(yīng)用程序。
它存儲(chǔ)所包含的值的基本數(shù)據(jù)類型，因此當(dāng)它用作中介時(shí)，強(qiáng)制執(zhí)行數(shù)據(jù)類型之間的所有轉(zhuǎn)換規(guī)則。您可以使用數(shù)據(jù)類型函數(shù)檢索此基本數(shù)據(jù)類型sql_variant_property()：
DECLARE?@MyVariant?SQL_VARIANT?=?'2.3657' ??SELECT?SQL_VARIANT_PROPERTY(@MyVariant,'BaseType')
在這種情況下返回varchar。這里還有一些其他有用的屬性：Precision、Scale、TotalBytes、Collation和MaxLength。如果要從sql_variant生成主鍵，則TotalBytes參數(shù)使該函數(shù)可用作初步檢查，因?yàn)橹麈I（或索引）的總大小限制為900字節(jié)。
順便提一句，您可以在任何數(shù)據(jù)類型上使用此函數(shù)。例如：
SELECT?SQL_VARIANT_PROPERTY(N'Béoáed?mac?Ocláin','collation')
聚合
讓我們看看如果我們嘗試聚合sql_variant列會(huì)發(fā)生什么。為了簡(jiǎn)單起見，我們將從派生表中執(zhí)行此操作。
重慶分公司
重慶分公司
SELECT?Sum(ValueAsVariant)
????FROM
??????(
??????VALUES?(Convert(SQL_VARIANT,?'one'),?1,?Convert(SQL_VARIANT,?1)),
?????????????('two',?2,?2),
?????????????('three',?3,?3),
?????????????('four',?4,?4),?
?????????????('five',?5,?5)
??????)?AS?f?(ValueAsString,?ValueAsInt,?ValueAsVariant);我們看到一個(gè)錯(cuò)誤：
Msg 8117，Level 16，State 1，Line 3操作數(shù)數(shù)據(jù)類型sql_variant對(duì)sum運(yùn)算符無效。
而如果我們先顯式地轉(zhuǎn)換為數(shù)字（int、numeric等等），它工作正常。
SELECT?Sum(Convert(NUMERIC(9,4),?ValueAsVariant))
??--?try?sum,?avg,?stdev,?stdevp,?var,?varp,?or?string_agg
????FROM
??????(
??????VALUES?(Convert(SQL_VARIANT,?'one'),?1,?Convert(SQL_VARIANT,?1)),
?????????????('two',?2,?2),
?????????????('three',?3,?3),
?????????????('four',?4,?4),?
?????????????('five',?5,?5)
??????)?AS?f?(ValueAsString,?ValueAsInt,?ValueAsVariant);在max()和min()聚合函數(shù)似乎很好地工作的sql_variant數(shù)據(jù)類型，所以不可能有技術(shù)問題阻止其他函數(shù)工作。
比較
您不能用LIKE過濾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無效。
實(shí)際上，沒有任何字符串函數(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)且曲折的過程中時(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ì)想到的行。這里的問題是，為了評(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)閟ql_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)度超過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é)的索引條目超過了聚簇索引的大長(zhǎng)度900字節(jié)。
結(jié)論
sql_variant在用戶表中使用數(shù)據(jù)類型是一種代碼味道，因?yàn)樗鼘⒎穷愋突瘮?shù)據(jù)類型引入強(qiáng)類型語言，并且需要進(jìn)行調(diào)查，就像您在家聞到燒焦的味道一樣。它可能只是燒烤，但它可能更令人擔(dān)憂。
sql_variant 具有合法用途，但總有一種風(fēng)險(xiǎn)，即盡管您可能確切知道如何使用它們，但是其他必須維護(hù)或調(diào)試代碼的人可能不知道，并且如果您除了純粹使用它們之外做任何其他事情，則最有可能導(dǎo)致問題用于存儲(chǔ)。
你絕不能依賴sql_variant的隱式轉(zhuǎn)換，因?yàn)樗?jīng)常失敗，要么是因?yàn)樗鼪]有實(shí)現(xiàn)，要么是因?yàn)樗瞧婀值摹Ｏ喾?，在進(jìn)行比較、表達(dá)式或聚合之前，將它們顯式轉(zhuǎn)換為SQL數(shù)據(jù)類型。如果你不完全確定你理解了最后一句話，那么最好永遠(yuǎn)不要使用sql_variant。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。
