Can we create non-clustured index on a clustered index ?

Question Posted / wgk

4 Answers
15373 Views
TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Can we create non-clustured index on a clustered index ?..

Answer / andrew g avelin

Clustered indexes sort and store the data rows in the table
based on their key values. There can only be one clustered
index per table, because the data rows themselves can only
be sorted in one order. For more information about
clustered index architecture

A nonclustered index contains the index key values and row
locators that point to the storage location of the table
data

---------------

when you create a clustered index It sorts data physically
which is not same with non-clustured index, this is the
reason why there can be only one clustered index per table
and multiple non-clustered index.

---------------
Both indexes work as B-Tree structure .
But clustered index store actual data in leaf level node
and non - clustered index stored row pointer

-----------------

For a clustered index, the database will sort the table's
records according to the column (or columns) specified by
the index. A clustered index contains all of the data for a
table in the index, sorted by the index key, just like a
phone book is sorted by name and contains all of the
information for the person inline. The nonclustered indexes
contain only the index key and a reference to find the
data, which is more like a book index. A book index stores
words in order with a reference to the page numbers where
the word is located. You can only create one clustered
index on each table.

---------------------

Clustered indexes are created usally when we create Primary
key.Atmost we can have only one clustered index
Non clustered indexes are created on the table columns
We can have maximum of 241

Is This Answer Correct ?

12 Yes

3 No

Can we create non-clustured index on a clustered index ?..

Answer / andrew g avelin

difference between Cluster index and non cluster indexing?

A clustered index is a special type of index that reorders
the way records in the table are physically stored.
Therefore table can have only one clustered index. The leaf
nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which
the logical order of the index does not match the physical
stored order of the rows on disk. The leaf node of a
nonclustered index does not consist of the data pages.
Instead, the leaf nodes contain index rows.
------------------------

The difference between them lies in what the leaf-level
nodes -- the lowest level of the tree -- contains. In a
clustered index the leaf-level is the data, while the
leaves of a non-clustered index contains bookmarks to the
actual data. This mean that for a table that has a
clustered index, the data is actually stored in the order
of the index. What the bookmarks of the non-clustered index
point to depends on if the table also has a clustered index
or not. If it does have a clustered index then the leaves
of non-clustered indexes will contain the clustering key --
the specific value(s) of the column(s) that make up the
clustered index -- for each row. If the table does not have
a clustered index it is known as a heap table and the
bookmarks in non-clustered indexes are in RID format
(File#:Page#:Slot#), i.e. direct pointers to the physical
location the row is stored in

------------------------

(1)CLUSTERED INDEX
(2)NON-CLUSTERED INDEX

CLUSTERED INDEX :

(A) Data is restored in case of this index.CLUSTERED INDEX
is unique of nay given table and we can have only one
CLUSTERED INDEX on a table

(B) Leaf Level of CLUSTERED INDEX is Actual Data.

Non-CLUSTERED INDEX :

(A) We can have as many Non-CLUSTERED INDICES on the
Database.

(B) Leaf Level on NON_CLUSTERED INDEX is pointer to the
data in rows .

I hope u've got the differences b/W them.
------------------------

CLUSTERED INDEX:
Clustered index will be created by default when u
create primary key on a column. So we can create one
clustered index per table. clustered index is stored in
serial passion.

NON CLUSTERED INDEX:
Non clustered index will be created automatically when u
create unique key on a column. A table can have no.of
unique keys, so we can create no.of non clustered indexes
per table.

Is This Answer Correct ?

7 Yes

4 No

Can we create non-clustured index on a clustered index ?..

Answer / sourav mondal

Clustered indexes are created usally when we create Primary
key. Non clustered index will be created automatically when u
create unique key on a column. So we can't create Non
clustered index on a clustered index.

Is This Answer Correct ?

0 Yes

1 No

Can we create non-clustured index on a clustered index ?..

Answer / amit

No,
Non clustered index create when we declare a primary or
unique key and clustered indexes are created by user.

Is This Answer Correct ?

7 Yes

27 No

Post New Answer