If the index is created by using “unique” option that column on which the index is created will not allow duplicate values i.e. it works as a “unique constraint” unique constraint can be either unique clustered or unique non-clustered also.
Note
while creating an index if clustered or non-clustered is not specified default is non clustered.
Qs. How many indexes a table can have ?
Ans. We can create a maximum of 250 indexes in which only one can be clustered and remaining all are non-clustered.
Are indexes created implicitly or should be create them explicitly. When ever we impose a primary key constraint on tables column internally a “ unique clustered” index gets created. Where as it a unique constraint is impose on any column internally a “unique non clustered ” index gets created.
For example
Creating indexes on emptable
As the empno column is imposed with a primary key it will contain a unique clustered index implicitly a part from that we can create any number of non-clustered indexes on the tables as following.
Syntax
Create nonclustered index ename_ind on Emp(ename)
Create index Sal_ind on Emp(sal).
Syntax
Create table student(sid int, sname varchar(50))
Create unique clustered index sid_Ind on student(sid)
Note
In the above case the index will not provide the functionality of primary key for sid column but still we will get the functionally of unique constraint.