// Net planet-es V2

SQL Server 2005で VIEW の定義方法や使用方法を掲載します。

動作確認は SQL Server 2005 Developer Edition で行っています。

1. VIEW を定義する

VIEW を定義し、変更、削除、検索するサンプルを掲載します。テーブル定義と同様にCREATE VIEW を使用して作成。ALTER文で変更、DROPで削除を行います。VIEW定義でENCRYPTION オプションを使用するとVIEW定義を暗号化できます。

-- VIEW を定義する
IF OBJECT_ID('dbo.vArticles') IS NOT NULL
  DROP VIEW dbo.vArticles
GO

CREATE VIEW dbo.vArticles
AS 
-- VIEW を定義する
SELECT a.ArticleID
      ,a.Title
      ,a.Abstract
      ,(SELECT COUNT(*) FROM dbo.Comment c WHERE c.ArticleID = a.ArticleID) NumOfComments
  FROM dbo.Article a
GO

-- VIEW にクエリを出す
select *
  from dbo.vArticles
 order by NumOfComments desc

-- VIEW を変更する
ALTER VIEW dbo.vArticles
AS 
-- VIEW を定義する
SELECT a.ArticleID
      ,a.Title
      ,a.Abstract
      ,a.ReleaseDate
      ,(SELECT COUNT(*) FROM dbo.Comment c WHERE c.ArticleID = a.ArticleID) NumOfComments
  FROM dbo.Article a
GO

-- VIEW を削除する
DROP VIEW dbo.vArticles

2. VIEWのメタデータを取得する

VIEWの定義情報はsys.sql_modulesを使用します。スキーマやカラム情報はsys.schemas, sys.columns を使用します。依存するオブジェクトを使用する場合は、 sys.sql_dependencies カタログビューを使用します。

-- VIEWの定義を表示する
select definition
  from sys.sql_modules
 where object_id = object_id('dbo.vArticles')

-- VIEWのスキーマ情報を表示する
    select s.name [schema], v.name [view]
      from sys.views v
inner join sys.schemas s
        on v.schema_id = s.schema_id
     where v.object_id = OBJECT_ID('dbo.vArticles')

-- カラム情報を表示する
    select v.name [view], c.name [column]
      from sys.views v
inner join sys.columns c
        on c.object_id = v.object_id
     where v.object_id = OBJECT_ID('dbo.vArticles')

-- VIEWが依存するオブジェクトを列挙する
    select v.name [view], OBJECT_NAME(referenced_major_id) ReferencedObject
      from sys.sql_dependencies d
inner join sys.views v 
        on d.object_id = v.object_id
 where d.object_id = OBJECT_ID('dbo.vArticles')

-- VIEW が参照しているオブジェクトが更新された場合に
-- VIEWのメタデータを更新する
EXEC sp_refreshview 'dbo.vArticles'

3. INDEX付きVIEWを定義する

ビューの作成時にWITH SCHEMABINDINGオプションを使用することで、インデックス化VIEWを作成することができます。Enterprise Edition では、クエリオプティマイザがインデックスを考慮してVIEWに対する実行プランを作成します。Standard Edition などでは自動的にオプティマイザはVIEWのインデックスを使用しないので手動でNOEXPANDテーブルヒントを指定することでVIEWのインデックスを使用するようにできます。

INDEX付きのVIEWを使用することで、集計間巣を多様しているVIEWで、元となるテーブルが頻繁に更新が発生しない場合パフォーマンスなどの場合にパフォーマンスを向上させることができる可能性があります。

IF OBJECT_ID('dbo.vArticles') IS NOT NULL
  DROP VIEW dbo.vArticles
GO

CREATE VIEW dbo.vArticles
WITH SCHEMABINDING
AS 
-- インデックスVIEW用にSCHEMABINDINGを
-- 設定してビューを定義する
SELECT c.CategoryID, c.Title, SUM(a.Votes) Votes, COUNT_BIG(*) NumOfArticles
  FROM dbo.Article a
INNER JOIN dbo.Category c
    ON a.CategoryID = c.CategoryID
 GROUP BY c.CategoryID, c.Title
GO

-- VIEWにクラスタ化インデックスを定義する
-- サブクエリが含まれている場合、OUTER結合が
-- 含まれる場合インデックスを作成できない
-- などの制限があるので注意.
CREATE UNIQUE CLUSTERED INDEX PK_vArticles
    ON dbo.vArticles (CategoryID)
GO

CREATE NONCLUSTERED INDEX IX_vArticles
    ON dbo.vArticles (Votes,NumOfArticles)
GO
-- インデックスを削除する
DROP INDEX dbo.vArticles.IX_vArticles
GO

VIEWに対してインデックスを設定する場合、OUTER　JOINやサブクエリを使用できないなど、VIEW定義のSELECT文に制限が発生するので注意が必要です。

4.まとめ

説明は以上です。動作確認をしていないので記事には掲載していませんが、VIEWにはこのほかにもパーティションビューという、異なるSQL インスタンスで分散されたVIEWを定義することもできます。BOOKS ONLINEなどで、パーティション ビューや分散パーティション ビューで調べてください。

誤り指摘点などがある場合はご連絡ください。