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.
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.