SQL SERVER 重組含有特殊字元的索引時遇到“關鍵字 'with' 附近有文法錯誤.”

案例描述

    這是在索引重組過程中遇到的有意思的錯誤案例，搜尋了一下也沒有看到相關資料，估計我第一個碰到這類錯誤的人（It's just a joke）。具體情況是YourSQLDba在做維護資料庫索引時遇到了索引重組錯誤，然後我排查時就發現了這個案例。我下面用一個簡單的測試例子示範一下具體情況。

資料庫版本： SQL SERVER 2005

因為YourSQLDba生成重組索引語句時，都會在索引名稱上加上[].如果用下面SQL語句進行索引重組，就會遇到這個錯誤

ALTER INDEX [[IDX_NAME]] ON [dbo].[TEST]

Reorganize With (LOB_COMPACTION = On);

消息 102，級别 15，狀态 1，第 1 行

'.' 附近有文法錯誤。

消息 319，級别 15，狀态 1，第 2 行

關鍵字 'with' 附近有文法錯誤。如果此語句是公用表表達式或 xmlnamespaces 子句，那麼前一個語句必須以分号結尾。

<a href="http://images.cnitblog.com/blog/73542/201503/090007338832557.png"></a>

我們嘗試去掉一對[],此時就會報"找不到索引'IDX_NAME'"的錯誤。其實這個好了解,因為雙引号被解釋為字元串的邊界，是以[IDX_NAME]才是真正的索引名稱，但是如果你重建索引時使用[IDX_NAME]， 那麼[]就變成了解釋字元邊界的字元，索引名就成了IDX_NAME，顯然就會出現找不到該索引的錯誤提示。

<a href="http://images.cnitblog.com/blog/73542/201503/090007348837728.png"></a>

消息 2727，級别 11，狀态 1，第 2 行

找不到索引 'IDX_NAME'。

如果在索引名稱上加上"",那麼索引名就成了[IDX_NAME], 那麼此時就可以成功進行索引重建。這兩者都好了解。

<a href="http://images.cnitblog.com/blog/73542/201503/090007362276472.png"></a>

而使用[[IDX_NAME]]這種寫法時，并不能完全讓資料庫引擎知道索引名為[IDX_NAME],有可能是語義解析時就有問題，這個我們也能了解，但是如果在MSSMS視窗将索引拖到查詢視窗，你會驚奇的發現索引名變成了[[IDX_NAME]]]這個鳥樣，不清楚為什麼這樣。這樣的SQL進行索引重組也是成功的。

<a href="http://images.cnitblog.com/blog/73542/201503/090007374302173.png"></a>

在SQL Server 2008下面測試，發現報錯的提示有些不一樣。如下所示：

<a href="http://images.cnitblog.com/blog/73542/201503/090007383671859.png"></a>

當然測試過程中要求設定SET QUOTED_IDENTIFIER的值為ON，關于SET QUOTED_IDENTIFIER的設定為：

     當SET QUOTED_IDENTIFIER值為ON時，雙引号内的字元被當作是資料庫對象。就是說雙引号" "和辨別符[]效果是一樣樣的，他們都表示引用的字元是資料庫對象。單引号'表示字元串的邊界。

     當SET QUOTDE_IDENTIFIER OFF時，雙引号被解釋為字元串的邊界，和單引号的作用是類似的。就是說雙引号"不能當做辨別符使用，但是可以當做字元邊界，和單引号'的效果是一樣樣的。

解決方法：

    既然搞清楚了原因，那麼就很容易解決問題了。删除索引後，然後重建索引時取消雙引号以及[]就OK了。