IT☆ぱらだいす

ビューの作り方、ANSI_NULLS、QUOTED_IDENTIFIER、WITH(NOLOCK)

ビューの目的

ビューを作成する目的は、クエリ側の副問い合わせやテーブル結合などの複雑な記述を避けるため。

または、似たようなクエリを複数のプログラムで使用する際に、使いまわしができるようにするために作成する。

テーブル結合や副問い合わせの結果を、一つのテーブルのようなものとして扱うことができる。

ビューを作成する構文

ビューを作成する際のスイッチの意味

ANSI_NULLS

ON:NULLとの比較演算NG　必ず「IS NULL」、「IS NOT NULL」で比較。
比較演算子での演算は必ず「false」が返ってくる。
※SQL-92に準拠

OFF:NULLとの比較演算OK（=,<>）XXX = NULLができる。
※非推奨

よく、質問サイトに「NULL同士の比較演算の結果がfalseになる」という質問がされているがこのような理由で、そのような設定になっている。
NULLの比較は「IS NULL」か「IS NOT NULL」で行うこと。

QUOTED_IDENTIFIER

ON:識別子は「”」リテラルは「’」で囲む
予約語も「”」で囲めば使用できる
OFF:リテラルは「”」または「’」で囲む

基本的にソフト開発の現場では、SQLの文字リテラルは「’」で囲むため、ONを設定する。

WITH(NOLOCK)

FROM句に書いたテーブル名のあとの「WITH(NOLOCK)」はほぼ必須である。

なくても、ビューは作成できるが。

ビューを作成する際、「WITH(NOLOCK)」を指定しなかった場合。

ビューの元となるテーブルが更新されているときに、
別のクエリで更新中のビューがSELECT文で参照されたらロックされる。

それを防ぐために、更新に時間がかかるビューや、マスタ系のビューを
作成する場合は、おまじないとして必ず記述する。

ビュー作成時の注意点（2017/01/20追記）

関数の戻り値をビューの列にする場合

ビュー作成時のSELECT文のカラムにテーブルの列をそのまま書いた場合、ビューの桁数は元のテーブルのデータ型と桁数を引き継ぐ。

しかし、「FORMAT関数」などを使用した場合、関数の戻り値のデータ型の最大桁数が列のサイズとなる。

例えば、「FORMAT関数」の戻り値は、nullかnvarcharで、nvarcharの最大桁数が4000桁であるので、ビューの列の長さは4000桁となる。

例えば、datetime型のデータから年のみを取り出す場合は

と記述するが、その場合下記のような桁数となる。

これを防ぐために、「SUBSTRING関数」などを使用して、桁数を適正な値に設定する。

「CASE WHEN」で条件により別のデータ型をビューの列とする場合

例えば、「CASE WHEN」などの条件分岐で、ある項目がnullの場合、’0’を渡し、それ以外の値はある項目を渡す列がある。
もしある項目のデータ型がUNICODE文字列の「nvarchar型」の場合、’0’は通常の「varchar型」のため、ビューの列を決定できない（どちらのデータ型になるか行毎に変わるため）。

この場合、ビューの列になるデータ型を1つに絞る場合は、ある項目がnvarchar型なら、定数は「N’0’」と記述し、定数をnvarchar型に合わせる必要がある。

※SSMSでは暗黙の型変換が行われても、ビューを使用するプログラム側でエラーになるという報告がある。