Deleting large volume of data ..

Hi,
Currently we are integrating with a POS system. We would be importing
millions of records into our system from a SQL Server 2005 database. After
the import, we should clear (ie delete all records) in the SQL Server
database.
Ideally I want to
- write stored procs for deleting data and
- keep the system and transaction log resources to as low as possible
My query is - as a general rule, what would be the best way to delete data
of this size?
Thanks,
Harish Mohanbabu
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
Two ways:
1) If the table is not referenced by other tables via foreign keys, use
TRUNCATE table.
2) Delete in chunks - say, 10,000 rows at a time - using DELETE TOP
(10000).
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Harish Mohanbabu" <Axapta@.online.nospam> wrote in message
news:754FE6E6-EE6E-4E76-9845-5FB6DD83EFAA@.microsoft.com...
Hi,
Currently we are integrating with a POS system. We would be importing
millions of records into our system from a SQL Server 2005 database. After
the import, we should clear (ie delete all records) in the SQL Server
database.
Ideally I want to
- write stored procs for deleting data and
- keep the system and transaction log resources to as low as possible
My query is - as a general rule, what would be the best way to delete data
of this size?
Thanks,
Harish Mohanbabu
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
|||Hi Tom,
Thanks for your suggestion. Good news is there is no FK reference. So I
guess 'Truncate' would be the obvious choice.
Thanks once again,
Harish Mohanbabu
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
"Tom Moreau" wrote:

> Two ways:
> 1) If the table is not referenced by other tables via foreign keys, use
> TRUNCATE table.
> 2) Delete in chunks - say, 10,000 rows at a time - using DELETE TOP
> (10000).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
|||And TRUNCATE won't increase yout transaction log size as well...
"Harish Mohanbabu" <Axapta@.online.nospam> wrote in message
news:3509DF4B-7CA9-457B-AC12-58B8902ED294@.microsoft.com...[vbcol=seagreen]
> Hi Tom,
> Thanks for your suggestion. Good news is there is no FK reference. So I
> guess 'Truncate' would be the obvious choice.
> Thanks once again,
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/
> "Tom Moreau" wrote:

Deleting large volume of data ..

Hi,
Currently we are integrating with a POS system. We would be importing
millions of records into our system from a SQL Server 2005 database. After
the import, we should clear (ie delete all records) in the SQL Server
database.
Ideally I want to
- write stored procs for deleting data and
- keep the system and transaction log resources to as low as possible
My query is - as a general rule, what would be the best way to delete data
of this size?
Thanks,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/Two ways:
1) If the table is not referenced by other tables via foreign keys, use
TRUNCATE table.
2) Delete in chunks - say, 10,000 rows at a time - using DELETE TOP
(10000).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Harish Mohanbabu" <Axapta@.online.nospam> wrote in message
news:754FE6E6-EE6E-4E76-9845-5FB6DD83EFAA@.microsoft.com...
Hi,
Currently we are integrating with a POS system. We would be importing
millions of records into our system from a SQL Server 2005 database. After
the import, we should clear (ie delete all records) in the SQL Server
database.
Ideally I want to
- write stored procs for deleting data and
- keep the system and transaction log resources to as low as possible
My query is - as a general rule, what would be the best way to delete data
of this size?
Thanks,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/|||Hi Tom,
Thanks for your suggestion. Good news is there is no FK reference. So I
guess 'Truncate' would be the obvious choice.
Thanks once again,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
"Tom Moreau" wrote:

> Two ways:
> 1) If the table is not referenced by other tables via foreign keys, use
> TRUNCATE table.
> 2) Delete in chunks - say, 10,000 rows at a time - using DELETE TOP
> (10000).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau|||And TRUNCATE won't increase yout transaction log size as well...
"Harish Mohanbabu" <Axapta@.online.nospam> wrote in message
news:3509DF4B-7CA9-457B-AC12-58B8902ED294@.microsoft.com...[vbcol=seagreen]
> Hi Tom,
> Thanks for your suggestion. Good news is there is no FK reference. So I
> guess 'Truncate' would be the obvious choice.
> Thanks once again,
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/
> "Tom Moreau" wrote:
>