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.
| Author |
Topic |
|
ankurgupta26
Starting Member
32 Posts |
 Posted - 2002-09-19 : 18:08:11
|
| Hi all,Have a look at following code :---------------------------------------begin transelect count(*) from table1 --returns 10truncate table table1select count(*) from table1 -- returns 0rollbackselect count(*) from table1 --returns 10....surprinsingly ??---------------------------------------I was under the impression that truncate statements are not logged in the transaction log. But, the above query has surprised me.Does any one know how wud the above query run on any other RDBMS like ORACLE ??Thanks !AnkuR. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-19 : 18:43:43
|
| A. It's part of a transaction. If you ROLLBACK the transaction all of the changes are undone. Another way of saying it, nothing is permanently changed until the transaction is committed. This is normal behavior.B. According to the Oracle documentation, you cannot roll back a TRUNCATE statement. I wouldn't know, I don't use Oracle. SQL Server and Oracle are different products that behave very differently, as your example demonstrates. Don't assume one product's behavior to be identical to the other's without thorough studying and testing. |
 |
|
|
saglamtimur
Yak Posting Veteran
91 Posts |
Posted - 2002-09-19 : 18:58:59
|
| I saw this article on kbalertz, but havent read yet. I think it could give you some info.(It s originally an MS article)Q257334 BUG: SQL Server Books Online States that TRUNCATE TABLE Is Nonlogged Direct linkt to this article:http://www.kbalertz.com/redir.aspx?kbs=27736 |
|
|
|
ankurgupta26
Starting Member
32 Posts |
Posted - 2002-09-20 : 14:41:00
|
| Does this mean this is a known BUG in SQL Server OR this is the way SQL server works ?Thanks !AnkuR. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-20 : 14:45:07
|
quote:
Does this mean this is a known BUG in SQL Server OR this is the way SQL server works ?
 Did you READ any of the replies?quote:
A. It's part of a transaction. If you ROLLBACK the transaction all of the changes are undone. Another way of saying it, nothing is permanently changed until the transaction is committed. This is normal behavior.B. According to the Oracle documentation, you cannot roll back a TRUNCATE statement. I wouldn't know, I don't use Oracle. SQL Server and Oracle are different products that behave very differently, as your example demonstrates. Don't assume one product's behavior to be identical to the other's without thorough studying and testing.
quote:
http://www.kbalertz.com/redir.aspx?kbs=27736
Why would you ever assume that this behavior is a BUG??? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-09-21 : 01:39:26
|
| Rob,I think because the article says BUG, he is thinking the behavior is a bug. In fact the bug is in the documentation, not the behavior of the product. That is the "Bug" that the article is refering to.-Chad |
|
|
|
ankurgupta26
Starting Member
32 Posts |
Posted - 2002-09-23 : 10:25:24
|
| It's because of this statment I got confused....."Q257334 BUG : SQL Server Books Online States that TRUNCATE TABLE Is Nonlogged Thanks for the clarification !!Thanks !AnkuR. |
|
|
|
|
|
|
|
|
Truncate withinn a transaction !