Truncate table query

Question

Post reply

Truncate table query

September 12, 2014 at 6:18 am

This was removed by the editor as SPAM
September 12, 2014 at 8:13 am

This was removed by the editor as SPAM
September 12, 2014 at 1:07 pm

This was removed by the editor as SPAM
September 12, 2014 at 2:58 pm

This was removed by the editor as SPAM

Viewing 15 posts - 31 through 45 (of 46 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Iulian -207023 (9/12/2014)

I found this, on msdn, it must be on a different context and that would explain it or it can be a typo as well, who knows ... not so interesting to me, I understand what minimal logged means now. Thanks for that.

It says:

TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.

what I would be curious is: what triggers are supposed to fire ?

Oh look. Books Online is wrong. Again.

Truncate does not fire triggers. It is however pretty easy to test and see that it is logged. There are no such things as unlogged data modification operations in SQL.

It is also true that Truncate cannot be used on a table that's marked for replication. That's a limitation of replication though.

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Koen Verbeeck (9/12/2014)

Replication doesn't need triggers

Merge does.

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

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 3

GilaMonster (9/12/2014)

Koen Verbeeck (9/12/2014)

Replication doesn't need triggers

Merge does.

Right. I stopped reading after transactional replication.

The sentence right after says merge replication uses triggers. :blush:

Thanks for the correction.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Iulian -207023 SSCertifiable Points: 7510 More actions · Answer 4

Stewart "Arturius" Campbell (9/12/2014)

Iulian -207023 (9/12/2014)

GilaMonster (9/12/2014)

Meow Now (9/11/2014)

I was asked this very thing in an interview a while back. Except, they wanted to know if a TRUNCATE was fully or minimally logged. While I mentioned that all of the data could be rolled back with the use of a transaction, there was no way to roll back individual records...so minimally logged was my answer. Their follow up question was whether or not the recovery model would make any difference. I said no, but they just stared at me like I was crazy.

It's fully logged.

A minimally logged operation is one where the logging behavior changed between the recovery models, hence both their follow up question and reaction to your answer makes sense.

Minimally logged operations are all insert-based (select into, bulk insert, bcp in, etc)

I found this, on msdn, it must be on a different context and that would explain it or it can be a typo as well, who knows ... not so interesting to me, I understand what minimal logged means now. Thanks for that.

It says:

TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.

what I would be curious is: what triggers are supposed to fire ?

Cheers,

Iulian

Suppose you have table dbo.x, with any type of trigger on it.

If you execute a truncate table dbo.x, the relevant trigger on table x will not execute.

Remember, TRUNCATE TABLE is a DDL command, not a DML.

OK I got it now. yes Truncate sounds very much like a DDL statement and it makes sens not to fire triggers.

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 5

Kick6Tiger

SSCrazy

Points: 2380

September 12, 2014 at 7:36 am

#1745086

That does make sense. I always knew it was a DDL and not a DML operation, I just never really gave much thought to the definition of fully vs minimally logged.

Aigle de Guerre!

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

Stewart "Arturius" Campbell (9/12/2014)

Iulian -207023 (9/12/2014)

OK I got it now. yes Truncate sounds very much like a DDL statement and it makes sens not to fire triggers.

Just to demo, attached is a script that clearly shows the effect of TRUNCATE TABLE on a trigger.

Sweet - a demo with which to learn.

Thanks.

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

Hugo Kornelis SSC Guru Points: 64748 More actions · Answer 7

Koen Verbeeck (9/11/2014)

Please don't say truncate table data isn't logged unless you understand the concepts involved.

😉

The Myth that DROP and TRUNCATE TABLE are Non-Logged

Allow me to rephrase that.

Please don't say truncate table data isn't logged.

(The second part of your sentence is actually redundant; if you understand the concepts involved you would never say such a thing.)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Iulian -207023 SSCertifiable Points: 7510 More actions · Answer 8

Stewart "Arturius" Campbell (9/12/2014)

Iulian -207023 (9/12/2014)

OK I got it now. yes Truncate sounds very much like a DDL statement and it makes sens not to fire triggers.

Just to demo, attached is a script that clearly shows the effect of TRUNCATE TABLE on a trigger.

I can see it , thanks a lot for putting together the demo

It first logs the inserts into AuditTrail , then truncate the table and rollback

All the time , I mean

before the transaction begin,

after the truncate table inside the transaction

and after rollback

the AutditTrail shows the same content.

I think that proves the Truncate Table does not fire trigger.

But I have one doubt that I would be curios about, and that is

Here I think we have DML triggers, while Truncate Table is a DDL statement so what if we make a DDL trigger and see if that one is fired.

but this is just a curiosity and is weekend here iepyyy iee 🙂 so we shall pick it again on another occasion, maybe next week.

I have to admit this is a lovely talk.

Have a nice weekend!

Iulian

TomThomson SSC Guru Points: 104773 More actions · Answer 9

GilaMonster (9/12/2014)

It is also true that Truncate cannot be used on a table that's marked for replication. That's a limitation of replication though.

It's a particular case of a more general statement (at least one that was true for SQL 2000 and I believe it's still true): replication replicates DML statements, and DDL statements on replicated objects are either forbidden or have special forms and/or restrictions.

It's an interesting thing in that it demonstrates one of (i) that SQL Server isn't a Relational DBMS at all or (ii) that SQL Server fails in some respects to be fully relational or (iii) that Replication is impossible in a relational DBMS, depending on how one interprets "relational". In my view, anyone who believes in (iii) is an idiot, while anyone who believes in (i) is a pedant; personally I believe that (ii) is the only sane position, but of course if one wants to be pedantic (or to clown, something I sometimes can't resist) (i) is clearly true as well (just look at sum of empty set and sum of set including both nulls and non-null values).

Tom

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 10

Stewart "Arturius" Campbell (9/12/2014)

Investigate the impact of a truncate table statement and it's interaction with a DDL trigger and post the results as either a QotD or an article.

That's easy. None whatsoever.

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

TomThomson (9/15/2014)

replication replicates DML statements, and DDL statements on replicated objects are either forbidden or have special forms and/or restrictions.

It's got better. Alter table replicates now. Drop table isn;t allowed, the table has to be removed from the publication first and newly created tables aren't added (good reasons there at least). 1 out of three isn't bad.

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