Fragmentation?

Sql Server 2000.

For simplicity, I am avoiding minor details.

Here is a scenario.

Create Table T (T_ID int PK, …..) and this table is referenced by 20 other places on T_ID.

And these 20 tables have T_ID as PK or part of PK.

We have large volume of deletes in T and in the other 20 tables based on T_ID.

Once the deletes are done, will dropping and re-creating the FK’s that reference the T_ID

help reduce the fragmentation caused by the deletes?

In 2000 DBCC ShowConTig is your best tool to find out about fragmentations and you can actually count the IAM(index allocation mapping) pages. I think you could compromise data integrity with deletes and constraints drops. Run a search for DBCC ShowConTig and IAM pages in the BOL. Hope this helps.

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/24/645803.aspx

|||

If you want to maintain the PK-FK relationship when removing data, you may wish to explore setting the CASCADE ON DELETE property for the tables with a FK. Then when you delete a row from table [T], related rows in all other tables will also delete.

But that doens't address the fragmentation issue. Caddre's suggestion is a good starting place to understand the level of fragmentation in your database.

|||

Sorry for the delayed response. Apparently what I was looking at is a incomplete script where the developer forgot to drop and recreate the PK.

As number of rows in the table vs number of rows that are going to be deleted are in the ratio 2:1, it is certain there will be fragmentation and he wanted to rebuild the primary key to reduce fragmentation and for that he was dropping the fk's.

I have gone over the sqlserverstorageengine blogs and it certainly helped to strengthen my knowledge.