Unique and Non Unique Indexes in sql server Part 37 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

welcome to pledging technologies I am
venker this is part 37 of sequel server
in this session we'll learn about what a
unique index is and the difference
between unique index and unique
constraint before continuing with the
session I strongly recommend to watch
parts 9 35 and 36 of this video series
so what's a unique index unique index is
used to enforce uniqueness of key values
in the index let's understand this with
an example I have the sample sequel
script here which creates table TBL
employee and if you look at the script
the ID column is marked as primary key
column and by default a primary key
constraint creates a unique clustered
index on that column if another
clustered index doesn't already exist
for this table let's look at this in
action
ok so let's create this table TBL
employee using the script command
completed successfully so the table
should have been created since we have
marked this ID column as the primary key
column a unique clustered index should
have been created on that column for
this table and obviously to find that
out we can make use of the system stored
procedure SP underscore help index
passing in the name of the table which
will list all the indexes that are
available for this table so let's
execute this so we have a unique
clustered index on the ID column and
this is the name of the index this is
one way to find out the indexes that are
available for a given table another way
obviously is to use the object Explorer
so within the object Explorer refresh
the tables folder and you should see the
TBL employee table expand that expand
the indexes folder and you should see an
index that we have just created and this
here says it's just a clustered index it
doesn't tell us whether it's a unique or
non unique index to identify that you
can right-click on the index select
properties and you should see this
checkbox checked which proves that this
is a unique
clustered index okay now how did we get
this unique clustered index because we
mark this column as primary
Column it created this unique clustered
index behind the scenes okay now since
we we mark this ID column as a primary
key there should be a primary key as
well with that column so when I expand
that okay look at the primary key here
in the keys folder look at the primary
key and look at the index they're
actually the same thing
piqué underscore TBL employee and that
random number okay so in reality the
primary key constraint actually uses
unique index behind the scenes to
enforce the primary key constraint okay
let's prove that let's try to drop this
index okay let's copy the index and
obviously to drop the index we can use
drop index statement drop index table
name dot index name because anytime you
drop an index you need to specify the
table name as well let's execute this
and see what's going to happen an
explicit drop index is not allowed on
this index because it's being used for
primary key constraint enforcement so
this message proves that primary key in
fact uses a unique index behind the
scenes to enforce the constraint okay
now if you are not able to drop it using
drop index statement you can actually do
that using the object Explorer so let's
try to delete this index from the object
Explorer right-click on the index and
select delete click okay so look at this
the index is gone but the key is still
there but the moment i refresh the
folder look at what's happening then the
key is also called the primary key
constraint is also gone
so now if I try to insert duplicates
into the table let's see if it except
stand okay one row affected one row
affected so both of the rows are
inserted into the table and we select
the rows you should see duplicate where
values for the ID column okay so this
proves that behind the scenes a primary
key constraint actually uses unique and
extra enforce that constraint
so this uniqueness of an index is not as
separate index by itself it is just a
property of an index so both it
clustered and a non-clustered unit
indexes can be unique ok so if I want to
create a unique non-clustered index for
example let's say I want to ensure that
in my organization no two employees can
have the same first and last name ok
let's say I want to enforce that I can
actually create a unique non-clustered
index in that case ok so I'm creating a
unique non-clustered index and usually
for unique indexes it's better if you
prefix that with you ix
so just by looking at the name you can
see that this is a unique index on TBL
employee on the first name and last name
columns ok so here we are creating a
unique non-clustered index ok so
difference between unique constraint and
unique index if you remember in part 9
we have actually spoken about unique key
constraint ok so anytime you want to
enforce uniqueness across one or more
columns you know we will actually use
unique key constraint ok but now we are
talking about using unique index so
what's the difference between them
actually there are no major differences
between a unique constraint and a unique
index in fact when we add a unique
constraint a unique index gets created
behind the scenes ok let's look at that
in action ok let's say I have this table
TBL employee which has got a city column
within that now I want to ensure that
you know all cities are unique within
this table how do I do that I can add a
unique constraint so let's add a unique
constraint ok so obviously to add a
unique constraint we have to alter the
table so all the table table name add
constraint and unique constraint you Q
underscore table name
underscore city on city column I'm
adding this unique constraint and I'm
saying unique okay and then for the city
column now let's execute this command so
command completed successfully so we are
actually adding a unique constraint so
let's refresh this folder and let's
expand the indexes now look at this I
have this index created and if you look
at the type of the index
it's a non-clustered unique index okay
if you want you can actually specify
let's drop this index for a minute now I
can specify whether I want a clustered
and a non-clustered index on this column
so if I want clustered I can specify
that as well so now when I do this okay
so I'm adding this constraint and when
we refresh the indexes folder expand
that look at this it clustered index and
obviously when we right-click go to the
properties you should say it's a unique
clustered index okay now this is
actually a constraint that we have we
are adding but behind the scenes what's
happening a unique index is getting
created now where is this constraint
then now when we expand the keys folder
you see the constraint and look at the
index name both of them are almost you
know the same okay so you know whether
you add a unique constraint or whether
you create an index you know there are
two ways basically to create a unique
index by adding a unique constraint or
using Create index statement and
directly creating a unique index but in
either cases you know index is the one
which supports that uniqueness I mean to
enforce that uniqueness so when should
you actually be creating a unique
constraint over a unique index now we
know that behind the scenes a unique
index gets created now you can do you
can do that through a constraint or
directly using a create index statement
okay so when do we choose one option
over the other now to make our
intentions clear create a unique
constraint when data integrity is the
objective
okay this makes the objective of that
index very clear in either cases data is
validated in the same manner and the
query optimizer doesn't really make any
difference between a unique index that
you have created by using it constraint
or manually created that is an create
index statement alright and some useful
points to remember now we have already
learned that by default a primary key
constraint creates a unique clustered
index we have seen that and we have also
seen a unique constraint creates a
unique non-clustered index by default
okay these are only defaults and they
can be changed if we wish to and we have
just seen that as well you know when we
add this constraint without this
clustered keyword it actually this
constraint actually added a unique
non-clustered index but if I want to
change the default which I did here by
just putting a clustered keyword in
which case it has created a unique
clustered index okay now a unique
constraint or a unique index cannot be
created on an existing table if the
table contains duplicate values in the
key columns okay so for example if you
look at this table the ID column value
is duplicated and if I try to add a
unique constraint or a unique index on
this column it's going to fail why
because you already have an existing
duplicate values there so until you
clear them you cannot actually add an
index or another option is basically to
include another column in your unique
index low if I include both ID and
first-name I want to ensure that you
know there is uniqueness you know in the
combination of both the columns so one
way is to delete the duplicate value the
other way is to basically add or remove
columns you know to ensure uniqueness
across one or more columns
on this slide you can find resources for
asp.net c-sharp and sequence of
interview questions that's it for today
thank you for listening
have a great day