Quote
Truncate is fast ...
Yes it is.
Quote
... but data is lost ...
Of
course it is; that's what "delete" does!
Quote
I want to delete records selectively with 'where' clause.
Then use a delete statement with a where clause, stop thinking about linear, Procedural logic and start thinking in set-based, Relational ways.
Quote
I hope that clarifies why I use delete and not truncate.
Not in the slightest.
You
say that you want to delete records selectively, but the end result of your procedure is to delete
all of the rows in the table, i.e. it keeps deleting 'n' rows at a time until there are
no rows left to delete.
OK, it's a slightly more "sociable" way of doing ...
delete from T1 ;
... for a large number of rows but it's
far less "sociable" than ...
truncate table T1 ;
... which is close to instantaneous (
assuming that you can use it in your case).
Regards, Phill W.