SQL語法提示工具SQL Prompt，幫你認識SQL_VA

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

本教程說明了SQL_VARIANT數(shù)據(jù)類型的“怪癖”，以及為什么最好調查SQL Prompt何時提醒您使用它。如果在使用之前將其顯式轉換為真實類型，那么將數(shù)據(jù)存儲為SQL_VARIANT才是唯一安全的。

sql_variant數(shù)據(jù)類型來自幾個不同數(shù)據(jù)類型的值，并由SQL Server在內部使用。它不是SQL標準的一部分，在關系數(shù)據(jù)庫中的用途有限。需要小心處理它，因為它的誤用會導致難以追蹤的性能問題和bug。sql_variant不能直接傳遞給某些SQL運算符和函數(shù)，例如LIKE、SUM()或者AVG()，并且在比較或表達式中使用時會產(chǎn)生誤導性結果。除二進制數(shù)據(jù)外，它不能通過ODBC返回到應用程序。

SQL Server是一種強類型語言，這樣做是為了確保數(shù)據(jù)完整性、高效存儲和有效檢索。由于這個原因，使用sql_variant有點奇怪，因此通過不明智地使用它會無意中造成問題也就不足為奇了。出于這些原因，SQL Prompt強制執(zhí)行“最佳實踐”代碼分析規(guī)則（BP024），該規(guī)則將提醒您使用sql_variant數(shù)據(jù)類型。

與許多“最佳實踐”規(guī)則一樣，這些建議有時聽起來像是告訴人們在拿著剪刀時不要跑。在這種情況下，只有在使用數(shù)據(jù)sql_variant之前將其顯式轉換為真實類型，才能將數(shù)據(jù)存儲為安全。

為什么有sql_variant？

sql_variant數(shù)據(jù)類型是在微軟從Sybase開發(fā)的SQL Server時首次引入的。他們需要能夠從微軟首次進入數(shù)據(jù)庫市場的Microsoft Access將數(shù)據(jù)庫導入SQL Server，該市場支持變體數(shù)據(jù)類型。它仍然在SQL Server內部用于系統(tǒng)存儲過程的參數(shù)以及擴展屬性等數(shù)據(jù)。

sql_variant傾向于作為用戶定義函數(shù)返回的列、變量、參數(shù)或值的catch-all數(shù)據(jù)類型。它最多可以容納8000個字節(jié)，并且可以存儲基本數(shù)據(jù)類型，如整數(shù)、小數(shù)、字符串和日期。它不能存儲其他一些數(shù)據(jù)類型，例如（MAX）數(shù)據(jù)類型、CLR數(shù)據(jù)類型或XML。

有時，sql_variant可能是一個有用的工具，例如在處理不一致或未指定的數(shù)據(jù)類型時，這通常是因為數(shù)據(jù)庫支持允許用戶定義數(shù)據(jù)的應用程序。

它存儲所包含的值的基本數(shù)據(jù)類型，因此當它用作中介時，強制執(zhí)行數(shù)據(jù)類型之間的所有轉換規(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ù)可用作初步檢查，因為主鍵（或索引）的總大小限制為900字節(jié)。

順便提一句，您可以在任何數(shù)據(jù)類型上使用此函數(shù)。例如：

SELECT?SQL_VARIANT_PROPERTY(N'Béoáed?mac?Ocláin','collation')

聚合

讓我們看看如果我們嘗試聚合sql_variant列會發(fā)生什么。為了簡單起見，我們將從派生表中執(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);

我們看到一個錯誤：

Msg 8117，Level 16，State 1，Line 3操作數(shù)數(shù)據(jù)類型sql_variant對sum運算符無效。

而如果我們先顯式地轉換為數(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ù)工作。

比較

您不能用LIKE過濾sql_variant列，因為LIKE它不支持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%'

錯誤時候這樣的：

Msg 8116，Level 16，State 1，Line 4

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

實際上，沒有任何字符串函數(shù)接受sql_variant，并且不會嘗試對字符串進行隱式轉換。相反，他們只是拒絕參數(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子句中顯式轉換數(shù)據(jù)類型，否則在隱藏在漫長且曲折的過程中時，可能會得到不正確的結果，其原因很難檢測到。例如，這只返回第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并為其提供字符串值，會發(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)在它返回所有你可能不會想到的行。這里的問題是，為了評估表達式，SQL Server檢查它的基類型或類型族，并將其與我們的變量類型進行比較。sql_variant的基類型系列可以是Unicode、精確數(shù)字、近似數(shù)字、日期和時間、二進制或唯一標識符，我們的ValueAsVariant列包含精確數(shù)字。

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

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

ODBC支持

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

限制在索引中使用sql_variant

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

DECLARE?@MyTableVariable?TABLE?(MyProperty?sql_Variant?PRIMARY?KEY)

我們得到一個警告：

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

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

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

我們得到錯誤......

Ms 1946，Level 16，State 3，Line 45

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

結論

sql_variant在用戶表中使用數(shù)據(jù)類型是一種代碼味道，因為它將非類型化數(shù)據(jù)類型引入強類型語言，并且需要進行調查，就像您在家聞到燒焦的味道一樣。它可能只是燒烤，但它可能更令人擔憂。

sql_variant 具有合法用途，但總有一種風險，即盡管您可能確切知道如何使用它們，但是其他必須維護或調試代碼的人可能不知道，并且如果您除了純粹使用它們之外做任何其他事情，則最有可能導致問題用于存儲。

你絕不能依賴sql_variant的隱式轉換，因為它經(jīng)常失敗，要么是因為它沒有實現(xiàn)，要么是因為它是奇怪的。相反，在進行比較、表達式或聚合之前，將它們顯式轉換為SQL數(shù)據(jù)類型。如果你不完全確定你理解了最后一句話，那么最好永遠不要使用sql_variant。