August 17, 2011 at 6:43 am
Even according to the MSDN article
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
Emphesis mine.
August 17, 2011 at 6:52 am
mbova407 (8/17/2011)
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
Emphasis mine 😉
August 17, 2011 at 6:53 am
mbova407 (8/17/2011)
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
True- key word there is individual - truncate can be rolled back (see earlier script by stewartc-708166 7th post in this thread), therefore it must be logged
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 17, 2011 at 6:57 am
easy one today
August 17, 2011 at 6:57 am
And BOL clearly states
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log
TRUNCATE TABLE is a logged operation. All write operations in SQL Server is logged (except writes in the version store).
August 17, 2011 at 7:06 am
Toreador (8/17/2011)
mbova407 (8/17/2011)
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.Emphasis mine 😉
Please test this yourself, it would be better before arguing. 😉
M&M
August 17, 2011 at 7:27 am
Easy one today, although it seems the 'truncate is not logged'-myth stills seems pretty persistent.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 17, 2011 at 7:40 am
Nice refresher, thanks!
August 17, 2011 at 7:42 am
stewartc-708166 (8/17/2011)
Kwex (8/17/2011)
I missed it because I also selected an option that said "TRUNCATE cannot be logged". Baffled to know this is otherwise 🙂While the details of the truncate (i.e. the removal of individual records) is not logged, the truncate itself is logged.
use this as an example
CREATE SCHEMA testgo
CREATE TABLE test.TestTruncate (id INT IDENTITY(1,1), [description] NVARCHAR(100), value NCHAR(6))
Go
INSERT test.TestTruncate(description, value)
VALUES ('this is test line 1','test1'),('this is test line 2','test2'),('this is test line 3','test3'),('this is test line 4','test4'),
('this is test line 5','test5'),('this is test line 6','test6'),('this is test line 7','test7'),('this is test line 8','test8'),
('this is test line 9','test9'),('this is test line 10','test10'),('this is test line 11','test11'),('this is test line 12','test12'),
('this is test line 13','test13'),('this is test line 14','test14'),('this is test line 15','test15'),('this is test line 16','test16'),
('this is test line 17','test17'),('this is test line 18','test18'),('this is test line 18','test18'),('this is test line 20','test20')
SELECT * FROM test.TestTruncate
BEGIN TRANSACTION
TRUNCATE TABLE test.TestTruncate
SELECT * FROM test.TestTruncate
ROLLBACK TRANSACTION
SELECT * FROM test.TestTruncate
DROP TABLE test.TestTruncate
Go
DROP SCHEMA test
GO
Thanks for your example!
August 17, 2011 at 7:46 am
Good one, thanks.
August 17, 2011 at 7:48 am
jcrawf02 (8/17/2011)
I missed it because I couldn't decide on my second choice, and went with "truncate is not logged" assuming the author was making that common error (um, sorry Stuart).
No worries - I thought that QOTD was to test one's own SQL knowledge, not to second guess the contributor's knowledge 😉
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 17, 2011 at 7:52 am
Stuart Davies (8/17/2011)
jcrawf02 (8/17/2011)
I missed it because I couldn't decide on my second choice, and went with "truncate is not logged" assuming the author was making that common error (um, sorry Stuart).No worries - I thought that QOTD was to test one's own SQL knowledge, not to second guess the contributor's knowledge 😉
oh, good, glad I could clear that up for you :-D:-D
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 17, 2011 at 8:45 am
Nice question Stuart.
Aparently half of us needed to learn more about Truncate and Delete.
😎
August 17, 2011 at 9:17 am
Nice question. I knew better than to choose truncate as not being logged because of previous QOTD's and articles regarding this same thing. the truncate does not log myth has been beaten out of me by this site. Thanks SSC! Considering how many have answered incorrectly, it looks like this was a much needed question and that many have learned something today.
August 17, 2011 at 9:34 am
Basics type of question.
Still astounds me that sooooo many people believe that truncate is not logged. At the time I answered, 44% of respondents selected that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply