Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Clustered Vs Non-Clustered Index

Author  Topic 

udham1985
Starting Member

6 Posts

Posted - 2011-01-27 : 23:25:43
Hi

Can you please explain me what is the use of Non-Clustered index?

Clustered index- reorder the data based on physical storage. so it makes fast search.

What about Non-Clustered ? It won't reorder the data,so how does it make fast searches?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-28 : 04:48:41
Clustered index does not force the physical order of the data.

For both, the fast searches come from the b-tree architecture, not the ordering.
Have a read through this and the rest of the series (3 parts in total) http://www.sqlservercentral.com/articles/Indexing/68439/

--
Gail Shaw
SQL Server MVP
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-28 : 05:11:20
quote:
Originally posted by GilaMonster

Clustered index does not force the physical order of the data.


Gail, I don't like to be contrary to this,
but a clustered index determines the physical order of data in a table.

Or I didn't catch anything about it...


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-28 : 08:07:33
A clustered index determines the logical order of the data in a table. Not the physical.

If there is 0% fragmentation of that index, and the data file is perfectly contiguous on disk, and the disk subsystem is not doing odd tricks with sectors, then the logical order may be the same as the physical order. May be. SQL will try when it creates or rebuilds the clustered index to put the pages contiguous and in order within the data file (it can't control fragmentation or placement of the file). It will not always be able to (ever rebuilt an index and seen that it had >0 fragmentation straight off?). The clustered index always enforces the logical order. It does not enforce the physical order.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-28 : 08:19:15
quote:
Originally posted by GilaMonster

A clustered index determines the logical order of the data in a table. Not the physical.

If there is 0% fragmentation of that index, and the data file is perfectly contiguous on disk, and the disk subsystem is not doing odd tricks with sectors, then the logical order may be the same as the physical order. May be. SQL will try when it creates or rebuilds the clustered index to put the pages contiguous and in order within the data file (it can't control fragmentation or placement of the file). It will not always be able to (ever rebuilt an index and seen that it had >0 fragmentation straight off?). The clustered index always enforces the logical order. It does not enforce the physical order.

--
Gail Shaw
SQL Server MVP


Thank you Gail!
Rose is a rose is a rose is a rose.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-28 : 08:39:46
Clustered index the leaf level is the data page.
Non clustered has a copy of the indexed columns with supporting balanced tree and the leaf level includes the included columns and either the clustered index columns plus a sequence if the clustered idex ix not unique or a rowid if no clustered index.

non clustered is usually faster for covering indexes, clustered has the advantage of a faster seek to access the whole data page but can cause fragmentation.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -