Interested to Buy Any Domain ? << Click Here >> for more details...
Can we create non-clustured index on a clustered index ?
- 4 Answers
- 16786 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
How to handle bulk data?
Why do we use joins?
Why we use joins in sql?
Can I copy :old and :new pseudo-records in/to an oracle stored procedure?
What are different types of keys?
How to place comments in pl/sql?
What is a constraint? Tell me about its various levels.
Can there be more than one function with a similar name in a pl/sql block?
what are the types of join and explain each? : Sql dba
What sql database should I use?
How to pipe multiline string to isql?
What does (+) mean in sql joins?
Which are the different types of indexes in sql?
What is sharding in sql?
What is the maximum number of rows in sql table?
