| Author |
Topic |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2006-11-14 : 09:21:16
|
| guys,What is the difference between clustered index and non-clustered index. Currently I have column with duplicate values which index will give me a better performance clustered index or non-clustered index.thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-14 : 09:24:08
|
| Clustered index forces the pages to be physically sorted on disk. That's why you only can have on clustered index on a table.Since it is sorted, it is much faster to find duplicates.Peter LarssonHelsingborg, Sweden |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-14 : 10:07:46
|
| Only when inserting randomly?If all inserts are made sorted by same order and column as index, is the insert slow then?Peter LarssonHelsingborg, Sweden |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-11-14 : 10:41:09
|
| You are correct Peso, if you insert in the clustered index order you will not see a performance degrade. Inserting in random order also causes alot of page splitting and fragmentation, which is why having a clustered index on i.e. a GUID is not such a good idea.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-14 : 10:45:43
|
| I thought inserting records randomly would trigger a reorganization for each page, on disk, for each record inserted.Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-14 : 10:49:43
|
| Depending on FILL FACTOR perhaps?? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-11-15 : 08:54:13
|
Your'e not "yanking my chain" here are you??  I would think this was a walk in the park for you guys...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-11-15 : 09:21:49
|
quote:
Originally posted by Peso
I thought inserting records randomly would trigger a reorganization for each page, on disk, for each record inserted.
Pete, can I call you Pete?uh, noimagine the overhead...if there's room enough on the page, it goes there, if no a new page is created...only when you reorg the page, does everything et put "back" in place.The physical order of data in a database has no meaningIn regards to the question, I think you would be hard pressed to find a significant difference beteween the 2....The size of the index is probably more importantBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-11-15 : 09:34:42
|
| Imagine ten 10-ounce cups standing in a row filled with different sodas. Each of them is filled 80% (fill factor), and suddenly you have 5 ounces of Diet MountainDew that needs a cup. Diet MountainDew which happens to be in cup #4 only has room for 2 ounces so you have to add another cup for the remaining 3. The additional cup is placed at the end of the line since moving all the other cups is more work. And having Diet MountainDew in to cups in separate places would qualify as fragmentation. Might not be the best example but hopefully it proves the point...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-15 : 15:54:44
|
| Yeah, but you can get two ounces in before you have to find a suitable place for a new, empty, cup.If there are 1,000,000 different sodas and new sodas are added "at random" then the likelihood is that you won't need to do the new-cup routine very often - and if you reorganise your Dinner fairly often the chances reduce further."I don't think we are in Kansas any more, Tonto!"Kristen |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-15 : 20:06:04
|
quote:
Originally posted by LumbagoInserting in random order also causes alot of page splitting and fragmentation, which is why having a clustered index on i.e. a GUID is not such a good idea.
Clustered index on a GUID is ok if you use NEWSEQUENTIALID() in 2005, or use Gert Draper's XPGUID library in 2000: http://www.sqldev.net/xp/xpguid.htm
SqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-15 : 20:54:44
|
quote:
The physical order of data in a database has no meaning
It does not have meaning at the disk level but order of the pages assigned to a clustered or non-clustered index certainly has meaning. It doesn't mean you get incorrect data from queries but it will affect performance. That's why there is such a thing as fragmentation and why there are commands like DBCC SHOWCONTIG, DBCC INDEXDEFRAG (ALTER INDEX REORGANIZE in 2005), and DBCC DBREINDEX (ALTER INDEX REBUILD in 2005). A clustered index will prevent large scale fragmentation if you create it on values that do not change often. It is not just about inserts, updates would cause whole rows to move pages if the values in the clustering key change often.Also, the clustering key is stored in every non-clustered index to perform row lookups from non-clustered indexes, so it is very important that the clustering key be small. |
|
|
|
|
Clustered Index