Difference between Cluster and Non-cluster index?

Answers were Sorted based on User's Feedback



Difference between Cluster and Non-cluster index?..

Answer / palpantian

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.

Is This Answer Correct ?    339 Yes 33 No

Difference between Cluster and Non-cluster index?..

Answer / p.karthick

clustered index is physically stored
a table can have 1 clustered index

non clustered index is logically stored
a table can have 249 non clustred index

Is This Answer Correct ?    193 Yes 46 No

Difference between Cluster and Non-cluster index?..

Answer / ajay kumar gupta

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 ?    141 Yes 37 No

Difference between Cluster and Non-cluster index?..

Answer / seema

Clustered Index :-
1.There can be only one Clustered index for a table
2.usually made on the primary key
3.the logical order of the index matches the physical stored
order of the rows on disk

Non-Clustered Index
1.There can be only 249 Clustered index for a table
2.usually made on the any key
3.the logical order of the index does not match the physical
stored order of the rows on disk

Is This Answer Correct ?    85 Yes 11 No

Difference between Cluster and Non-cluster index?..

Answer / naren

clustered index is created on actual data.

Non clustered index ix created on pointer of data.

If there is primary key by default clustered index is
created.
else non clustered index created.

only one clustered index is possible to create.


249 non clustered indexex can be created.

Is This Answer Correct ?    59 Yes 15 No

Difference between Cluster and Non-cluster index?..

Answer / kshirsagar sambhaji

The difference is that, Clustered index is unique for any
given table and we can have only one clustered index on a
table. The leaf level of a clustered index is the actual
data and the data is resorted in case of clustered index.
Whereas in case of non-clustered index the leaf level is
actually a pointer to the data in rows so we can have as
many non-clustered indexes as we can on the db.


Is This Answer Correct ?    48 Yes 19 No

Difference between Cluster and Non-cluster index?..

Answer / hannan

First, you must understand what a cluster is in Oracle.

A cluster is simply a method for storing more then 1 table
on the same block. Normally
-- a block contains data for exactly 1 table. In a cluster
-- you have data from many
tables sharing the same block.

For example -- if you join the data from EMP and DEPT by
DEPTNO frequently -- you might
consider clustering this data by DEPTNO. In that fashion
all of the rows from EMP for
deptno=10 and the row in DEPT for deptno=10 will reside on
the same exact block (one IO
to get all of the data instead of some IO's to EMP and DEPT).

In order to organize data in such an object, we create a
cluster KEY -- deptno in our
above example. We must index this cluster key. This index
on deptno in the cluster is
called a CLUSTER INDEX and is used to locate the blocks that
contain data about deptno=10

Is This Answer Correct ?    21 Yes 4 No

Difference between Cluster and Non-cluster index?..

Answer / chandu

Clustered index:
A Table have only one clustered index.
Once we defined a column with primary key constraint, sql
server automatically creates unique clustered index on that
column,
Actual rows arranged in same order as index.
Eg: Dictionary.

Non-clustered index:

Atable have 249 non clustered index.
Once we defined a column with unique key constrain , sql
server automatically create non clustered index.
Actual rows not arranged in same order as index.
Eg: Text book

Is This Answer Correct ?    9 Yes 2 No

Difference between Cluster and Non-cluster index?..

Answer / suvek samyal

>clustered index store the actual data.
>one table can only have one clustered index because
clustered index is the actual table stored in order of the
clustered key.
>usually primary key used as the clusted key,but we can
also take the combinition of first name and last name as a
clustered key.
>printing phone directory is the best example of clustered
index.
-->non-clustered index is just like a pointer to the data.
-->one table can have upto 249 non-clustered indexs because
it has the pointer of actual row.
-->page no.,google search and other search engines used as
a pointer of non-clustered index.
-->the index in the back of a book is the best example of
non-clustered index

Is This Answer Correct ?    8 Yes 2 No

Difference between Cluster and Non-cluster index?..

Answer / helps

The index one sees in the beginning of a book is a
clustered index and the index one sees at the end of the
book is a non clustered index.

Is This Answer Correct ?    5 Yes 0 No

Post New Answer

More SQL Server Interview Questions

An employee table, with the columns id, name, sal and dob. Query to select emp names of all highest salaries(there are 4-5 people having the same salary which happens to be the highest).

9 Answers   ADP,


What is the difference between a function and a stored procedure?

0 Answers  


i have a table student like sname ----- ram ram ram raj raj raj i need the output like sname ----- ram raj ram raj ram raj

9 Answers   IBM,


What is abstracting periodical?

0 Answers  


What is model database in sql server?

0 Answers  






What is plan freezing?

0 Answers  


What is the distinction amongst delete and truncate?

0 Answers  


What is data integrity? Explain constraints?

1 Answers   Thinksoft,


What is the difference between join and inner join?

0 Answers  


How to execute stored procedure in select statement sql server?

0 Answers  


What is the difference between dataadapter and datareader?

0 Answers  


how to define testing of network layers? : Sql server database administration

0 Answers  


Categories