Playing with Clustered and Non Clustered Index :-

Clustered INDEX :-

A table is nothing but a collection of record sets; by default, rows are stored in the form of heaps unless a clustered index has been defined on the table, in which case, record sets are sorted and stored on the clustered index. The heaps structure is a simple arrangement where the inserted record is stored in the next available space on the table page.

Heaps seem a great option when the motive is simply storing data, but when data retrieval steps in, this option back fires. An index acts as a fire fighter in this scenario. Indexes are arranged in the form of a B-Tree where the leaf node holds the data or a pointer to the data. Since the stored data is in a sorted order, indexes precisely know which record is sitting where. Hence an index optimizes and enhances the data retrieval immensely.

SQL Server 2005:

 1 Clustered Index + 249 Nonclustered Indexes = 250 Indexes per table

SQL Server 2008: 

1 Clustered Index + 999 Nonclustered Indexes = 1000 Indexes per table

-------Clustered INDEX example------

--Creating new table with few columns

create table INDEX_learnig(ID int ,FirsName varchar(30),LastName varchar(30))

select * from INDEX_learnig

--Inserting same data into the table before creating CI

insert into INDEX_learnig values(1,'Reza','AA')

insert into INDEX_learnig values(1,'Masum','BB')

*We can Insert same values with multiple times

--Creating Normal Clustered on ID column

create clustered index U_CI on INDEX_learnig(ID)

insert into INDEX_learnig values(1,'Reza','AA')

insert into INDEX_learnig values(1,'Masum','BB')

select * from INDEX_learnig

still not a problem to insert same values 

-------- Alter or Drop the Clustered values with Unique Constraint

drop index U_CI on INDEX_learnig

truncate table INDEX_learnig

-------

--Creating Unique clustered values

create unique clustered index U_CI on INDEX_learnig(ID)

insert into INDEX_learnig values(1,'Reza','AA')

insert into INDEX_learnig values(2,'Masum','BB')

insert into INDEX_learnig values(1,'Masum','BB')  --error(Same records from row 1)

Following error will Occur:

Msg 2601, Level 14, State 1, Line 25

Cannot insert duplicate key row in object 'dbo.INDEX_learnig' with unique index 'U_CI'. The duplicate key value is (1).

The statement has been terminated.

------

drop index U_CI on INDEX_learnig

truncate table INDEX_learnig

------

--Creating Unique Clustered with multiple column

create unique clustered index U_CI on INDEX_learnig(ID,FirsName)

insert into INDEX_learnig values(1,'Reza','BB')

insert into INDEX_learnig values(2,'Masum','BB')

insert into INDEX_learnig values(1,'Reza','BB') --Error(Same as first row)

and the error will occur like :

Msg 2601, Level 14, State 1, Line 34

Cannot insert duplicate key row in object 'dbo.INDEX_learnig' with unique index 'U_CI'. The duplicate key value is (1, Reza).

The statement has been terminated.

--Clustered Index with Primary KEY and Unique Identity

--Creating Unique Identity Column

drop table INDEX_learnig

create table INDEX_learnig(ID int unique identity(1,1),FirsName varchar(30),LastName varchar(30))

insert into INDEX_learnig values('Masum','BB')

select * from INDEX_learnig

--Try to create Unique Clustered Index

create unique clustered index U_CI on INDEX_learnig(FirsName,LastName)

insert into INDEX_learnig values('Masum','BB') --Error

error will occur like :

Msg 2601, Level 14, State 1, Line 46

Cannot insert duplicate key row in object 'dbo.INDEX_learnig' with unique index 'U_CI'. The duplicate key value is (Masum, BB).

The statement has been terminated.

When you create a unique constraint, the database engine creates a unique nonclustered index. 

However, you can specify that a unique clustered index be created if a clustered index does not already exist.

For all practical purposes, a unique constraint and unique index are one in the same.

Once You Create Primarey Key it will autometically create Clustered Index

drop table INDEX_learnig

create table INDEX_learnig(ID int Primary key,FirsName varchar(30),LastName varchar(30))

Try to create Unique Clustered Index

create unique clustered index U_CI on INDEX_learnig(FirsName,LastName)

The error will throw like :

Msg 1902, Level 16, State 3, Line 51

Cannot create more than one clustered index on table 'INDEX_learnig'. Drop the existing clustered

index 'PK__INDEX_le__3214EC27E5FBFD04' before creating another.

When you create a primary key constraint, the database engine also creates a unique clustered index, 

if a clustered index doesn’t already exist. However, you can override the default behavior and specify 

that a nonclustered index be created. If a clustered index does exist when you create the primary key, 

the database engine creates a unique nonclustered index.