XML FeedsI wrote a blogpost about the fact that there is a common myth that you can't rollback a truncate statement in SQL this post was written on June 13, 2007 and it showed you that you could rollback a truncate. Here is some code that shows that.
First create this very simple table
- CREATE TABLE dbo.TruncateTest (ID int IDENTITY PRIMARY KEY,
- SomeOtherCol varchar(49))
- GO
Add the following two rows
- INSERT dbo.TruncateTest VALUES(1)
- INSERT dbo.TruncateTest VALUES(1)
Now execute this whole block in one shot, you will see three resultsets, two of them will have two rows and one resultset will be empty
- SELECT * FROM dbo.TruncateTest -- 2 rows
- BEGIN TRAN
- TRUNCATE TABLE dbo.TruncateTest
- SELECT * FROM dbo.TruncateTest -- 0 rows
- ROLLBACK TRAN
- SELECT * FROM dbo.TruncateTest -- 2 rows again after rollback
Here is the output
ID SomeOtherCol ----------- ------------------------------------------------- 1 1 2 1 (2 row(s) affected) ID SomeOtherCol ----------- ------------------------------------------------- (0 row(s) affected) ID SomeOtherCol ----------- ------------------------------------------------- 1 1 2 1 (2 row(s) affected)
As you can see the table was empty at one point, however the table has the same two rows again, if you execute this query, you will see those two row again
- SELECT * FROM dbo.TruncateTest
What about Oracle, can you rollback a truncate statement?
In SQL Server the minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, I blogged about this as well in the post Why do you need additional privileges for truncate table compared to delete?
In Oracle to truncate a table, the table must be in your schema or you must have DROP ANY TABLE system privilege.
In Oracle a truncate statement is actually a DDL statement, you CANNOT rollback a truncate after it has happened. A truncate statement removes all rows and returns the freed space to the tablespace containing the table.
Please keep these differences in mind when working with different platforms, don't assume anything, it will bite you in the butt, always reference the documentation. For some more Oracle and SQL Server differences, see also my post Differences between Oracle and SQL Server when working with NULL and blank values
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.