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

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

點擊下載SQL Prompt免費版

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

比較

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

本教程內容完結了，相關內容請看下面的文章，后續(xù)還會更新內容哦~

金喜正規(guī)買球相關的文章：

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

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

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

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

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

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

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