| Author |
Topic |
|
ashutosh011283
Starting Member
6 Posts |
 Posted - 2011-07-21 : 08:11:47
|
| Hi All,I would like to know actual Difference between Cluster and Non-cluster indexes in sql server 2005 and why we use indexes in database ?ashutosh upadhyay |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-21 : 08:20:46
|
| Have a look at bol.Note indexes can be used as constraints as well as for performance.==========================================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. |
 |
|
|
ankit.choudhary4
Starting Member
1 Post |
Posted - 2011-07-21 : 08:29:28
|
| In Non Cluster indexing data is present in random order although they have logical order .Non cluster indexing is basically user on those table which needs to be update frequently.In cluster indexing data is blocked to certain index to match like you can take example B tree every parent has its left child greater then parent and right child smaller than parent.indexing is basically used to speed up data extraction from a table. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-21 : 08:52:19
|
| Not sure any of that is correct.In a clustered index the leaf level is the data page.Non-clustered is a copy of the indexed and included columns plus the clustered index columns and a sequence if the clustered index is not unique or a row identifier if the base table is a heap.Indexes are used for performance or to implement constraints.==========================================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. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-21 : 09:06:05
|
quote:
Originally posted by ankit.choudhary4
In Non Cluster indexing data is present in random order although they have logical order .Non cluster indexing is basically user on those table which needs to be update frequently.In cluster indexing data is blocked to certain index to match like you can take example B tree every parent has its left child greater then parent and right child smaller than parent.
That is completely wrong.See http://www.sqlservercentral.com/articles/Indexing/68563/ and http://www.sqlservercentral.com/articles/Indexing/68636/--Gail ShawSQL Server MVP |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-07-21 : 16:38:00
|
| clustered indexes are boysnon-clustered indexes are girlsBe One with the OptimizerTG |
|
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2011-07-21 : 16:44:29
|
quote:
Originally posted by TG
clustered indexes are boysnon-clustered indexes are girlsBe One with the OptimizerTG
So you are saying that all the boys have unique characteristics and girls can be grouped by characteristics? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-07-21 : 17:08:46
|
quote:
Originally posted by Ghanta
quote:
Originally posted by TG
clustered indexes are boysnon-clustered indexes are girlsBe One with the OptimizerTG
So you are saying that all the boys have unique characteristics and girls can be grouped by characteristics?
Quit bustin' my clusters! Just trying to explain where data pages come from.Be One with the OptimizerTG |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-21 : 17:37:51
|
| You know, if you look at the indexes from the point of data pages, there's no difference at all between a clustered and a nonclustered index. They look the same.--Gail ShawSQL Server MVP |
|
|
|
Jason W
Starting Member
19 Posts |
Posted - 2011-07-21 : 17:46:46
|
| Absolutely hilarious!Here is some additional material on indexes in case you were interested:http://www.sqloptimizationsschool.com/Pages/Basic%20Concepts/Indexes.aspxhttp://www.sqloptimizationsschool.com/Pages/Advanced%20Concepts/Utilizing%20Indexes.aspx |
|
|
|
|
Difference between Cluster and Non-cluster index