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

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

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

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

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

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

為什么有sql_variant？

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

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

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

它存儲所包含的值的基本數據類型，因此當它用作中介時，強制執行數據類型之間的所有轉換規則。您可以使用數據類型函數檢索此基本數據類型sql_variant_property()：

DECLARE?@MyVariant?SQL_VARIANT?=?'2.3657'
??SELECT?SQL_VARIANT_PROPERTY(@MyVariant,'BaseType')

在這種情況下返回varchar。這里還有一些其他有用的屬性：Precision、Scale、TotalBytes、Collation和MaxLength。如果要從sql_variant生成主鍵，則TotalBytes參數使該函數可用作初步檢查，因為主鍵（或索引）的總大小限制為900字節。

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

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

聚合

讓我們看看如果我們嘗試聚合sql_variant列會發生什么。為了簡單起見，我們將從派生表中執行此操作。

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操作數數據類型sql_variant對sum運算符無效。

而如果我們先顯式地轉換為數字（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()聚合函數似乎很好地工作的sql_variant數據類型，所以不可能有技術問題阻止其他函數工作。

比較

您不能用LIKE過濾sql_variant列，因為LIKE它不支持sql_variant參數。

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

參數數據類型sql_variant對于LIKE函數的參數1無效。

實際上，沒有任何字符串函數接受sql_variant，并且不會嘗試對字符串進行隱式轉換。相反，他們只是拒絕參數。相反，如果我們聲明它到底是什么類型的數據類型，它的工作原理如下：

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子句中顯式轉換數據類型，否則在隱藏在漫長且曲折的過程中時，可能會得到不正確的結果，其原因很難檢測到。例如，這只返回第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

但是，如果我們將參數更改為a sql_variant并為其提供字符串值，會發生什么？

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

現在它返回所有你可能不會想到的行。這里的問題是，為了評估表達式，SQL Server檢查它的基類型或類型族，并將其與我們的變量類型進行比較。sql_variant的基類型系列可以是Unicode、精確數字、近似數字、日期和時間、二進制或唯一標識符，我們的ValueAsVariant列包含精確數字。

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

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

ODBC支持

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

限制在索引中使用sql_variant

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

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

我們得到一個警告：

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

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

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字節的索引條目超過了聚簇索引的最大長度900字節。

結論

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

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

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