Indexes in sql server Part 35 Video Lecture - SQL Server Administration

hello welcome to human technologies I am
rancor this is part 35 of sequel server
in this session we'll learn about what
indexes are why do we use indexes and
the advantages of using them so why
indexes indexes are used by queries to
find data from tables quickly in excess
I usually created on tables and views
index on a table or review is very much
similar to an index that we find in a
book if you don't have an index in a
book and I ask you to locate a specific
chapter in that book you will have to
look at every page starting from the
first page till the end on the other
hand let's assume there is an index in
that book in that case you look up the
page number of the chapter with the
index and then directly go to that page
number to locate that chapter so
obviously the book index is helping to
drastically reduce the time it takes to
find the chapter in a similar fashion
table and view indexes can help the
query to find data quickly in fact the
existence of write indexes can
drastically improve the performance of
the query if there is no index to help
the query then the query engine just
like we do you know it has to scan every
row in the table from the beginning till
the end this is called as table scan and
database terminology and table scans are
bad for performance so when does a query
result in a table scan whenever there is
no index to help that query so obviously
in that case the database engine has to
scan every row from the beginning till
the end to find the data it satisfies
the query so obviously if the table is
large enough then it's going to affect
the performance of the query very badly
just like how it takes a longer time for
us to find you know the chapter number
or some keywords if there is no index
based on that chapter or keywords let's
look at an example we've got this
employee table which has highly named
salary and gender columns at the moment
this employee table does not
index on the salary column so if you
look at the salaries you know we have
two thousand five hundred six thousand
five hundred and then four thousand five
hundred again so these salaries are not
arranged in ascending or descending
order in this table so they are mixed
and matched so obviously if I have to
find you know employees whose salaries
between 5,000 and 7,000 I will have to
scan each and every row in a similar
fashion if there is no index on this
table to help this query obviously the
database engine has to scan every row
from the beginning till the end it has
to scan the entire table and this is
called as tables can we just poke about
it and table scans are very bad so when
does a query result in a table scan if
it doesn't have a right index to help
that query okay so let's see how to
solve this problem with the help of a
query I mean with the help of an index
so to create an index on a table we use
create index statement so to create a
table will say create table table name
to create a stored procedure we will use
create proc procedure name in a similar
fashion to create an index we use create
index and the index name so there is a
you know naming convention that we
generally follow for naming indexes so
since we use SP for stored procedures
TBL for tables indexes usually we use
capital I and X in the X underscore the
name of the table on which you are
creating the index in this case we are
creating an index on table employee so
underscore TBL employee underscore on
the column within the table on which we
are creating this index in this case we
are creating an index on salary column
so underscore salary on the table on
which we are creating the index TBL
employee and on salary column within
that table now how you want the entries
in the index to be arranged okay so we
are creating an index on the salary
column so in the index the salary column
is the key so how do you want these key
values to be arranged do you want them
to be arranged in
ascending or descending order you can
control that using ASC for ascending and
de se for descending keywords okay so we
are creating here you know an index on
salary column in the ascending order now
if you look at this index all the
salaries are arranged in ascending order
okay but here in this table they are not
arranged in ascending order that's why
it's very difficult to find data that
matches this query select star from TBL
employee where salary is greater than
5,000 and less than 7,000 so once we
have this index and if we execute that
query now we know that all salaries
between five and seven thousand are
somewhere at the bottom of the index
because they are stored in an ascending
order and if you look at every entry in
the index you know for example two
thousand five hundred record a record
that has got two thousand five hundred
salary it has got a row address here
just like how we have page numbers in
the book the rules have row addresses
now the actual index may look slightly
different this is for our understanding
that I have entered row address here but
in fact it will be some hexadecimal
number there so two thousand five
hundred and it will have its Rewa
address there so it will directly go to
the table and then pick up that record
just like how if we have a page number
we go to that page number directly okay
so since the salary is between five and
seven thousand are present at the bottom
of the index we take those row address s
go to the table fetch them so instead of
scanning each individual row in this
table I am now able to seek the index
and then pick up the addresses from the
index go to the table and using those
addresses retrieve those records
directly rather than scanning each row
in the table so obviously this is going
to you know improve the performance of
this query okay now let's see how to
create this index practically so create
index I X underscore we want to create
that on TBL employee table so TBL
employee on salary column on TBL
employee table on salary column and how
do you want the entries to be arranged
in ascending order that's it so create
index index name on employee table for
salary column so let's select that
execute so command completed
successfully so we have created an index
on this TBL employee table so go to the
table within the database so expand
tables folder and we have caught TBL
employee table now if we expand the TBM
employee table you should see indexes
folder and if you expand indexes folder
you should see an index that we have
just created index underscore TBL
employee underscore salary and then if
you look at that we have got something
called it's a non-unique and
non-clustered index so what we mean what
do we mean by non unique and
non-clustered indexes we'll talk about
the different types of indexes in our
next session of this video series ok so
basically in this session we'll
understand the basics of index so what
is the advantage of indexes indexes help
us to find data quickly and how to
create an index we can use the create
index statement ok and after we create
the index how to find the index that we
have created you can go to the object
Explorer expand the database and then
the tables folder and the table and then
go into the indexes folder that's one
way another way is basically to use one
of the built-in system stored procedures
to find the indexes on that table so I
have the system stored procedure called
SP underscore help index just like how
we have SP underscore help text system
stored procedure to find the text of a
you know stored procedure we have a SP
underscore help index system stored
procedure to find all the indexes that
are available or that are created for
the table that we are going to specify
in this case I want to find all the
indexes that are created on TBL employee
table specify that as a parameter for
the system should
seiga execute that you should see the
index so we have this index created and
then this and in this index what is the
index key salary column and obviously if
you want to drop the index we can use
the drop key word drop index and then
the index name okay but then since an
index is on a table you also have to
specify the table name so we have
created an index on TBL employee table
so I can I will have to specify TBL
employee and then the name of the table
in this case it is index TBL employee
salary so f5 command completed
successfully
I come to the indexes folder refresh
that it shouldn't be there anymore now
we have seen how to create an index
using a query it's also possible that
you can create an index graphically
using the designer okay and the way we
do it is right click on the indexes
folder and select new index it gives you
a designer where you can specify the
index name and other options so let's
say I want to create an index called IX
on TBL employee on the salary column and
look at this it's asking you whether you
want to create a clustered or non
clustered or special now there are
different types of indexes in sequel
server which we will be talking about in
a later session for now let's create a
non-clustered index and do you want the
index to be unique or non unique you
know again we'll talk about unique and
non unique indexes in a later session
and then you can specify the columns
okay I want to create an index on the
salary column click OK so we are
creating an index on this Ellery column
click OK and it should create that index
okay just like how we have used the
query you know we have used the designer
to create the index for us
on this slide you can find resources for
a speed or tendency sharp interview
questions that's it for today thank you
for listening have a great day