Indexed views in sql server Part 41 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 41 of sequel
server in this session we'll learn about
indexed views before continuing with the
session I strongly recommend to watch
parts 39 and 40 of this video series so
what is an indexed view or what actually
happens when you create an index on a
view as standard or non indexed view is
just a stored sequel query when we try
to retrieve data from this view the data
is actually retrieved from the
underlying base tables so a view is just
a virtual table it doesn't really store
any data by default however this default
behavior can be changed by creating an
index on that view so once we create an
index on a view the view gets
materialized
this means the view is now capable of
storing data in sequel server
we call these indexed views and in
Oracle they're termed as materialized
views let's look at an example
I have product details table here which
has got Product ID name and unit price
columns this table stores the master
product details on the right I have
product sales table which stores the
sales of products for example I have
sold 10 quantity of product with ID is
equal to 1 in this case
I sold 10 books and the second row says
I have sold 23 pencils because 3 is
pencils ok so basically this table
stores the product sales data now based
on these two tables I want you to create
a view which can result this aggregated
data that you can see on the right hand
side ok so I want you to write a view
which can actually return the total
sales and total transactions by product
so after selling books what is the total
amount of sales that I made and how many
times did I actually sell books ok so if
you look at books total transactions are
for meaning in
the product sales table we should have
made for transactions as far as books
are concerned the books ID is 1 so there
should be 4 entries for product ID with
1 so 1 2 3 4 so total transactions for
books is 4 ok so now let's create a view
which can produce this output now we
wanted to create an index on this view
so it can get materialized so obviously
if you want a view to be indexed then
there are certain rules that you should
be following okay which we'll talk about
now all right so we want to produce this
view that you can see on the right-hand
side and obviously if you look at the
view itself I want the name total sales
and total transactions 3 columns there
ok so the first thing is obviously when
you're creating this view you know if
you look at the implementation of the
view here we are saying create view view
name and we are using with schema
binding option ok now the first rule is
that the view should be created with
schema binding option if you are
planning to create an index on that view
okay now we have already spoken about
using schema binding option and we have
used the schema binding option with
functions and stored procedures as well
ok so if you create a function or a
stored procedure or a view that schema
binding option then you cannot change
the underlying objects in any way that
could affect that function stored
procedure our view that's what schema
binding option is for so if you are
planning to create an index on a view
then that view should be created with
schema binding option that's the first
row so the second row if an aggregate
function is used in the Select list so
if you look at this function I mean this
view we're actually using an aggregate
function sum and why we are using that
because we want to compute the total
sales made so we are using the sum
function and obviously if you want to
calculate the total sales how will you
calculate that we will multiply the
quantity sold with unit price so here is
the quantity and unit prices in the TBL
products table so we have to multiply
there
unit price with quantity sold and then
sum that up for each row in this product
sales table so that's the reason why we
are using the aggregate function and we
are multiplying quantity sold with unit
price now let's say for some reason you
know product ID or quantity sold is now
and then when you when you multiply that
you know this expression could return
now so the second rule states that if
you use an aggregate function in the
Select list and if that aggregate
function is making use of an expression
and if there is a possibility for that
expression to become null then you
should specify a replacement value okay
so if this expression becomes null what
what is the replacement value that I
want to give to this sum function
instead of giving it now I want to give
0 okay and how am I doing that I'm using
is null function again we have spoken
about different ways to replace null
values in this video series okay
apart from null you can also use case
statement and colace functions we have
spoken about them extensively in a
previous session in this video series
okay so the second rule is that if an
aggregate function in the Select list
references an expression and if there is
a possibility for that expression to
become null then a replacement value
should be specified and here we are
using is now and obviously we are giving
as total sales an earliest name for that
column all right and another important
rule is if you use group by in your view
here we are using that because we want
to group by the product the total sales
and total transactions so we are using
group by so if you use grew by within
your view then the view select list must
contain a count of big function count
underscore big expression okay so
obviously we actually need to count the
total number of transactions here so we
are using count of underscore big you
can also use count
function but the problem is if you are
planning to create an index it view you
cannot use count you will have to use
count underscore big function okay and
the base tables in the view this this is
the last rule the base table in the view
should be referenced with two path name
so here we are this view has two base
tables what are those TBL product sales
and TBL product so when you are
referring these when you are referring
to these two tables in this view you
should use the two partner in the schema
name and the table name you cannot use
one partner images without schema or you
cannot use a three part name or a fourth
part name you will have to use only the
two part name this schema our name dot
the table name okay these are only a few
of the rules you know but for the
complete list of rules please visit this
Microsoft URL please visit MSDN at this
URL there are a whole bunch of them but
for the purposes of this demonstration
to create this view it's enough if we
satisfy these rules all right so let's
create this view in action so we exactly
have the same view create view view name
with schema binding option as we need
the name of the product and we want to
sum the quantity sold multiplied by unit
price and then we want total
transactions you know we are using count
function count underscore big function
for that from product sales joining with
TBL product table on the join condition
is obviously the product ID which is
common column between those two tables
and we are grouping by name again if
you're new to you know using group by
class we have recorded a video on this
as well in the previous sessions of this
video series please check that part so
let's execute this command completed
successfully which means this view is
created now let's refresh the views
folder and we should see the view so
obviously to select the data from that
view we use select star from that view
let's execute that so we get the data as
expected okay so now we want to create a
view
on this sorry we want to create an index
on this view so if you look at you know
when we execute this particular
statement look at this we have just
created that view and we are executing
the Select statement on that view so
when we execute this query what is
actually happening as it stands right
now this particular view is not storing
any data so when we execute select star
from this view what is actually
happening behind the scenes the database
engine will actually execute this select
statement which means the query has to
go back to these base tables and then it
has to compute you know the sum the
total transactions everything and it has
to group by them okay so it has to every
time you say select star from this view
it has to every time compute those so
obviously if your product sales table is
huge because just imagine you know
companies like Amazon Microsoft etc they
will be making millions and millions of
transactions so if you want to sum them
up every time you execute it very just
imagine how much time it's going to take
to compute those reserves okay but here
since we haven't created an index on
this view you know the query will have
to go back to the base tables and and
evaluate this expressions every time you
execute select star from this view we
can change that by creating an index on
the view okay so to create an index on
the view we are saying create unique
clustered index this is the name of the
index on this view and remember the
first index that you create on a view
should be a unique clustered index and
it makes sense because this view itself
as it stands right now it doesn't it
doesn't store any data so you cannot
create a non-clustered index you know if
it doesn't already have any data at all
in that view so the first index that you
will have to create should be a unique
clustered index okay so create unique
clustered index and we are giving it a
meaningful name on this view on this
column
on the product name column okay so let's
create that view index execute that
command completed successfully so if we
expand this view indexes folder you
should see the index that we have just
created so now when I execute this query
it will return that data from that index
itself it doesn't have to go back to the
base tables to compute that okay so any
time a transaction is made and if there
is a row entered into this table the
view is also recomputed and updated so
anytime people issues the select star
from that view you know the calculations
are done pretty fast and it can
significantly improve the performance of
your query okay and basically indexes
are suitable at places where the data is
not changed heavily okay so these index
views are ideal for OLAP systems in a
data warehouse environment systems where
the data is not changed you know on
where the data is not frequently changed
mainly the data in a data warehouse is
used for reporting and analysis purposes
okay
maybe every week or every night they
just you know batch load the data and
then it gets reprocessed but then the
best way to load the data when you have
index views is to basically drop those
index views load the data and then
recompute or recreate those index views
which will definitely increase you know
reduce the amount of time that it will
take to process the data so this index
adverse are suitable for environments
where the data doesn't frequently change
but for OLTP systems index views might
affect the performance because you know
they change on a constant basis whenever
a new transaction is made and we would
have to continuously readjust this you
know index view which could actually
affect the performance so that's why you
know and another thing to keep in mind
is that the cost of maintaining indexed
views is much more than the cost of
maintaining table view table indexes
okay so mostly this index views are used
in a data warehousing and wire
and rather than in an OLTP system on the
slide you can find resources for asp.net
c-sharp and sequel server interview
questions if you want to receive email
updates when new videos are posted
please subscribe to my channel that's it
for today thank you for listening have a
great day