SQLServer: Index付Viewをリリースするときに注意すべきこと #DB

インデックス付Viewは便利ですが、導入するときには気をつけないといけないなーという失敗をしたので書いておきます。

インデックス付Viewは、作成のSQLだけ通ればめでたしめでたしというわけにもいかないです。。
インデックス付Viewの元テーブルに更新をかけてるクエリやストアド実行時のSETオプション次第では、クエリ実行がエラー終了してしまうという恐ろしい罠があります。

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

上記2オプションが必須です。ここがOFFになってるとNG。ストアドのCREATE文を実行するときに使ったクライアント次第では自動でOFFになったりするので注意が必要です。
デフォルトはどっちもONなので、普通にSSMSからCREATEしている分には問題ないと思います。

インデックス付Viewの元テーブルを使っていて、かつ更新があり、かつオプションがインデックス付View的にNGなストアド一覧を見つけ出すには↓のSQLを実行すればOKだと思います。

SELECT OBJECTPROPERTY(OBJECT_ID(ROUTINE_Name), 'ExecIsQuotedIdentOn') AS 'ExecIsQuotedIdentOn'
    ,OBJECTPROPERTY(OBJECT_ID(ROUTINE_Name), 'ExecIsAnsiNullsOn') AS 'ExecIsAnsiNullsOn'
    ,*
FROM information_schema.routines
WHERE routine_definition LIKE '%テーブル名%'
    AND routine_type = 'procedure'
    -- ANSI_NULLS または QUOTED_IDENTIFIER オプションが0のものに限定（=index付Viewの元テーブルに対して更新かけるようなストアドだとエラー）
    AND (
        OBJECTPROPERTY(OBJECT_ID(ROUTINE_Name), 'ExecIsQuotedIdentOn') = 0
        OR OBJECTPROPERTY(OBJECT_ID(ROUTINE_Name), 'ExecIsAnsiNullsOn') = 0
        )
    -- 更新かけてるストアドに限定（SELECTは問題なし)
    AND (
        routine_definition LIKE '%insert %'
        OR routine_definition LIKE '%update %'
        OR routine_definition LIKE '%delete %'
        OR routine_definition LIKE '%drop %'
        OR routine_definition LIKE '%truncate %'
        )
ORDER BY CREATED DESC

以上、同じ罠にかかる人がでてこないように。。

※さらに詳しくは↓を参照ください。
https://msdn.microsoft.com/ja-jp/library/ms191432.aspx