Thanks to
MS sql could have materialized views ,similar with oracle MVs, using indexed views.
what is going on ? are they same thing ?
Here we go :
(1) general demo tables and rows
/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch
"Indexed views"
2011-03-24
*****************************************************/
set nocount on
go
set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go
create table dbo.Clients
(
ClientId int not null,
ClientName varchar(32),
constraint PK_Clients
primary key clustered(ClientId)
)
go
create table dbo.Orders
(
OrderId int not null identity(1,1),
Clientid int not null,
OrderDate datetime not null,
OrderNumber varchar(32) not null,
Amount smallmoney not null,
Placeholder char(100) not null
constraint Def_Orders_Placeholder
default 'a',
constraint PK_Orders
primary key clustered(OrderId)
)
go
with CTE(Num)
as
(
select 0
union all
select Num + 1
from CTE
where Num < 100
)
insert into dbo.Clients(Clientid, ClientName)
select Num, 'Client ' + convert(varchar(32),Num)
from CTE
option (MAXRECURSION 0)
go
with CTE(Num)
as
(
select 0
union all
select Num + 1
from CTE
where Num < 100000
)
insert into dbo.Orders(Clientid, OrderDate, OrderNumber, Amount)
select
Num % 100,
DATEADD(day,-Num % 365, GetDate()),
'Order: ' + convert(varchar(32),Num),
Num % 100
from CTE
option (MAXRECURSION 0)
go
(2) -- no views select query
select
c.ClientId, c.ClientName,
count(o.OrderId) as [NumOfOrders],
sum(o.Amount) as [TotalAmount]
from
dbo.Clients c join dbo.Orders o on
c.ClientId = o.ClientId
group by
c.ClientId, c.ClientName
having
sum(o.Amount) > 90000
go
(9 row(s) affected)
Table 'Clients'. Scan count 0, logical reads 200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 1823, physical reads 2, read-ahead reads 1798, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(3) creating indexing view
create view dbo.vClientWithOrders(
ClientId, ClientName,
NumOfOrders, TotalAmount
)
with schemabinding
as
select
c.ClientId, c.ClientName,
count_big(*) as NumOfOrders,
sum(o.Amount) as TotalAmount
from
dbo.Clients c join dbo.Orders o on
c.ClientId = o.ClientId
group by
c.ClientId, c.ClientName
go
create unique clustered index
IDX_vClientWithOrders_ClientId
on dbo.vClientWithOrders(ClientId)
go
--run again same query
(9 row(s) affected)
Table 'vClientWithOrders'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- only query from the view
select *
from dbo.vClientWithOrders
where TotalAmount > 90000
go
(9 row(s) affected)
Table 'vClientWithOrders'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(4) With the standard edition of SQL Server, you can use ---with (noexpend)
select *
from dbo.vClientWithOrders with(noexpend)
where TotalAmount > 90000
go
and get the same result as above one
so,. let close it with MS web site words
Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution. The view does not have to be referenced in the query for the optimizer to consider that view for a substitution.
Comments