1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

Difference between Cluster and Non-cluster index?

Question Posted / prakash

Answer Posted / prakash

Cluster index :-

1) CLUSTERED INDEX IS PHYSICALLY STORED THE DATA.

2)ONE TABLE CAN HAVE ONLY ONE CLUSTERED INDEX
BECAUSE IT STORED IN ORDER OF THE CLUSTERED KEY.

3)CREATING A CLUSTERED INDEX ON A TABLE RE-ARRANGES
THE DATA IN A SEQUENTIAL MANNER

4)ONCE WE DEFINED A COLUMN WITH PRIMARY KEY CONSTRAINT, SQL
SERVER AUTOMATICALLY CREATES UNIQUE CLUSTERED INDEX ON THAT
COLUMN




5) THE LEAF LEVEL OF A CLUSTERED INDEX IS THE ACTUAL
DATA


__________________________________________________________
NON-CLUSTERED INDEX:-

1)NON- CLUSTERED INDEX IS LOGICALLY STORED DATA.

2) ONE TABLE CAN HAVE 249 NON- CLUTTERED INDEX IN SERVER-2005
AND 999 IN SERVER-2008

3)NON-CLUSTERED INDEX IS CREATED ON A TABLE, THE DATA IS NOT
PHYSICALLY
RE-ARRANGED IN SEQUENTIAL ORDER.


4)ONCE WE DEFINED A COLUMN WITH UNIQUE KEY CONSTRAIN , SQL
SERVER AUTOMATICALLY CREATE NON CLUSTERED INDEX.

5) NON-CLUSTERED INDEX THE LEAF LEVEL IS
ACTUALLY A POINTER TO THE DATA IN ROWS

Is This Answer Correct ?    3 Yes 0 No



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

What is correlated subquery in sql server?

559

How to Insert multiple rows with a single insert statement?

551

What is the purpose of sql profiler in sql server?

517

Are connections to sql server encrypted?

578

Explain four layers of abstraction microsoft architectured?

124




What are the different types of replication you can set up in sql server?

531

Can a database be shrunk to 0 bytes, if not, why?

642

Explain different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?

584

Define right outer join in sql server joins?

521

what's the difference between delete table and truncate table commands? : Sql server database administration

490

What are joins in sql and what are the different types of joins?

554

How to call a function from a stored procedure in SQL Server ?

553

How to read data in a table with "select" statements?

565

How to write a query with an inner join in ms sql server?

554

When would you prefer to have a minimum number of indexes?

509