none
Viewで参照するテーブルに列を追加した場合の再構築 RRS feed

  • 質問

  • ※もしかしたら、もう直っている問題かもしれません※
     最後にこの問題が発生したのは3年くらい前です。

    Viewで、参照テーブルに列を追加(Alter table add)したらView結果の値がズレました

    (違う列名の値返却。文字なのに違う列の数値になる等)

    10以上のデータベースを運用しているのですが、
    この列結果のズレは特定のデータベースの時だけ起きました。
    この動作は5年くらい続きましたので、2012年くらいから起きていました。
    その都度、Viewを手動で再構築(Drop Create)して直しました。

    最近は列を追加した直後にViewを手動で再構築しているので問題は起きていません。


    例えばOracleだったら9iなどの相当古いバージョンの時点でも、
    Viewなどが参照するテーブルに変更があった場合は
    Oracleが勝手に再構築が必要な状態と判断して、
    次回アクセス時に再構築くれたので、
    列追加とView再構築を気にしたことが無かったのですが、
    Azure SQL databaseはテーブルに列を追加してもViewを再構築しないのでしょうか?仕様でしょうか?

    2020年11月14日 11:57

回答

  • View で参照しているテーブルに対して、列を追加したとしても、Viewの中で参照する列を明示的にしている場合、特に違う列名の値をアウトプットとして返却することはないかと思います。

    create table tab001 (c1 int, c2 nvarchar(50), c3 nvarchar(50) CONSTRAINT [PK_tab001] PRIMARY KEY CLUSTERED ([c1] ASC))
    go
    insert into tab001 values (1,N'TEST',N'TEST'),(2,N'TEST2',N'TEST2'),(3,N'TEST3',N'TEST3'),(4,N'TEST4',N'TEST4')
    go
    create view vwtab001 as
    select c1, c2 from tab001
    where c1 in (2,3)
    go
    select * from vwtab001
    go
    alter table tab001 add c4 nvarchar(50)
    go
    select * from vwtab001
    go
    select * from tab001
    go

    しかしながら、Viewの中で参照する列をアスタリスク '*' で指定している場合は、問題が発生する可能性があります。

    そのため、上記のようなViewを作成している場合は、Viewの参照元のテーブルに対して、列の追加/削除を実施した場合、sp_refreshview コマンドを実行し、Viewを最新化する必要があるかと思います。

    create table tab002 (c1 int, c2 nvarchar(50), c3 nvarchar(50) CONSTRAINT [PK_tab002] PRIMARY KEY CLUSTERED ([c1] ASC))
    go
    insert into tab002 values (1,N'TEST',N'TEST'),(2,N'TEST2',N'TEST2'),(3,N'TEST3',N'TEST3'),(4,N'TEST4',N'TEST4')
    go
    create view vwtab002 as
    select * from tab002
    where c1 in (2,3)
    go
    select * from vwtab002
    go
    alter table tab002 add c4 nvarchar(50)
    go
    select * from vwtab002
    go
    select * from tab002
    go
    EXEC sp_refreshview vwtab002
    go
    select * from vwtab002
    go
    2020年11月15日 5:20

すべての返信

  • View で参照しているテーブルに対して、列を追加したとしても、Viewの中で参照する列を明示的にしている場合、特に違う列名の値をアウトプットとして返却することはないかと思います。

    create table tab001 (c1 int, c2 nvarchar(50), c3 nvarchar(50) CONSTRAINT [PK_tab001] PRIMARY KEY CLUSTERED ([c1] ASC))
    go
    insert into tab001 values (1,N'TEST',N'TEST'),(2,N'TEST2',N'TEST2'),(3,N'TEST3',N'TEST3'),(4,N'TEST4',N'TEST4')
    go
    create view vwtab001 as
    select c1, c2 from tab001
    where c1 in (2,3)
    go
    select * from vwtab001
    go
    alter table tab001 add c4 nvarchar(50)
    go
    select * from vwtab001
    go
    select * from tab001
    go

    しかしながら、Viewの中で参照する列をアスタリスク '*' で指定している場合は、問題が発生する可能性があります。

    そのため、上記のようなViewを作成している場合は、Viewの参照元のテーブルに対して、列の追加/削除を実施した場合、sp_refreshview コマンドを実行し、Viewを最新化する必要があるかと思います。

    create table tab002 (c1 int, c2 nvarchar(50), c3 nvarchar(50) CONSTRAINT [PK_tab002] PRIMARY KEY CLUSTERED ([c1] ASC))
    go
    insert into tab002 values (1,N'TEST',N'TEST'),(2,N'TEST2',N'TEST2'),(3,N'TEST3',N'TEST3'),(4,N'TEST4',N'TEST4')
    go
    create view vwtab002 as
    select * from tab002
    where c1 in (2,3)
    go
    select * from vwtab002
    go
    alter table tab002 add c4 nvarchar(50)
    go
    select * from vwtab002
    go
    select * from tab002
    go
    EXEC sp_refreshview vwtab002
    go
    select * from vwtab002
    go
    2020年11月15日 5:20
  • プロシージャーに sp_refreshview があったとは知りませんでした!勉強になります!ありがとうございます!

    私の遭遇した列返却値ズレは*ではなく列名指定で、

    例えばですが
    select
     会社名,
     金額
    from
     問題のView

    のような内容で(実際は列はもっと多いです)

    突然会社名の列に金額が設定されて障害になりまして、
    しかも特定のDBインスタンスの時だけ高確率で起きました。

    昔OracleMaster試験の勉強をしたときにテーブル列追加の後は再構築しないと列結果がズレる(Oracleは自動で再構築する)と習ったので、同じ現象だと思い、

    SQL Serverの勉強を多少はしましたが、たまたま読んだ本に再構築の記述を見たことがなかったので、
    今回フォーラムに投稿してみた次第です。

    プロシージャーを一通り勉強してみます!

    2020年11月15日 6:49