Thursday 28 May 2015

How to delete one million records using SQL query?


How to delete one million records using SQL query?  Or if you have table which contains large amount of data which command will you use for removing data, truncate or delete?

First understand difference between Delete and Truncate

1.       Truncate is fast delete is slow.
2.       Truncate doesn't do logging delete logs on per row basis.
3.        Rollback is possible with delete not with truncate until specifically supported by vendor.
4.       Truncate doesn't fire trigger, delete does.
5.        Don't delete, truncate it when it comes to purge tables.
6.       Truncate reset identity column in table if any, delete doesn't.
7.       Truncate is DDL while delete is DML (use this when you are writing exam)
8.        Truncate doesn't support where clause, delete does.
9.       You can use where in delete command but truncate won’t allow where clause

Example:

truncate table Orders;
delete  * from Orders; 
delete  * from Orders where Symbol="MSFT.NQ"

No comments:

Post a Comment

How to find table row count?

--Use below query to find table row count select so.name,sp.rows from sys.objects so inner join sys.partitions sp on so.object_id = sp.obj...