Drop or Delete.. which is better?
-
Hi,
I need to deleteor drop and re-create a table with around 150,000 rows in it.
Which is more advatageous regarding speed?
I don't mind if things are not logged etc, as I will be repopulating the table.
Thanks,
-
Hai,
If you drop a table it is faster. But with all its side effects like You need to recreate the table from scratch. (Have the table definition script). . You cannot recover the data back. You need to have the data else where because this is a non logged operation
On the other hand delete does a row by row logging on transaction table and if your transaction log file is not large enough to handle the delete, all other transaction will start rolling back because of insuffient log space.
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
Try TRUNCATE TABLE dbo.YourTable if you are not concerned with logging.
I always script my databases but the problem with drop is where there are dependent views and stored procedures. You have to sp_refreshview and sp_recompile all those views and procs respectively otherwise your execution plans won't be opt.
-
If you drop the table, you'll also lose those ever dependable dependencies
--------------------
Colt 45 - the original point and click interface
-
Hi,
Id forgotten about TRUNCATE. I don't give it much use. ;-0
Thanks guys
-
TRUNCATE also resets any IDENTITY column so it starts over from the seed value. DELETE does not. After a DELETE, new rows are numbered from where it left off.
-
Also when you do a TRUNCATE you don't lose the user permissions you have set up on the table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply