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

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

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

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

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

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

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

為什么有sql_variant？

sql_variant數(shù)據(jù)類型是在微軟從Sybase開發(fā)的SQL Server時首次引入的。他們需要能夠從微軟首次進(jìn)入數(shù)據(jù)庫市場的Microsoft Access將數(shù)據(jù)庫導(dǎo)入SQL Server，該市場支持變體數(shù)據(jù)類型。它仍然在SQL Server內(nèi)部用于系統(tǒng)存儲過程的參數(shù)以及擴(kuò)展屬性等數(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ù)類型時，這通常是因?yàn)閿?shù)據(jù)庫支持允許用戶定義數(shù)據(jù)的應(yīng)用程序。

它存儲所包含的值的基本數(shù)據(jù)類型，因此當(dāng)它用作中介時，強(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列會發(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運(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ù)工作。

本教程內(nèi)容尚未完結(jié)，后續(xù)內(nèi)容請點(diǎn)擊下面的文章~

相關(guān)文章：

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

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

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

SQL語法提示工具SQL Prompt教程：添加NOT NULL列或使可空列NOT NULL的問題（上）

SQL語法提示工具SQL Prompt教程：添加NOT NULL列或使可空列NOT NULL的問題（下）

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點(diǎn)擊“咨詢在線客服”

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