SQL Prompt使用教程：為什么不要創建將ANSI_PADDING設置為OFF的列？

---

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

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

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

什么是ANSI填充？為什么？

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

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

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

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

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

那么，規則是什么？

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

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

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

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

您會看到以下消息：

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

結果是這樣的：

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

第二個結果來自關閉ANSI_PADDING時創建的表?？蔀榭盏牡谝粋€CHAR列已被修剪。具有NOT NULL約束的CHAR列用空格填充。無論是否可以為空，VARBINARY列都修剪了尾隨零。VARCHAR列修剪了尾隨空格。

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

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

查找使用ANSI_PADDING關閉創建的異常列

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

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處于打開狀態，則為1；如果關閉，則為0。該查詢將非常快速地告訴您數據庫是否有設置為ANSI_PADDING off的異常列（只需去掉第一行“USE tempdb”）。

結論

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

本教程內容到這里就完結了，感興趣的朋友可以繼續關注慧都網了解更多產品資訊~您也可以下載SQL Prompt試用版免費體驗~

相關內容推薦：

SQL Prompt 使用教程>>>

---

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