Indexed Views, Space Used.

Potentially stupid question but here goes:
Does an indexed view require extra space (due to data being copied) or
is it just a logical construct that uses existing table data?
Thanks, TFD.> Does an indexed view require extra space (due to data being copied) or
> is it just a logical construct that uses existing table data?
A standard view is a logical construct that is temporarily materialized when
a statement referencing the view is executed. An indexed view is
materialized and stored on disk at the time the clustered index is created
on the view. So, yes, it requires additional disk space to hold the
clustered index.
You might find this white paper useful
http://www.microsoft.com/technet/pr.../ipsql05iv.mspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1137686494.821811.34620@.g47g2000cwa.googlegroups.com...
> Potentially stupid question but here goes:
> Does an indexed view require extra space (due to data being copied) or
> is it just a logical construct that uses existing table data?
> Thanks, TFD.
>|||> So, yes, it requires additional disk space to hold the clustered index.
And further to disk space, there is also additional I/O when you issue DML
against the base table (since it has to mirror those changes in the
materialized view(s)).
A|||Is there a way to determine the space used by a materialized view
(after the fact) like you can do for a table?|||Sure. You can use sp_spaceused for indexed views.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1137710309.602103.184880@.z14g2000cwz.googlegroups.com...
> Is there a way to determine the space used by a materialized view
> (after the fact) like you can do for a table?
>|||CREATE VIEW V1
AS
SELECT a, SUM(b) AS Revenue
FROM MyTable
GROUP BY a
GO
sp_spaceused 'V1'
and the result comes back as
Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
Views do not have space allocated.
'|||Ummm, that's not an indexed view.
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1137725398.306412.146190@.g43g2000cwa.googlegroups.com...
> CREATE VIEW V1
> AS
> SELECT a, SUM(b) AS Revenue
> FROM MyTable
> GROUP BY a
> GO
>
> sp_spaceused 'V1'
>
> and the result comes back as
> Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
> Views do not have space allocated.
> '
>|||Well, at least create the clustered index. :) As soon as it's created the
system procedure will work.
ML
http://milambda.blogspot.com/|||And further to that, you'll need to create the view WITH SCHEMABINDING
in order to create the clustered index on it, the clustered index must
be a *unique* clustered index, you must use 2 part names in the view
(i.e. you must specify the owner of "MyTable"), and the owner of the
view & the base tables referenced in the view must all be the same. For
example:
use tempdb;
go
create table dbo.SalesAmounts
(
InvoiceID int identity(1,1) primary key clustered,
SalesPerson varchar(10) not null,
Amount smallmoney not null
);
go
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Fred', 10.90);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Fred', 17.45);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Fred', 3.95);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Bill', 78.85);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Bill', 26.50);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 16.20);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 12.10);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 18.90);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 9.95);
go
create view dbo.SalesAggregates with schemabinding
as
select SalesPerson, sum(Amount) as Revenue, count_big(*) as NumSales
from dbo.SalesAmounts
group by SalesPerson;
go
create unique clustered index UX_SalesAggregates_SalesPerson on dbo.SalesAgg
regates (SalesPerson);
go
select InvoiceID, SalesPerson, Amount from dbo.SalesAmounts;
select SalesPerson, Revenue, NumSales from dbo.SalesAggregates;
go
exec sp_spaceused 'dbo.SalesAmounts';
exec sp_spaceused 'dbo.SalesAggregates';
go
drop view dbo.SalesAggregates;
drop table dbo.SalesAmounts;
go
There are quite a few caveats and considerations around indexed views,
for more info see BOL
(http://msdn.microsoft.com/library/e...des_06_9jnb.asp).
*mike hodgson*
http://sqlnerd.blogspot.com
ML wrote:

>Well, at least create the clustered index. :) As soon as it's created the
>system procedure will work.
>
>ML
>--
>http://milambda.blogspot.com/
>