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.