Truncate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Truncate

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-03 : 14:36:22

Gurus,

Why we are calling truncate as a DDL ?

Thanks
Krishna

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-03 : 14:44:45

maybe because a truncate cannot be rolled back? That is how it is different than a DELETE for example.

-ec

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 14:47:07

acctaully a truncate can be rolled back.


create table t1 (id int)
insert into t1
select 1 union all
select 2 union all
select 3

select * from t1

begin tran 
truncate table t1
rollback

select * from t1

begin tran 
truncate table t1
commit

select * from t1

drop table t1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-03 : 14:47:47

Yes Spirit!!! We can rollback the truncate.

Gurus -
This is not an interview question.
-- Krishna

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-03 : 14:53:10

hmmm, even a drop table statement can be rolled back using the test code spirit posted. This is different behavior than other RDBMS btw. For example, in Oracle you cannot rollback a drop table statement or a truncate table statement.

So, I don't know the answer to this question then as it pertains to SQL Server.

-ec

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 14:53:58

the difference is in how it uses the transaction log.
with delete a deletion of each row is stored in transaction log
while with truncate only page deallocations are stored in tran log.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-03 : 14:54:35

Yes EC, in Oracle we can't rollback the Truncate. But SQl we can.

-- Krishna

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 14:55:41

quote:
Originally posted by eyechart

hmmm, even a drop table statement can be rolled back using the test code spirit posted. This is different behavior than other RDBMS btw. For example, in Oracle you cannot rollback a drop table statement or a truncate table statement.

So, I don't know the answer to this question then as it pertains to SQL Server.

-ec

isn't there a kind of a trasaction bucket or something that holds all commited/rollbacked transactions and the user must commit this bucket to the db himself?
i thought i read something about this a while ago...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-03 : 14:56:38

Spirit,

Could you tell me the exact answer please. Still i am searching the answers in google.

Thanks
Krishna

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-03 : 14:58:31

quote:
Originally posted by spirit1

the difference is in how it uses the transaction log.
with delete a deletion of each row is stored in transaction log
while with truncate only page deallocations are stored in tran log.

nice. I just looked this up in Kalen's book and that is exactly what she said.

This seems to be a good interview question.

-ec

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 15:00:32

my opinion is that it's ddl because you can't filter modified data with it.
you simply remove all the pages in a table from disk.
also truncate releases all empty pages from a table.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 15:02:36

@ec:
also note the aquired locks on the table with delete and truncate:
delete locks every row
truncate rows every page/table but not row so fewer locks are used (less memory)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-03 : 15:03:31

Sprit,
I have a small doubt over here. How about the page status while rollback the truncate statment?

Thanks
Krishna

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 15:04:14

what do you mean "page status"?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-03 : 15:05:14

pages in a table

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-03 : 15:09:33

Thanks Spirit and EC

-- Krishna

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-03 : 15:14:20

I think we just answered a homework question. oh well...

-ec

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 15:15:54

it's ok... we learned something new

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-03 : 15:16:44

Maybe EC. But I want to clarify the doubt. If i did a wrong post please forgive me

-- Krishna

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 15:17:58

don't worry about it. it good to clear up things like that.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-03 : 15:18:59

I got something new from you guys.

Once again Thanks guys

-- krishna

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources