Clustered and nonclustered indexes in sql server Part 36 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to play game technologies
I am venket this is part 36 of sequel
server in this session we'll learn about
the different types of indexes that are
available in sequel server what are
clustered and non-clustered indexes and
the difference between them before
continuing with the session I strongly
recommend to watch / 35 of this video
series the following are the different
types of indexes that are available in
sequel server cluster non-clustered
unique filtered XML full-text spatial
columnstore index with included columns
index on computed columns in this
session we'll be talking about clustered
and non-clustered indexes and the
difference between them so what's a
clustered index a clustered index
determines the physical order of data in
a table for this reason a table can have
only one clustered index we have a
create table statement here where we are
creating TBL employees table and if you
look at this closely the ID column is
marked as a primary key column a primary
key constraint will automatically create
a clustered index on that column here ID
is marked as a primary key column so
when I create this table using this
create table statement it's going to
create a clustered index automatically
on this ID column for this table let's
look at this in action I have the same
create table statement here let's
execute this command complete it
successfully
now let's check you know if it has
created a clustered index on this ID
automatically and how do we check that
there are two ways one way is to
basically use this SP underscore help
index system stored procedure passing it
the name of the table okay so it shows
that we have a clustered index on this
ID column okay we did not create this
explicitly it got automatically created
because a primary key constraint will
automatically create a clustered index
on that column if the table doesn't have
any clustered index
okay and if you look at this it says
it's a unique clustered index clustered
unique index okay so what is a unique
index we'll be talking about them in the
next session but at this point
understand that you know this unique
index is basically used by sequel server
to enforce the uniqueness of the primary
key okay we will be talking about unique
indexes in detail in the next session
all right what is the other way of
checking the indexes on this table one
is to use the SP a underscore help in
the existential procedure the other one
is basically to use you know the object
Explorer within the object Explorer
expand the TBL employees stable so TBL
employee table and expand the indexes
folder you should see the index that has
just been created all right
okay so now we just have created this
table TBL employee now let's insert some
data into that okay and look at this I
have some insert statements here and if
you look at this we are inserting values
for ID name salary gender and city here
if we look at this data close Lane the
values for the ID column are not in
sequential order
I am intentionally you know inserting
them in a non sequential order okay so
we told that it clustered index
determines the physical order of data in
a table and on and in this table on the
ID column we have a clustered index so
even if you insert data in this order
you know first the third record then the
first record then the fourth record when
I say select star from TBL employee the
data should have been automatically
arranged in this order so when I insert
three it inserts it in the first
location and when I insert the record
with ID is equal to one it should push
number three down and insert one before
that and along the same lines when I
insert four it should come after three
and when I insert five it should come
after five and when I insert two it
should automatically insert that between
records
1 & 3 so it should rearrange the data
based on the ID based on the value of
the ID column okay let's see if that
happens okay so now if you select from
the table we don't have any rows so
let's insert the data and notice that
the values for the ID column are not in
sequential order execute the data is
inserted now but look at this when I say
select star from TBL employee the data
is arranged in an ascending order of
this ID column so the clustered index
determines you know how the data is
stored in this table so a cluster
indexes and allow this to a telephone
directory where the data is arranged by
the last name so in the telephone
directory the data is arranged by last
name similarly in a table the clustered
in the data is arranged by the clustered
index key okay and we just learned that
the table can have only one clustered
index but however the index the one
clustered index that one clustered index
can contain multiple columns and if a
clustered index contain multiple columns
within that we call that kind of an
index as a composite index since it's a
clustered index we can call it as
composite clustered index similarly and
non-clustered index also can have
multiple columns within that in which
way in which case we call it a composite
non-clustered index okay now in the
telephone directory you know you can
consider the telephone directory just
like a composite index because you know
the basically the numbers are organized
by last name first and then if there are
similar last names for people or
organizations then the data is arranged
according to their first names okay so
in a similar fashion we can create a
composite clustered index on this
employee table for gender and salary
okay so basically what happens is when
we create this index now if you look at
the way we have the data it's arranged
on the in the order of the sided column
because currently we have a clustered
index on the ID column okay but instead
of that I want to create a clustered
index on the gender and salary columns
together which means I want to sort the
data first by gender and then by salary
the data should be arranged in that
order in this table so obviously when we
try to do that what happens to the order
of the IDS obviously that will be messed
up so at any given point of time you can
only arrange data in a certain way that
is the reason why you can have only one
clustered index per table okay so now
let's try to create a clustered index on
the gender and salary but remember there
is already a clustered index on the ID
column so obviously when we try to
create this clustered index on this
table we should get an error stating
that you cannot create more than one
clustered index on the table TBL
employee drop the existing clustered
index before creating another one okay
so obviously we will have to drop that
and to drop an index on a table we there
are again two ways one is to use the
query so drop index and the index name
but since an index is on a table you
will have to specify the table name as
well when you're dropping an index so
TBL employee dot the index name so let's
copy that then we execute this now look
at this we get an again an error and
explicit drop index is not allowed on
this index because it is being used for
primary key constraint enforcement okay
we'll talk about how a unique clustered
index is used to enforce primary key
constraint we'll talk about that in the
next session when we talk about unique
and non unique in the XS okay but to
safely delete this index what you can do
is go to the object Explorer expand the
indexes folder right click on the index
and select delete and click OK that
should delete the index
okay so we have deleted the
clustered index on the ID column now
let's try to create the clustered index
on the gender and salary columns okay so
basically this is a composite clustered
index because your index is containing
more than one column now okay keep in
mind you can only have one clustered
index on a table it's not possible to
have more than one clustered index but
it is possible for that one clustered
index to have more than one column
within that s index keys so here
the cluster index that I am going to
create now is going to have two columns
as the index keys gender and salary
gender will be arranged in descending
order first and then salary within that
gender in ascending order so execute now
let's select the data from the employees
table before we created this index it
was arranged in the ascending order of
the ID column but since now we have
created a clustered index on gender and
salary columns now the data is first
arranged you know in this table in the
order of in the descending order of
gender first and then within the same
within the gender it's then arranged in
ascending order of the salaries okay so
that's clustered indexes okay so a table
can have only one clustered index and
clustered index determine determines the
order in which the data gets stored in
that table okay and to create a
clustered index it's pretty simple
create clustered index the name of the
index and we have spoken about the
naming conventions to use when we are
creating an index on a table on table
and for which columns and if I have to
create a non-clustered index so how do
we create that we say create
non-clustered index the only differences
instead of saying clustered you specify
a non-clustered so here if you look at
the example I am creating a
non-clustered index on the name column
for TBL employee table okay now when we
spoke about clustered indexes you know
we understood that the data in the table
is arranged based on the clustered index
column okay now when you create a
non-clustered index and non-clustered
indexes is analogous to an index in a
textbook okay so if you look at the
index in a textbook the index is stored
in a separate place and the data is
stored in a separate place for example
at the beginning of the book you have
the index you know chapter index now if
I asked you to go to a specific chapter
you will first check the index okay
chapter five is on page number 400 so
you will go to page 400 so the index is
stored separately and the data itself is
stored separately so the index in a book
is a little different to an index in a
telephone directory or a dictionary okay
so in a telephone directory or a
dictionary the data is arranged in the
alphabetical order basically the data
itself is arranged you don't have a
separate index pages and separate data
pages but in a book you have separate
index pages and separate you know data
pages so similarly and non-clustered
indexes on a loggers to a book index
okay the index itself is stored
separately here for example since we
have created a non-clustered index on
the name column the names are arranged
in the ascending order here and then
each name you know have row address for
example if I write a query select star
from TBL employee where name is equal to
Todd what is going to happen it's going
to come here okay Todd is here because
tea you know when you compare with JPS
its its it will be present in the end so
it comes here okay Todd is here the row
address is here it goes to the table
based on the row address and then
directly fetch that record so since the
non-clustered index is stored separately
from the actual table a table can
actually have more than one
non-clustered index okay
just like how a book can can have an
index by chapters at the beginning and
maybe by maybe another index by common
terms at the end of the book okay so the
index is that we see in a book we can
have as many indexes as we want of that
kind okay so this
is applicable for non-clustered indexes
I can create one index or name column
another index on city column so there is
no really restriction on how many
indexes on how many non-clustered
indexes you you you can have okay but
clustered index is only one why because
you can only arrange data in one way if
we try to arrange by another column
obviously you will lose that ordering on
the first column for that reason you can
only have one clustered index on a table
okay and in a non-clustered index you
know the data itself is arranged based
on that index key either in ascending or
descending order and you can specify
that when you are creating the index
okay but this one will not in any way
the the non-clustered index will not in
any way influence the way in which data
gets stored in the table itself that is
determined by the clustered index key
and if you don't have a clustered index
on the table then you know the data is
not guaranteed to be ordered in any
specific way okay finally we will look
at the differences between a clustered
and a non-clustered index okay and this
is a very common interview question
that's asked these days okay
and from whatever we have learnt until
now you know you can have only one
clustered index per table whereas you
can have more than one non plastered
index and there is a logical reasoning
behind that anyone and I and I believe
you understand that clustered index is
obviously faster than non-clustered
index because the clustered index has to
refer back to the table if the selected
column is not present in the index okay
what do we mean by that for example this
is a non-clustered index now when I
asked you know select ID comma salary
comma agenda from TBL employee wear name
is equal to sarah what will you do you
first check the index
okay Sarah's record is here this is the
row address and if I want the ID N and
salary columns I don't have those
columns in this index so I will have to
check I will have to refer the table
back okay so obviously there is this one
extra
look up involved if it's a non-clustered
index but on the other hand if it's a
clustered index then all the columns are
present in the table itself and the data
itself in the table is arranged based on
that clustered index so you don't have
that extra lookup that's why clustered
indexes are slightly faster than
non-clustered indexes clustered index
determines the storage order of rows in
the table and hence doesn't require
additional disk space obviously but
whereas non-clustered indexes you know
if you look at here non-clustered
indexes are stored separately ok and
you're duplicating this name let's say
for example I have million records here
you will have million records in the
index as well and since non-clustered
indexes are stored separately from the
day table itself you require extra disk
storage space but for clustered index
it's not the case because it's cluster
index index just determines the order of
data in the table itself you don't
require additional disk storage space so
these are the three differences between
a clustered and a non-clustered index on
this slide you can find resources for
asp.net and C sharp interview questions
that's it for today thank you for
listening have a great day