constraint unique

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.


  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.


                   Create nonclustered index ename_ind on Emp(ename)

                   Create index Sal_ind on Emp(sal).


          Create table student(sid int, sname varchar(50))

          Create  unique clustered index sid_Ind on student(sid)


 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.