Interested to Buy Any Domain ? << Click Here >> for more details...
Can we create non-clustured index on a clustered index ?
- 4 Answers
- 16728 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 is number function in sql?
Does sql support programming?
What is the most common sql injection tool?
Is clustered index a primary key?
What are the query optimization techniques?
Is sql better than excel?
Table 1: col1 Timestamp ---------------- 01-mar-2012 11:12:46 Table 2: col2 Timestamp -------------------- 01-mar-2012 11:12:10 01-mar-2012 11:11:23 Write a query to display a row with table2 col2 value less than tabl1 col1 value. Maximum timestamp value previous to table1 col1 value. Display a result as: Col1 col2 ----- ----- 01-mar-2012 11:12:46 01-mar-2012 11:12:10
Can you skip header records while loading? : aql loader
What are the different dcl commands in sql?
What is procedure function?
What is thread join () in threading?
Is sql a backend?
What are local and global Indexes and where they are useful.
how to use in conditions? : Sql dba
Is left join faster than inner join?
