welcome to preim Technologies I wenet this is part 37 of SQL server in this session we'll learn about what a unique indexes 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 this sample SQL 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 okay so let's create this table TBL employe 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 cluster index should have been created on that column for this table and obviously to find that out we can make use of the system stor procedure Spore 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 um 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 cluster index because we marked this column as primary key 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 pkor TBL employee and the trandom 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're 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 the in the key is also gone the primary key constraint is also gone so now if I try to insert duplicates into the table let's see if it accepts them 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 values for the ID column okay so this proves that behind the scenes a primary key constraint actually uses unique index to enforce that constraint so this uniqueness of an index is not a separate index by itself it is just a property of an index so both a clustered and a non-clustered indexes can be unique okay 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 okay let's say I want to enforce that I can actually create a unique non-clustered index in that case okay so I'm creating a unique non-clustered index and usually for Unique indexes it's better if you prefix that with uix so just by looking at the name you can say that this is a unique index on TBL employee on the first name and last name columns okay so here we are creating a unique non-clustered index okay so difference between unique constraint and unique index if you remember in part nine we have actually spoken about unique key con straint okay so anytime you want to enforce uniqueness across one or more columns you know we will actually use unique key constraint okay 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 okay let's look at that in action okay let's say I have this table TBL employee which has got um 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 okay so obviously to add a unique constraint we have to alter the table so alter table table name add constraint and unique constraint uq _ table name uncore City on City column I'm adding this unique constraint and I'm saying unique okay and then for the C 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 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 or 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 a clustered index and obviously when we right click go to the properties you should see it's a unique clustered index okay now this is actually a constraint that we are 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 getss 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 a constraint or manually created that using Create index statement all right 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 mean that as well you know when we added 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 L 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 and SQL server interview questions that's it for today thank you for listening have a great day