SQL Prompt使用教程：為什么不要創(chuàng)建將ANSI_PADDING設(shè)置為OFF的列？

---

SQL Prompt是一款實用的SQL語法提示工具。它根據(jù)數(shù)據(jù)庫的對象名稱、語法和代碼片段自動進(jìn)行檢索，為用戶提供合適的代碼選擇。自動腳本設(shè)置使代碼簡單易讀--當(dāng)開發(fā)者不大熟悉腳本時尤其有用。本文介紹了不要創(chuàng)建將ANSI_PADDING設(shè)置為OFF的列。

查找永久更改表中某些數(shù)據(jù)類型檢索方式的連接設(shè)置，這有點奇怪，但是如果在創(chuàng)建臨時或永久表時使用SET ANSI_PADDING OFF，就會發(fā)生這種情況。在創(chuàng)建表時，由于設(shè)置不當(dāng)，或者偶然使用帶有DBLib連接的舊應(yīng)用程序，這會導(dǎo)致表中的某些列從那時起奇怪地或不一致地處理某些字符串或二進(jìn)制數(shù)據(jù)類型的尾隨空格。

此選項已被棄用，在某些時候，它將被刪除（它將始終為“on”）。SQL Prompt具有不贊成使用的語法規(guī)則DEP013，它將警告您使用此選項以及其他不贊成使用的SET選項。

什么是ANSI填充？為什么？

在SQL的早期，如何處理字符串的問題引起了爭議。定義和固定字符串長度的CHAR數(shù)據(jù)類型旨在使數(shù)據(jù)檢索簡單而有效。字符串以指定的長度存儲在CHAR數(shù)據(jù)類型中。對于較短的字符串，數(shù)據(jù)類型中所有剩余的字符位置都用空格（CHAR）或零（BINARY）填充。這些填充空格是字符串的一部分嗎？如果不是，您如何區(qū)分故意填充空格的值和自動填充的值？

SQL的早期實現(xiàn)通常在檢索數(shù)據(jù)時修剪掉CHAR中的所有尾隨空格，除非該列是NOT NULL。但是，很明顯，為了符合ANSI SQL標(biāo)準(zhǔn)，必須對此進(jìn)行更改。NIST測試套件檢查是否始終填充CHAR數(shù)據(jù)類型，并且對于CHAR或VARCHAR數(shù)據(jù)類型，用戶輸入的尾隨空格都不會被截斷。SQL Server決定，為了遵守規(guī)則，將隨數(shù)據(jù)一起檢索任何尾隨空格（無論是故意還是作為填充自動添加的），對于二進(jìn)制數(shù)據(jù)類型的尾隨零也是如此。但是，由于在舊的體制下編寫了太多代碼，因此Transact-SQL中引入了一種稱為ANSI_PADDING的設(shè)置。當(dāng)它關(guān)閉時，它允許此舊代碼照常工作。似乎每個人都很高興。

一旦有關(guān)CHAR數(shù)據(jù)類型的ANSI-ISO標(biāo)準(zhǔn)爭議平息下來，就會引入新的數(shù)據(jù)類型和新的表類型。ANSI_PADDING爭議僅影響當(dāng)時存在的類型，而用戶為此目的定義的長度。現(xiàn)在可以將字符串存儲為NVARCHAR、VARCHAR、NCHAR或CHAR的定義大小。二進(jìn)制數(shù)據(jù)可以存儲為BINARY或VARBINARY的定義大小。對于CHAR（n）、BINARY（n）、VARCHAR（n）或VARBINARY（n）的較早數(shù)據(jù)類型，在創(chuàng)建表時ANSI_PADDING選項的設(shè)置會影響SQL Server隨后處理這些字符串的方式。

但是，后來的NCHAR、NVARCHAR、NTEXT、TEXT或IMAGE數(shù)據(jù)類型并非如此。未定義長度的類型VARBINARY（MAX）、VARCHAR（MAX）和NVARCHAR（MAX）也不受影響。

舊數(shù)據(jù)庫開發(fā)人員真正需要關(guān)閉ANSI填充的唯一用途是無需使用該RTRIM()功能即可進(jìn)行字符串連接。避免必須使用RTRIM()函數(shù)似乎是個好主意，但是填充規(guī)則的行為與有可空列的行為不一致。另外，隨著新類型的表的引入，沒有人愿意使它們向后兼容，因此適用的規(guī)則通常在ANSI_PADDING關(guān)閉時對表變量根本不起作用。同樣，如果您嘗試在計算列或索引視圖上創(chuàng)建或更改索引，則很可能會陷入困境。如果您將ANSI_PADDING設(shè)置為OFF，則根本不允許這樣做。

那么，規(guī)則是什么？

ANSI標(biāo)準(zhǔn)的簡單行為是，對于固定寬度類型插入的數(shù)據(jù)，總是用尾隨空格或零填充到指定長度，然后，對于所有數(shù)據(jù)類型，任何尾隨空格或零都被視為數(shù)據(jù)的一部分，依此類推。當(dāng)SQL Server將數(shù)據(jù)檢索到內(nèi)存時，將永遠(yuǎn)不會修剪它們。

如果在創(chuàng)建表和列時將ANSI_PADDING切換為OFF，則行為將變得更加復(fù)雜。幸運的是，是否存在尾隨空格不會影響WHERE子句中的字符串比較，因為無論設(shè)置什么，這些始終會忽略它們。它也不會對比較產(chǎn)生很大的影響。關(guān)閉ANSI_PADDING的主要效果如下：

• CHAR NOT NULL和BINARY NOT NULL列在插入數(shù)據(jù)時被填充，并且隨后未進(jìn)行修剪（與ANSI標(biāo)準(zhǔn)相同的行為）
• 在檢索時會修剪可空的CHAR和BINARY列（因此，當(dāng)然在插入時不再填充）。您會丟失任何尾隨空格或故意添加的零
• 檢索時會修剪VARBINARY和VARCHAR列，因此您會丟失任何尾隨空格或故意添加的零

如果您需要說服力，我們可以證明所有這些。

您會看到以下消息：

使用ANSI_PADDING ON創(chuàng)建一個臨時表
現(xiàn)在使用ANSI_PADDING OFF創(chuàng)建相同的臨時表
現(xiàn)在使用ANSI_PADDING OFF'創(chuàng)建相同的表變量
重新打開ANSI_PADDING
插入兩個表
從第一個表中選擇，并在ANSI填充為ON的情況下創(chuàng)建（<>顯示字符串的范圍）
從第二個表中選擇，使用ANSI填充OFF創(chuàng)建：同一查詢
從表變量中選擇，使用ANSI填充OFF創(chuàng)建：相同的查詢

結(jié)果是這樣的：

正確的。與往常一樣，在創(chuàng)建表時將ANSI_PADDING設(shè)置為ON，我們故意添加尾隨空格或零的第一行就不會被裁剪。沒有尾隨空格的第二行被一致地添加為CHAR和BINARY數(shù)據(jù)類型，無論是否允許NULL。

第二個結(jié)果來自關(guān)閉ANSI_PADDING時創(chuàng)建的表。可為空的第一個CHAR列已被修剪。具有NOT NULL約束的CHAR列用空格填充。無論是否可以為空，VARBINARY列都修剪了尾隨零。VARCHAR列修剪了尾隨空格。

第三個結(jié)果來自一個表變量，該變量也是通過將ANSI_PADDING設(shè)置為OFF來創(chuàng)建的，該設(shè)置完全無害。無論設(shè)置如何，它的行為都與ANSI兼容。

如果您正在努力接受所有規(guī)則和例外，那么您并不孤單。

查找使用ANSI_PADDING關(guān)閉創(chuàng)建的異常列

在訪問表時，無論您對ANSI_PADDING進(jìn)行了何種設(shè)置，查詢行為都是一致的。該設(shè)置將保留在表列中，而連接設(shè)置將被忽略。無論使用何種連接設(shè)置訪問“舊版”數(shù)據(jù)庫，該數(shù)據(jù)庫都能始終如一地運行。我們可以通過查詢元數(shù)據(jù)來檢查臨時表發(fā)生了什么。

USE tempdb
SELECT S.name AS TheColumn,
  Object_Schema_Name(S.object_id) + '.' + Object_Name(S.object_id) AS TableName,
  is_ansi_padded
  FROM sys.columns AS S
    INNER JOIN sys.tables AS t
      ON t.object_id = S.object_id
  WHERE system_type_id IN (165, 167, 173, 175) 
     AND is_ansi_padded = 0; --ansi padding off!!

sys.columns視圖中的列如果ANSI_PADDING處于打開狀態(tài)，則為1；如果關(guān)閉，則為0。該查詢將非常快速地告訴您數(shù)據(jù)庫是否有設(shè)置為ANSI_PADDING off的異常列（只需去掉第一行“USE tempdb”）。

結(jié)論

除非有人最終有意或無意關(guān)閉ANSI_PADDING的危險消失，否則在從SQL Server中最終刪除該功能之前，請始終在執(zhí)行表CREATE語句之前使用SET ANSI_PADDING ON設(shè)置與ANSI行為的連接，但在其他任何地方都不應(yīng)使用的設(shè)置，因為該設(shè)置和支持已計劃棄用，此時您將無法關(guān)閉ANSI兼容性。

本教程內(nèi)容到這里就完結(jié)了，感興趣的朋友可以繼續(xù)關(guān)注慧都網(wǎng)了解更多產(chǎn)品資訊~您也可以下載SQL Prompt試用版免費體驗~

相關(guān)內(nèi)容推薦：

SQL Prompt 使用教程>>>

---

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點擊