Materialized Views
-
Does Materialized view the same as indexed view in SQL Server? When the tables changes, do I have to re-compile the view?
-
Indexed views are the "equivalent" to materialized views. They have to be created with the "with schemabinding" option and you can only change the columns that are not associated with the view if you try to change any of the ones in the view you'll get and error. Should you need to change them the only way is to drop the index, drop the view modify your table and finally recreate the view
Cheers,
* Noel -
...from my experience you cannot change anything related to the base tables - not even columns not referenced in the view.
-
... from mine you can:
create table TDemo ( col1 int primary key, Col2 decimal(10,2), Col3 int, ColFree varchar(20))
go
insert TDemo( Col1, Col2, Col3, ColFree)
select 1, 10.1, 11, 'ABCD'
union all select 2, 20.2, 22, 'ABCD'
union all select 3, 30.3, 33, 'ABCD'
union all select 4, 40.4, 44, 'ABCD'
union all select 5, 50.5, 55, 'ABCD'
union all select 6, 10.1, 11, 'ABCD'
union all select 7, 20.2, 22, 'ABCD'
union all select 8, 30.3, 33, 'ABCD'
union all select 9, 40.4, 44, 'ABCD'
union all select 10, 50.5, 55, 'ABCD'
union all select 11, 10.1, 11, 'ABCD'
union all select 12, 20.2, 22, 'ABCD'
union all select 13, 30.3, 33, 'ABCD'
union all select 14, 40.4, 44, 'ABCD'
union all select 15, 50.5, 55, 'ABCD'
go
set ansi_warnings on
set ansi_padding on
create view dbo.IDXView
with schemabinding
as
select Sum(Col2) as Total, Col3, count_big(*) as Cnt
from dbo.TDemo
group by Col3
go
create unique clustered index vw_ix on IDXView(Col3)
go
-- This succeeds
Alter Table TDemo
alter column ColFree varchar(200) -- increase size
go
-- This Fails
Alter Table TDemo
alter column Col3 decimal(15,4) -- increase range
go
Cheers,
* Noel -
Yes indeed! Thank you for pointing out another BUG in my db tool...
They have some splainin' to do!
Thanx!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply