Impact of bulk insertion and bulk deletion to the MS SQL server 2008

Does anybody know what's the impact of MSSQL 2008 Database when executing insert and delete SQL statement for around 100,000 records each run after a period of time?

I heard from my client saying that for mysql and for its specific data type, after loading and clearing the database for a period of time, the data will become fragmented/corrupted. I wonder if this also happens to MS SQL? Or what will be the possible impact to the database?

Right now the statements we use to load and reload the data in to all the tables in the database are simple INSERT and DELETE statements.

Please advice. Thank you in advance! :)

-Shen

Answers


The transaction log will likely grow due to all the inserts/deletes, and depending on the data which is being deleted/inserted and table structure there will likely be data fragmentation

The data won't be 'corrupted' - if this is happening in MySql, it sounds like a bug in that particular storage engine. Fragmentation shouldn't corrupt a database, but does hamper performance

You can combat this using a table rebuild, a table recreate or a reorganise.

There's plenty of good info regarding fragmentation online. A good article is here:

http://www.simple-talk.com/sql/database-administration/defragmenting-indexes-in-sql-server-2005-and-2008/


Need Your Help

Change default screen options posts per page value in Wordpress Admin through functions.php

php wordpress

Thanks in advance for any support you can provide, I really appreciate it!

Is it possible to check if a member function is defined for a class even if the member is inherited from an unknown base class

c++ sfinae

I found similar questions and answers like this one. However, as I tried out, this SFINAE tests only succeeded if the tested member is directly defined in the class being tested. For example the