Drop or Delete.. which is better?

  • sthornton

    Ten Centuries

    Points: 1223


    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.


  • Helen-84411

    Hall of Fame

    Points: 3320


    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.

    Are you a born again. He is Jehova Jirah unto me

  • David.Poole

    SSC Guru

    Points: 75363

    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.

  • philcart


    Points: 47794

    If you drop the table, you'll also lose those ever dependable dependencies


    Colt 45 - the original point and click interface

  • sthornton

    Ten Centuries

    Points: 1223


    Id forgotten about TRUNCATE. I don't give it much use. ;-0

    Thanks guys

  • CAGreensfelder

    SSC Eights!

    Points: 919

    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. 

  • SueB


    Points: 1537

    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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply