Truncate/Delete

  • For the server what is better to use delete or truncate. I know truncate (deletes data faster) and delete slower, but for the performance what is better?

  • What do you mean by performance? Most of the time, that means speed, and you already said (correctly) that truncate is faster. So, what exactly do you mean?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • There's a lot more to consider than performance.

    Truncate deletes all the data in the table. Delete can have a where clause allowing conditional deletes.

    Truncate does not fire triggers, delete does.

    Truncate is not allowed on a replicated table or one with foreign key constraints, delete is.

    Truncate requires ALTER TABLE permissions, delete requires DELETE permissions

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • yulichka (4/13/2009)


    For the server what is better to use delete or truncate. I know truncate (deletes data faster) and delete slower, but for the performance what is better?

    :w00t:

    With all due respect to previous posters that took your joke seriously... was it a joke, wasn't it?

    Just let build a decision table based on your question...

    slower => bad performance

    faster => good performance

    😀

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • I had a case when We had to clean database nearly 45 GB. It was a vendor's product. Vendor gave a simple delete script to purge/delete unwanted historical data. DBA who was responsible for that product at my company spent hours deleting historical records and finally came to me if there is any way we could speed up this process.

    I then analyzed database and found there were 3 tables occupying almost 45 GB Data. I first thought I will truncate data but they had foreign key constraints build on those tables.

    I then performed following tasks,

    Step 1: Took a full backup.

    Step2: Script foreign key constraints on these three tables.

    Step3: Drop foreign key constraints on these three tables.

    Step4: Truncate 3 tables (took less than a second).

    Step5: Build foreign key constraints on those three tables using scripts I generated before dropping constraints.

    Whole process took less than a minute...

    I don't suggest any one to follow this route, unless they know what they are playing with...

    *** Play Safe.***

    ~ IM.

  • I would just add that delete operation is recorded in transaction log for each row but that is not the case with truncate. So while using delete statement it should be considered that it may also result in growth of transactional log file according to number of deleted rows.

    DBDigger Microsoft Data Platform Consultancy.

  • Moreover, if you really need to delete data based on some criteria you could do it in smaller batches using the ROWCOUNT statement. However, you need to keep in mind all of the above mentioned caveats.

  • Thank you everyone for your response, All I am trying to do delete data from the table where date >=30,to schedule a job to run every month. Is my transaction log will grow fast?

  • The growth of the log size would depend on how many records are deleted. You can limit the impact on the system and the log file by deleting in batch. You can change the rowcount to whatever number you like. You could also use the if condition to check the time of day and decide whether you want the script to continue to execute or exit. This will help you in making sure the delete process doesn't run in to business hours or any other nightly processing.

    SET NOCOUNT ON

    SET ROWCOUNT 10000 -- Or some other value

    WHILE 1 = 1

    BEGIN

    DELETE FROM table_name

    WHERE dtAccessed >= some_date

    IF @@ROWCOUNT = 0 OR check_current_time

    BREAK

    END

    SET ROWCOUNT 0

  • yulichka (4/14/2009)


    Thank you everyone for your response, All I am trying to do delete data from the table where date >=30,to schedule a job to run every month. Is my transaction log will grow fast?

    That depends on how much data you have in the table that needs to be deleted. If it is a lot, there is a way to write the delete routine to complete the data purge in batches so that you may control the growth of the transaction log by taking t-log backups between batches.

    We just need to know what your requirements are for the delete, including the volume (number of records) to be deleted monthly.

  • It will also depend on what recovery model your database is in. If you're in Simple, then batch-deletes probably won't cause it to grow much. If Full, then backups between batches will keep the transaction log from growing, but you might eat up even more disk space with the backups. (It's usually a bad idea to have a production database in Simple recovery, but sometimes it's necessary. We'd need more data on your situation and set up before being able to judge that point.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I run this: and get error:(he DELETE statement conflicted with the REFERENCE constraint "FK_packages_package_lists". The conflict occurred in database "CSWReportDatabase", table "dbo.packages", column 'packagelist_id'.

    The statement has been terminated.)

    My code:

    SET NOCOUNT ON

    SET ROWCOUNT 10000 ---

    WHILE 1 = 1

    BEGIN

    DELETE dbo.package_lists

    FROM dbo.package_lists INNER JOIN

    dbo.packages ON dbo.package_lists.packagelist_id = dbo.packages.packagelist_id

    WHERE (dbo.packages.void_flag = 0) AND (DATEDIFF(day, dbo.package_lists.shipdate, GETDATE()) >= 180)

    IF @@ROWCOUNT = 0

    BREAK

    END

    SET ROWCOUNT 0

  • yulichka (4/14/2009)


    I run this: and get error:(he DELETE statement conflicted with the REFERENCE constraint "FK_packages_package_lists". The conflict occurred in database "CSWReportDatabase", table "dbo.packages", column 'packagelist_id'.

    The statement has been terminated.)

    My code:

    SET NOCOUNT ON

    SET ROWCOUNT 10000 ---

    WHILE 1 = 1

    BEGIN

    DELETE dbo.package_lists

    FROM dbo.package_lists INNER JOIN

    dbo.packages ON dbo.package_lists.packagelist_id = dbo.packages.packagelist_id

    WHERE (dbo.packages.void_flag = 0) AND (DATEDIFF(day, dbo.package_lists.shipdate, GETDATE()) >= 180)

    IF @@ROWCOUNT = 0

    BREAK

    END

    SET ROWCOUNT 0

    Looks like you have a foreign key declared. that means you also have to delete the data from the child table as well.

  • How can I delet 2 tables together?Thank you

  • You can delete the data from the table which has the foreign key or you can disable constraint checks and then delete.

    So it really depends on your database design.

Viewing 15 posts - 1 through 15 (of 24 total)

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