お世話になっております。
Version: Microsoft SQL Server 2008 R2 (SP1)
ご相談したい内容は、
「データが頻繁に更新or挿入されるテーブルを使ってインデックス付Viewを作成しても問題ないか？」です。
https://technet.microsoft.com/ja-jp/library/ms187864(v=sql.105).aspx
こちらの「インデックス付きビューをデザインする際のガイドライン」に、
==========
インデックス付きビューは、基になるデータを更新する頻度が低い場合に最も効果的です。インデックス付きビューをメンテナンスする方が、テーブル インデックスのメンテナンスよりもコストがかかる場合があります。基になるデータを頻繁に更新する場合、インデックス付きビューの使用によるパフォーマンス向上よりも、インデックス付きビューのデータ メンテナンスにかかるコスト増加の方が大きい場合があります。
==========
とあります。ただ、個人的には、↑はcount_big(*)やsum(*)などの集計処理を含むインデックス付Viewの場合には更新コストが大きいので、頻繁に基テーブルが更新されるようなケースではインデックス付Viewは不適切な選択だ、という意味かなと解釈しております。
今回やりたいことは、ハイフン(-)が入った電話番号カラムから、ハイフンを取り除いた数値だけの電話番号カラムを作って、インデックスを張って問い合わせ処理を高速に行いたい、というものです。
計算列を作成し、計算列へのインデックス追加も検討したのですが、諸事情によりテーブルへのカラムの新規追加はできる限り避けたいです。
インデックスを作成しようと考えているViewでは、replace, castしか追加の演算として行っておりませんので、更新時のコストも問題ないのかなと考えております。
したがって、ガイドライン的にはNGな「挿入/更新が頻繁に実行されるテーブルを使ってインデックス付Viewを作成する」ですが、今回のケースでは作成しても問題ないのかな？というのが個人的な見解です。
しかしながら不安がありますので、私の認識があっているか、また、このようなケースでインデックス付Viewを作成することによる、懸念点などございましたらご教授いただけますと大変ありがたいです。
以下、私のほうで実験してみた結果です。
■ 要件
電話番号でデータを検索するシステムを作成したいが、下記2点を満たすことが必要
・カラム[tel]にはハイフン(-)または数値(0-9)のみ。ハイフンはどこに入ってもOK（例：090-0000-0000 , 03-000-0000)
・検索時の入力は数値のみで行いたい（例：09000000000 , 030000000)
-- 実験用テーブル
CREATE TABLE [dbo].[TelTest](
[pk] [int] IDENTITY(1,1) NOT NULL,
[tel] [varchar](20) NOT NULL
CONSTRAINT [PK_TelTest] PRIMARY KEY CLUSTERED
(
[pk] ASC
) ON [PRIMARY]
) ON [PRIMARY]
go
-- TelTestテーブルに電話番号（っぽいテストデータ）を100件いれる
CREATE TABLE [dbo].[TelNumber](
[num] [int] IDENTITY(1,1) NOT NULL
,[d] [datetime] NOT NULL
)
go
insert into TelNumber(d) values(getdate())
go 999
insert into TelTest (tel)
select
cast((select top (1) num from TelNumber order by newid()) as varchar)
+ '-'
+ cast((select top (1) num from TelNumber order by newid()) as varchar)
+ '-'
+ cast((select top (1) num from TelNumber order by newid()) as varchar)
go 100
--実際のデータ量は一千万件ほどを予定しており、下記SQLではインデックスが効かずとても遅いため、インデックス付Viewを作成
declare @key varchar(20)
set @key = replace((select top (1) tel from TelTest), '-', '')
--scanされるので遅い(1000件だと問題ないけど一千万件だと遅い)
select * from TelTest where Replace(tel, '-', '') = @key
go
--挿入/更新コスト確認
--insert
insert into TelTest (tel)
select
cast((select top (1) num from TelNumber order by newid()) as varchar)
+ '-'
+ cast((select top (1) num from TelNumber order by newid()) as varchar)
+ '-'
+ cast((select top (1) num from TelNumber order by newid()) as varchar)
--udpate
update TelTest set tel = '111-1111-1111' where pk = 500
go
--インデックス付Viewを作成
CREATE VIEW [dbo].[VTel]
WITH SCHEMABINDING
AS
SELECT cast(Replace(tel, '-', '') as varchar(20)) as tel2, pk
FROM dbo.TelTest
GO
CREATE UNIQUE CLUSTERED INDEX IDX_TelTest
ON dbo.vTel (tel2, pk);
GO
declare @key varchar(20)
set @key = replace((select top (1) tel from TelTest), '-', '')
--seekになる
select * from VTel with(noexpand) where tel2 = @key
go
--挿入/更新コスト確認
--insert(View作成前と大差なし）
insert into TelTest (tel)
select
cast((select top (1) num from TelNumber order by newid()) as varchar)
+ '-'
+ cast((select top (1) num from TelNumber order by newid()) as varchar)
+ '-'
+ cast((select top (1) num from TelNumber order by newid()) as varchar)
--udpate(View作成前と大差なし）
update TelTest set tel = '111-1111-1111' where pk = 500
drop view VTel
drop table TelTest
drop table TelNumber
