Advantages and disadvantages of indexes in sql server Part 38 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to prism technologies I am
venket this is path 38 of sequel server
in this session we'll learn about what
are the advantages and disadvantages of
indexes the types of queries that can
actually benefit from indexes and we'll
also cover a concept called covering
queries now before continuing with the
session I strongly recommend watch parts
35 36 and 37 of this video series so
what are the advantages of indexes now
in part 35 we have learned that index is
a used way queries to find data quickly
ok so if you look at the table that we
have here it's employees table which has
got ID first name last name salary
gender in City columns and on this table
the ID column is the primary key column
and we know that when we have a primary
key constraint on a table by default it
creates a unique clustered index on that
column so since this is a clustered
index the ID on the ID column we have a
clustered index the data is arranged in
the ascending order of the ID column but
if you look at the salary column this is
not arranged in any order because the
data is already arranged in the
ascending order of the ID column now on
the right hand side you can see an index
on the salary column okay and if you
look at the salaries they are arranged
in ascending order okay and then for
example if I want to find out who is the
employee who has got 2,500 salary you
look at this row and there is a row
address we pick up the true address and
get the row which has got 2500 which is
John's record directly okay so this is a
non-clustered index and this is the
table now let's say somebody writes a
query like this select star from TBL
employee where salary is greater than
4,000 and salary is less than 8,000 if
you look at this I want all the
employees whose salary falls between
four and eight thousand so obviously
this query can benefit from this non you
know from this index on the salary
column because salaries are in ascending
order so the database engine can quickly
narrow down
the rules that fit into this category so
if it starts at 3,500 no it doesn't fall
into this category but 4,000 falls into
this category so it starts from there
and takes everything until 8000 and then
it has the ROO addresses so it goes to
the actual table and retrieve all the
other columns that we are requesting in
the Select Clause so select statements
with a where Clause can benefit from the
right indexes even delete and update
statements can benefit now if I want to
delete a particular record let's say for
example I want to delete an employee
whose salary is 2500 now assuming that I
have a million records in this table
now obviously if I have to find all the
employees whose salaries 2,500 I'll have
to look at every record instead of that
if there is an index on the salary
column 2,500 people withdrew so or rose
with 2,500 will be present you know
consecutive and will be present in
consecutive rows so there will be
adjacent to each other and it is easy to
pick those row addresses and then go to
the base table and delete these rows and
along the same lines update queries can
also benefit okay because we are
updating the salaries of all people
whose salary is currently 7,500 where we
are updating that to 900 so all people
with 7500 will appear in the index next
to each other so get all those rows and
update in the base table okay
not only select update and delete order
by queries okay look at this I want all
the employees ordered by their salaries
in ascending order okay if we don't
specify ascending keyword by default the
salaries that are rendered in ascending
order so now if you look at the index
itself the salaries are already pre
sorted so the database engine can simply
scan this index from the beginning till
the end take the row addresses go to the
table retrieve them in the same order so
this avoids you know sorting the
during query execution it simply has to
scan the index retrieve the raw
addresses go to the base table retrieve
them and there is no you know additional
step of sorting those results during
query execution which will significantly
improve the performance of the query not
only sorting the salaries in ascending
order even if you want to sort the
salaries in descending order the
database engine has to simply scan the
index in reverse order and then retrieve
the results from the table
so both order by ascending order by
descending queries can also benefit from
the right indexes and along the same
lines even group by clause can benefit
from the index okay now what we are
trying to do here we are grouping or we
are counting the total number of
employees at each salary for example I
won't find all the salary I mean the
total number of employees who have got
2,500 s salary three thousand five
hundred as their salary and then I want
the total number of employees in the
next column so obviously to achieve that
we have spoken about group by clause as
well in our previous video sessions of
this video series okay so basically we
are grouping employees by salary okay so
we know that in the index employees with
similar salaries appear next to each
other okay they will appear
consecutively in this index so counting
them will be relatively easy from the
beginning till the end scan the index
find the rules which have similar
salaries and count them and simply
result return that result so group I can
also benefit from the index on the
salary column all right let's look at
the disadvantages of few indexes now we
spoke about the advantage is basically
indexes help queries to find data
quickly so what are the downsides the
downsides obviously additional disk
space now if it's a clustered index you
know here on the ID column we have the
clustered index so we don't require an
additional disk storage space but if it
is a non-clustered index the index is
stored separately
now let's say I have ten columns in a
table and let's say I create ten indexes
non-clustered indexes for example okay
now as this non-clustered indexes are
stood separately from base table itself
they require additional disk storage but
these days disk storage is less costly
you know we can actually trade that for
best application performance so cluster
indexes doesn't require any additional
storage every non-clustered index
require additional space as it is stored
separately from the table the amount of
space required will depend on the size
of the table and the number and type of
columns that we are using in the index
okay obviously if we create a composite
index then there are more number of
columns in the index and and the amount
of space required will be more what is
the other disadvantage insert update and
delete statements can also become slow
now in the previous session I mean in
the previous slide we said that you know
if we have deleted update records
obviously they can benefit from an index
but here we are saying insert update and
delete can become slow
that's because see in order to delete
the delete or update a record we need to
locate that you know that time to locate
can be reduced but then if the tables
are huge and if there are too many
indexes when you delete their update
records then the indexes needs to be
updated as well all the indexes now if
you look at this for example if you take
if it if you look at this employee table
and this index let's say for example I
am deleting an employee with salary is
equal to 5,000 from the table
I mean 2,500 from the table so John's
record will be deleted 2500 salary John
record will be deleted from the table
now when that record is deleted from the
table it also has to be deleted from the
index okay so if there are 10 other
indexes which are referring you know his
first name is gender city obviously all
those indexes needs to be updated as
ah okay so that's why if there are too
many indexes they can actually affect
insert update and delete statements
okay so DML statements data manipulation
statements insert update and delete
modifies data in a table the data in all
the indexes also needs to be updated
indexes can help to search and locate
the rules that we want to delete but too
many indexes to update can actually hurt
the performance of data modifications
that's why index tuning is a very
important task you have to choose the
right number and type of indexes all
right so those are the two disadvantages
of indexes so what's a covering query
covering query I mean if all the columns
that you have requested in the Select
Clause now let's say here if you look at
the index that we have we have the index
on the salary column now when I say
select star from TBL employee where
salary greater than 2000 and less than
8,000 I'm saying star meaning I want all
the columns first-name lastname salary
gender City now obviously these columns
first name last name gender are not
present in the index so from the index
we have to look back in the main table
and then retrieve those columns so there
is a lookup involved but then if I just
say select salary from TBL employee
where salary is greater than 4,000 and
less than 8,000 then salary column that
you have requested in the Select list is
present in the index itself so here the
query is being covered by this index so
that's called a covering query now we
have spoken about composite indexes in
part 35 or 36 now when we create
composite index a composite index is
basically an index on two or more
columns now if I create a composite
index let's say on first name and last
name obviously if I you know the chances
of that index covering a query or more
so basically covering query all the
columns that you have requested in the
Select Clause of a query are present in
index then there is no need to look up
in the table again the requested column
column's data can simply be returned
from the index okay a clustered index
clustered index you know here the ID is
the clustered index if this is chosen
you know when we execute a query then a
clustered index will always cover a
query because all the columns are
present in the table itself so when I
say select star from TBL employee wear
ID is equal to five so you are saying
star which means you want all the
columns ID is equal to five so obviously
it uses the clustered index there and
then it retrieves everything from the
same table if there is no lookup
involved okay so the a clustered index
will always cover a query and the
chances of composite index covering a
query a little more so covering query is
nothing but if the requested columns are
present in the index itself there's no
need to look up the base table so in
that case that index is covering the
requested columns in the query so in so
that kind of a query is called as a
covering query on this slide you can
find resources phrase we don't need
c-sharp and sequel server interview
questions that's it for today thank you
for listening have a great day