views in 99L

# What is a VIEW ?

victual fable -> based on the result-set of a

SgL
grey
stores
only the
Query
does not store data
physically itself (except for
Materialised views)
# characteristics a) virtual table
Rey
-

3) dynamic
) stored
query
&

9) composable
:
# Advantages
-

9 be used
simplicity >
- a view name can
exactly in
the same table
way
as a name

- once stored it used

reusability > ,
can be
again are

again (no need to revorite the same

query &
> does not take disk
2) performance -
up any
extra
data
space for storage
d) logical data
independance -> does not
change the
DIB schema
 to certain
e) security >
- one
may
restrict access rows

and columns table

of a

#
potential drawbacks : -

overhead
9 performance >
-
simple views add a
layer
of abstraction > the
-
underlying grey still runs

entirely (slow for complex views)

b update restrictions -
>
complex views often
vitare

updatable -> We cannot INSERT UPDATE ,

DELETE or

view that uses DISTINCT

from a clause
aggregate ,

functions or multiple tables

yes in
maintenance views to
add the ber
 General Synlat
(i) Creating a view :-

CREATE VIEW view-name AS

SELECT 2011 col2 , .. -

FROM fable - name

WHERE condition ;

i) Altering a view8-

ALTER VIEW vW-Active -

Cust As
CrotED Contact Phone Email
SELECT , Company Name ,
Name , ,

FROM Cuetomey
WHERE Active 1
;
=

is 8-
Dropping a view

DROP VIEW VW-Active - Cust ;

 View
when can a be
updated
I created from a
single table

(i) does not use DISMNCT , GROUP BY or

agg June
i) does not use set
operations (UNION ,
UNION ALL
,
etc.
)

is all NOT NULL columns from the base table are

included in the view

(for INSERT operations)
UPDATE vW-Active Cust -

"

SET Phone + -555-1234"

=

WHERE CurtED =
"ALFKI" ;
 Types of Views

Simple views :
1) a) created
from single base table
-

b) does not contain Agg func .,

. GROUP
BY Or DISTINCT clauses

2) allows DML operations (INSERT ,

UPDATE and DELETE) directly
CREATE VIEW VO-Active -
Cust AS
SELECT CustID ,
Company Name ,
Contact ,
Phone
FROM Customer
WHERE Active = 1
;

(i) Complex views a) created from multiple tables

-

3)
generally contains Agg func ., . GROUP
BY or DISTINCT clauses

does not allow DML operations

1)
tables
directly
>
-
ambigous across
multiple base

vW-Cust-Order
CREATE
SELECT
VIEW -

Summary AS

CustID ,
c .

Company Name c .
,

COUNT (OdeID) As Total order

S

SUM
(d Quantity & od Unitinice)
. .
As Total
Spent
From Customers <

LEFT JOIN Riders ON c . CretID = od . CretFy

LEFT JOIN [Orden Details] od ON o . Order ID = od . OrderEa
GROUP By c . Customer Fi
,
c .

CompanyName ;
 Materialised views

# What Materialised
are views ?

stores the result

physically
Unlike a standard view
set
of a

grey
on distr

represents the
snapshot of the data at a
point in
time

data is
persisted + must be
refreshedmanually or

auto .

) ,
when the underlying data
changes
# proso-a) performance improvement + for
dramatic
complex queries (involving large tables , multiple join
and agg , executing the Query and
func ·

,
storing once

the result is
far faster thanrunning the new
grey repeatedly
6) reduced load on base tables

to data
& offline access
per-computed
9) n/w efficiency in Distributed D/B

# consi a) data
latency > data
- is not real time +
-

only reflects the state

of the base tables at the
moment when it was last
refreshed
3) storage space >
- consumes
physical disk space
to store the result set which be
large
,
may

& maintenance overheads + needs to be

refreshed
 d) not always updatable >
- cannot
perform
INSERT ,
UPDATE or DELETE
operations directly on M . .
V

e) implementation complexity
# Standard views Materialized views

is virtual table -> based on

snapshots
at
of the data
result set time
of any poina
SOL in ->

t
an

stores but
gray
>
-
only
the
stored
physically
guery in the D/B

is shower data retrieval faster data access

his types - simple types >

- re
fish on Auto
Demand
complex refush on

in used to restrict from used in Data

D/B
data
Warehoming
:
# General
syntax
CREATE VIEW vi-Product -
Sales -
Materialised WITH
SCHEMABINDING AS
SELECT

p. ProductID ,
p Product Name ,
.

SUM Cod Quantity)

·
As Total Units Sold ,
COUNT-BIG(*) As Count
FROM dbo . Products p
JOIN dbo [Onder Delaib] od ON ProductED Od Product I
-
=
p
.
. .

GROUP BY Producti , Product Name

p p ;
. .

CREATE UNIQUE CLUSTERED INDEX IX-vE ProductSales

ON ro-Product Sales -
Materialized (ProductID) ;
 Complete Refresh
# concept uncates and
->
swatch
completely rebuilds the
entire M . .
V
from
#
algorithm (steps) : -

① the D/B funcates or deletes all

existing data
from
the M . V .

storage
② rexecutes the entire
defining query against the
base table

③ insents the into the

all
resulting vows M V
.
.

# : -a) when the

use cases
underlying data has
change
sig .

that do not Incremental

6) M . r .

support
Refosh
maintenance
a) during or
off-peak his
 Incremental (Fast) Refresh

only thechangesinsect ,
# concept >
-
applies
and deletes
S that
last
upd
e
since the refresh
#
algorithm (steps)8
① the D/B changehastracking mechanisms
uses
M logs) . V .

to
identify what changed in base tables

② applies only the delta changes to the M V

.
.

③ full query execution only

no >
- applies specific
incents , updates and deletes

# cases when real-time data is needed

a)
-

use near

with min .

latency
6) for figment refreshe
a) large datasets
,
where Complete Refush
is
very expensive
d) when base tables had min .

changes