Clustered and non Clustered Index

Question

Hi friends What is the basic difference between clustered and a non-clustered index? Why we use these type of index....... thanks..........

Tulasi · Answer

Index in sql is created on existing tables to retrieve the rows quickly. 2 Types of Indexes Clustered Index : we can create only one clustered index for table . It sort the records either ascendeing order or descendeing order permanatly in database . this is useful when to search particular range of records . By default Primary key Contraint will create clustered index on table Non-Clustered Index : can create more than one non-clustered indexes on table. It will no sort records Physically , this is usefull when to search random records on table . By default unique key contraint will create non-clustered index on table. Use of SQL server index es provide many facilities such as: Rapid access of information Efficient access of information Enforcement of uniqueness constraints Chk the below link http://www.codeproject.com/KB/database/SQLIndex.aspx

Aarti · Answer

Hi Vikas, Clustered indexes cause the data to be ordered according to the index, which means reordering happens every time the index changes, for whatever reason. Could be an UPDATE, or an INSERT. Since clustering affects the physical order of data, a table can have a maximum of 1 clustered index. The general rule of thumb is to: 1: cluster on columns where you fetch ranges 2: cluster on columns you frequently order by 3: cluster on primary key. Of dubious value in my opinion. One main and importtant difference is that when a table doesn't hava a clustered index, any non-clustered index contain a pointer to the actual data in the table. When a clustered index is created, all non-clustered indexes are rebuilt to contain the index columns of the clustered index, which may lead to slower access. Quite often, it's more valuable to consider creating covering indexes, i.e. an index containing all columns required to run a query, thus eliminating looking in the actual data pages. About reclaiming unused space: Yes, rebuilding a non-clustered index doesn't affect actual table space, but a clustered index *includes* the data pages, hence the change in space usage when rebuilding the index. Thanks.

Vikas Mishra · Answer

Thanks vidya.......

Satyapriya Nayak · Answer

Hi Vikas, Normal 0 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. Clustered index is physically stored a table can have 1 clustered index A non-Clustered 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 non-Clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Non clustered index is logically stored a table can have 249 non Clustered index Thanks

Narayan · Answer

http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/ff7d1b60-2872-4aaa-b705-df95ca9cdd94/

Clustered and non Clustered Index

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)