Can we create non-clustured index on a clustered index ?
- 4 Answers
- 15917 Views
- TCS, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
What are all the different types of indexes?
Is coalesce faster than isnull?
How do I save a stored procedure?
Can we use views in stored procedure?
What is the use of function in sql?
Does sql support programming?
What is bind reference and how can it be created?
What is $$ in sql?
What is indexes?
How can get second highest salary in sql?
What is count * in sql?
How does left join work in sql?
Explain the working of foreign key?
What are pl/sql cursors?
Does pl/sql support create command?
