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

---

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

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

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

什么是ANSI填充？為什么？

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

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

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

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

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

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

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

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

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

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

您會(huì)看到以下消息：

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

結(jié)果是這樣的：

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

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

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

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

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

在訪問表時(shí)，無論您對(duì)ANSI_PADDING進(jìn)行了何種設(shè)置，查詢行為都是一致的。該設(shè)置將保留在表列中，而連接設(shè)置將被忽略。無論使用何種連接設(shè)置訪問“舊版”數(shù)據(jù)庫(kù)，該數(shù)據(jù)庫(kù)都能始終如一地運(yùn)行。我們可以通過查詢?cè)獢?shù)據(jù)來檢查臨時(shí)表發(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ù)庫(kù)是否有設(shè)置為ANSI_PADDING off的異常列（只需去掉第一行“USE tempdb”）。

結(jié)論

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

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

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

SQL Prompt 使用教程>>>

---

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