Click here to Skip to main content
15,937,127 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
what is the difference between clustered and non clustered index in sql server. what will we use while designing table in sql server.
Posted
Updated 19-Sep-12 22:36pm
v2
Comments
Andrei Straut 20-Sep-12 4:38am    
Pst! Look what Google whispered to me[^] when I told him your exact question
Vedangi 20-Sep-12 4:40am    
YOu can refer this article
http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005

While I agree that the best way to answer this would have been google, I will just add that a clustered index is super fast for searches, and slows down deletes and ( to a lesser degree ) changes to data. If you have a primary key on your table, it is a clustered index by default, so if you want to have another clustered index, your primary key needs to be marked NOT clustered.
 
Share this answer
 
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 and this is usually made on the primary key. 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.
There can be as many as 249 nonclustered index per table
 
Share this answer
 
v2
The basic differences between Primary Key and Unique key are as follows.
1) By default Primary Key will generate Clustured Index
whereas Unique Key will Generate Non-Clustured Index.
2) Primary Key is a combination of Unique and NOT NULL Constraints so it can’t
have duplicate values or any NUll
Whereas for Oracle UNIQUE Key can have any number of NULL whereas for SQL
Server It can have only one NULL
3) A table can have only one PK but It can have any number of UNIQUE Key.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900