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.
| Author |
Topic |
|
udham1985
Starting Member
6 Posts |
 Posted - 2011-01-27 : 23:25:43
|
| HiCan 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 ShawSQL Server MVP |
 |
|
|
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. |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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 ShawSQL 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. |
|
|
|
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. |
|
|
|
|
|
|
|
|
Clustered Vs Non-Clustered Index