April 21, 2022 at 6:17 am
Hi all, SQL is a new topic for me. What is the difference between DELETE and TRUNCATE statements?
April 21, 2022 at 6:39 am
Normally you use the DELETE statement. You don't say which SQL product you are using and I guess the TRUNCATE statement works differently in different products. But I would expect that it deletes all rows in a table. In Microsoft SQL Server (which is the RDBMS I work with), TRUNCATE TABLE is a more efficient operation compared to deleting all rows with DELETE, because a lot less is written to the transaction log. On the other hand, you cannot use TRUNCATE TABLE is there are referencing foreign keys. Also, you need heftier permissions to use TRUNCATE TABLE than for DELETE.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 21, 2022 at 4:14 pm
In addition to Erland's comments - delete can be used to remove specific rows where truncate is all or nothing. There are additional side-effects to be aware of with truncate vs delete (in SQL Server) - for example, if you have an identity column on the table then truncate reseeds the identity and delete does not.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 28, 2022 at 1:18 pm
Thank you for your answers. I will try it.
June 2, 2022 at 11:32 am
This was removed by the editor as SPAM
June 30, 2022 at 10:30 am
This was removed by the editor as SPAM
July 8, 2022 at 12:11 pm
This was removed by the editor as SPAM
July 8, 2022 at 12:12 pm
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply