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

インデックス付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