Interested to Buy Any Domain ? << Click Here >> for more details...
Can we create non-clustured index on a clustered index ?
- 4 Answers
- 16754 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
Is id a reserved word in sql?
How do I find duplicates in two columns?
What is the difference between inner join and natural join?
How do you update f as m and m as f from the below table testtable?
Explain lock escalation? : Transact sql
What is a sql statement?
What is sql not null constraint?
what is the difference between rownum pseudo column and row_number() function? : Sql dba
what is data control language? : Sql dba
How do I find duplicates in sql?
What are the 3 modes of parameter?
What are the different types of a subquery?
What is pivot query?
Where can I learn sql for free?
Enlist the data types that can be used in pl/sql?
