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

SQL Prompt根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發者不大熟悉腳本時尤其有用。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數據類型，所以不可能有技術問題阻止其他函數工作。

本教程內容尚未完結，后續內容請點擊下面的文章~

半島外圍網上直營相關的文章：

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

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

SQL語法提示工具SQL Prompt教程：忽略使用或濫用RETURN關鍵字（BP016）

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

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

想要購買SQL Prompt正版授權，或了解更多產品信息請點擊

掃描關注慧聚IT微信公眾號，及時獲取最新動態及最新資訊