SQL Prompt是一款實用的SQL語法提示工具。它根據(jù)數(shù)據(jù)庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設(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)致表中的某些列從那時起奇怪地或不一致地處理某些字符串或二進制數(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)，必須對此進行更改。NIST測試套件檢查是否始終填充CHAR數(shù)據(jù)類型，并且對于CHAR或VARCHAR數(shù)據(jù)類型，用戶輸入的尾隨空格都不會被截斷。SQL Server決定，為了遵守規(guī)則，將隨數(shù)據(jù)一起檢索任何尾隨空格（無論是故意還是作為填充自動添加的），對于二進制數(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的定義大小。二進制數(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()功能即可進行字符串連接。避免必須使用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ù)時被填充，并且隨后未進行修剪（與ANSI標(biāo)準(zhǔn)相同的行為）
• 在檢索時會修剪可空的CHAR和BINARY列（因此，當(dāng)然在插入時不再填充）。您會丟失任何尾隨空格或故意添加的零
• 檢索時會修剪VARBINARY和VARCHAR列，因此您會丟失任何尾隨空格或故意添加的零

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

您會看到以下消息：

結(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進行了何種設(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 使用教程>>>