Interested to Buy Any Domain ? << Click Here >> for more details...
Can we create non-clustured index on a clustered index ?
- 4 Answers
- 16900 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 many commands are in sql?
how can we destroy the cookie? : Sql dba
how to get a list of columns in an existing table? : Sql dba
How do you delete data from a table?
What are the different parts of a package?
How can you view the errors encountered in a trigger?
what is a join? : Sql dba
How do you get column names only for a table (sql server)?
How do you use collections in procedure to return the resultset?
What is keys and its types?
What are tables and fields in the database?
What is %s in sql?
What are views in sql?
What is trigger point?
What is the difference between inner join and outer join?
