17. januar 2002 - 19:55Der er
3 kommentarer og 1 løsning
Clustered vs. nonclustered index
Til et mellemstort site anvender vi MS SQL 7 som backend database.
Vi begyndte at få problemer med performance på vores databaseserver, fordi vi havde glemt at indexere de forskellige tabeller i databasen.
Vi har nu indexeret de tabeller, der bruges meget, men har indtil nu brugt nonclustered indexes.
Er der nogen der kan forklare forskellen på clustered og nonclustered indexes, fortælle lidt omkring fordele og ulemper ved hver slags m.v.?
Det at vi indexerede tabellerne gjorde, at serveren blev omkring 50% mindre belastet, men ville vi kunne høste fordele af at anvende clustered indexes?
yezper>It would be much easier for you to read up on this in Books Online. I have just had a look myself and I think that a full explanation rather than a couple of lines would be to your anvantage. But here is one reason why you will end up using NONCLUSTERED more than CLUSTERED
Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index.
As far as I can see, the BOL doesn\'t state and pros and cons of using clustered instead of nonclustered indexes and vice verca. Can you tell me something about this? What performance issues do I have to consider etc.?
This is taken from Mocrosoft documnetation from a course I have been on. Reading this I understand it as saying that you should ALWAYS create a CLUSTERED (you can only create one) and then your NO-CLUSTERED. This is done for PERFORMANCE reasons. So you could say there are no \"fordele eller ulemper\" its just the correct way to do things. Thgats more than likely who you cant find anything on this in BOL.
Take the following actions to reduce the impact on performance when you create or use indexes.
Create Indexces on foreign keys.
Create CLUSTERED indexes before NONCLUSTERED because CLUSTERED change the physical row order of the table.
Consider creating composite indexes
Create multiple indexes for a table whcih is reda frequently
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
