Indexed (materialized) views in SQL Server,different with Oracle (materialized) views

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.