創建沒有冗餘程式碼的索引

作為我的數據庫項目的一部分，我需要一個儲存過程中的簡單索引創建腳本。

• 如果有問題的索引已經存在，我想重新創建它，以確保沒有其他程序更改了有問題的索引。
• 我想使用重新創建現有索引CREATE INDEX的選項；WITH (DROP_EXISTING = ON)所以我可以利用微軟提到的性能優勢。
• 如果索引不存在，我想創建它。
• 當有問題的索引不存在時使用WITH (DROP_EXISTING = ON)失敗，所以我不能簡單地包含該選項而不檢查索引是否存在。
• 話雖如此，我不希望需要重複創建索引的命令的解決方案；這可能會導致維護問題（有人編輯了命令的第一個版本，但沒有編輯第二個，我會根據它是否已經存在而得到不同的索引）。

這是一些範常式式碼。理想情況下，這將是所需的程式碼；但是，如果索引不存在，它會失敗：

CREATE NONCLUSTERED INDEX [IX_a]
               ON [dbo].[animals]([BioNr] ASC)
               INCLUDE([ID], [Currency]) WITH (DROP_EXISTING = ON);                 

如果索引不存在，則此程式碼有效，但如果索引確實存在，則不會替換該索引：

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a')
   CREATE NONCLUSTERED INDEX [IX_a]
               ON [dbo].[animals]([BioNr] ASC)
               INCLUDE([ID], [Currency]);      

此程式碼有效，但要求索引創建程式碼位於兩個位置；這允許將來的編輯破壞腳本的意圖，從而根據它是否已經存在而產生不同的索引：

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a')
BEGIN
       CREATE NONCLUSTERED INDEX [IX_a]
               ON [dbo].[animals]([BioNr] ASC)
               INCLUDE([ID], [Currency])  WITH (DROP_EXISTING = ON);      
END
ELSE
BEGIN
       CREATE NONCLUSTERED INDEX [IX_a]
               ON [dbo].[animals]([BioNr] ASC)
               INCLUDE([ID], [Currency]);      
END

還有其他選擇嗎？

在 SQL Server 2016 中，這非常簡單，您只需在使用簡單腳本或享受您實際觀察到的任何性能之間做出選擇DROP_EXISTING（這是可以量化的嗎？您是否測試過？）。

CREATE TABLE dbo.what(i int, INDEX x(i));
GO

DROP INDEX IF EXISTS dbo.what.x;
GO

CREATE INDEX x ON dbo.what(i DESC);
GO

所以，你的要求是：

• 創建列表中的所有索引，刪除和替換現有索引。
• 如果索引確實存在，請使用WITH (DROP_EXISTING = ON).
• 無需重複創建索引的程式碼。

我只看到兩個選項可以做到這一點：

選項 1：動態 SQL

建構基本CREATE INDEX命令；然後，如果索引存在，則附加該DROP_EXISTING子句。

DECLARE @stmt NVARCHAR(MAX);

SET @stmt = N'
CREATE NONCLUSTERED INDEX [IX_a]
      ON [dbo].[animals]([BioNr] ASC)
      INCLUDE([ID], [Currency])'
+ CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a')
   THEN N' WITH (DROP_EXISTING = ON)'
   ELSE N''
 END
+ N';'
;

EXECUTE sp_executesql @stmt;

顯然，這個解決方案有它自己的一些問題。通常，您必須將語句維護為字元串。這裡最大的問題是您必須記住將語句中應該出現的任何單引號加倍。由於範例語句沒有，因此這可能不是問題，但必須注意。WITH特別是，如果需要設置其他選項，則必須更改該語句。同樣，在此範例中，這不是問題。如果所有語句都包含現有WITH選項，那麼這也將是一個大問題（儘管該語句需要調整：

SET @stmt = N'
CREATE NONCLUSTERED INDEX [IX_a]
      ON [dbo].[animals]([BioNr] ASC)
      INCLUDE([ID], [Currency]) WITH (DATA_COMPRESSION = PAGE'
+ CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a')
   THEN N', DROP_EXISTING = ON'
   ELSE N''
 END
+ N');'
;

選項 2：創建一個“虛擬”索引

請注意，我不建議這樣做；實際上，它至少與僅擁有兩個CREATE INDEX語句副本一樣難以維護，並且會導致系統上的額外工作。但是，如果您希望索引幾乎總是存在，那麼它可能會很有用。

首先，您使用您想要的名稱創建一個索引（列無關緊要）；然後，將其替換為您實際想要的索引。

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[animals]') AND name = N'IX_a')
   CREATE NONCLUSTERED INDEX [IX_a]
          ON [dbo].[animals] ([BioNr] ASC);

CREATE NONCLUSTERED INDEX [IX_a]
      ON [dbo].[animals]([BioNr] ASC)
      INCLUDE([ID], [Currency]) WITH (DROP_EXISTING = ON);

正如我所說，我不會推薦它，但它會（至少在技術上）滿足您的要求，所以我想我應該提到它。

最後說明

可能值得發布一個CONNECT項目，要求DROP_EXISTING = ON無論索引是否存在都允許（如果你找不到現有的，至少）。如果索引不存在，則簡單地忽略該選項似乎是完全合理的。

引用自：https://dba.stackexchange.com/questions/179582