SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Truncate rollback


Truncate rollback

Author
Message
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65519 Visits: 18570
Steve Jones - Editor (4/22/2010)
I think this was worded a little poorly, and I didn't catch the insert issue. I ran the code on 2008, it worked, I let the question go. I thought the rollback/truncate was tricky enough to be worth 2 points.

I have added 2008 to the question header, as well as noted in the answer for error, "error on the last SELECT".

The debate is interesting here, but for those of you that say that the question isn't fair because it's 2008 specific, 2008 isn't even the current version today. SQL Server 2008 R2 is. I would think that after a year and a half, that you would expect that 2008 is the subject of most questions.

SQL 2000 is EOL, SQL 2005 is getting close to a complete end of support (it's 2010), regardless of what's in *your* environment, consider 2008 to be the standard.


Thanks for clearing all that up Steve.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37101 Visits: 9268
The person who posted the question should have specified environment. I didn't have to run the code to know the VALUES statement wouldn't work in 2005. Therefore, I chose "Error" even though the Error answer was wrong. EDIT - I thought there was a typo in the last answer. It's happened before.

I was tempted to choose 3, but couldn't figure out how the VALUES statement would work. If I'd known what edition, it would have changed the way I read the question.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65519 Visits: 18570
Trey Staker (4/22/2010)

I got the question wrong. I'm kicking myself because it is something I should have gotten right, I just clicked the wrong box and hit submit before I had a chance to change it, noticed it right as I hit the button. doh.


It has happened to me too - I hate it when that happens.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Ian Elliott
Ian Elliott
Old Hand
Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)

Group: General Forum Members
Points: 387 Visits: 971
CirquedeSQLeil (4/22/2010)
Hugo makes a great point. If everything is explicitly laid out so as to invoke no thought, then the question may be too easy and far too few will learn anything at all.
Also, since these QODs are more of a learning opportunity and community involvement opportunity, they are no less perfect than certification questions.


Yep he does and as I stated in my response completely explicit is one thing but specifying the important basics should always be done as that rarely detracts from the question.

Anyway time to move on to the next question I'm going to get wrong :-P
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65519 Visits: 18570
Ian Elliott (4/22/2010)
CirquedeSQLeil (4/22/2010)
Hugo makes a great point. If everything is explicitly laid out so as to invoke no thought, then the question may be too easy and far too few will learn anything at all.
Also, since these QODs are more of a learning opportunity and community involvement opportunity, they are no less perfect than certification questions.


Yep he does and as I stated in my response completely explicit is one thing but specifying the important basics should always be done as that rarely detracts from the question.

Anyway time to move on to the next question I'm going to get wrong :-P

Cool:-D



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66225 Visits: 9671
David_Simpson (4/22/2010)

If you really need the points... you can have mine. I've tried spending them at Walmart but they wouldn't take them. ;-)

Speaking of learning something, Paul Randal has some great articles on what happens during a TRUNCATE statement on his blog.

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281930%29-TRUNCATE-TABLE-is-non-logged.aspx
http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-When-are-pages-from-a-truncated-table-reused.aspx

David



HeheHeheHehe:-P:-P:-D:-D


Thanks for the links... I'm sure many of us need to read this.
Open Minded
Open Minded
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 567
I am politely disagreeing with statement that 'SQL 2008 is the standard'.

Shouldn't it be the standard(s) we have (note I am using "we"), are those of what our members are using?

I also understand it may not be optimal to have to two SQL versions in your computers, even if one is running in a virtual machine. It will only be by force or migration work to have two versions.

I also understand the difficulty of people making QoDs. In my workplace, I too have difficult people.

Just to add something useful, I hope, I am humbly posting a workaround:


if right(left((SELECT @@VERSION), 25), 4) = '2008'
select '2008'
--insert into a (b) values (1), (2), (3)

if right(left((SELECT @@VERSION), 25), 4) = '2005'
begin
select '2005'
--insert into a (b) values (1)
--insert into a (b) values (2)
--insert into a (b) values (3
end
else
select '6.5?'


I do believe a lot of you can make it better.
SwaroopRaj
SwaroopRaj
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 24
Open Minded (4/22/2010)
I am politely disagreeing with statement that 'SQL 2008 is the standard'.

Shouldn't it be the standard(s) we have (note I am using "we"), are those of what our members are using?

I also understand it may not be optimal to have to two SQL versions in your computers, even if one is running in a virtual machine. It will only be by force or migration work to have two versions.

I also understand the difficulty of people making QoDs. In my workplace, I too have difficult people.

Just to add something useful, I hope, I am humbly posting a workaround:


if right(left((SELECT @@VERSION), 25), 4) = '2008'
select '2008'
--insert into a (b) values (1), (2), (3)

if right(left((SELECT @@VERSION), 25), 4) = '2005'
begin
select '2005'
--insert into a (b) values (1)
--insert into a (b) values (2)
--insert into a (b) values (3
end
else
select '6.5?'


I do believe a lot of you can make it better.



haha... it was a nice one at 6.5? ha haHehe:-D

- Swaroop R -
SwaroopRaj
SwaroopRaj
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 24
Ninja's_RGR'us (4/22/2010)
David_Simpson (4/22/2010)

If you really need the points... you can have mine. I've tried spending them at Walmart but they wouldn't take them. ;-)

Speaking of learning something, Paul Randal has some great articles on what happens during a TRUNCATE statement on his blog.

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281930%29-TRUNCATE-TABLE-is-non-logged.aspx
http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-When-are-pages-from-a-truncated-table-reused.aspx

David



HeheHeheHehe:-P:-P:-D:-D


Thanks for the links... I'm sure many of us need to read this.






yep.. i thought that 'truncate' would never rollback. That was the way i was taught in past. You proved that wrong for me now. Thanks for the support links... Really, they helped me a lot and it added valueCool to me when i discussed this Myth;-) point with my friends..

- Swaroop R -
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37101 Visits: 9268
SwaroopRaj (4/23/2010)

yep.. i thought that 'truncate' would never rollback. That was the way i was taught in past. You proved that wrong for me now.


The thing that trips most people up is the concept that Truncate is "not logged." I hear this concept a lot in discussions and in job interviews. And while it's broadly correct when answering the standard "Why is a Truncate better than a Delete" interview question, it's technically a wrong assumption.

Truncates are *minimally logged* (EDIT: in ALL database Recovery models), like having your database in Bulk-Logged Recovery Model. That means there are pointers to the pages of the just removed data that can yank that stuff back if needed.

Deletes are logged more than Truncates, (EDIT: being fully logged in FULL mode down to the row) even in Bulk-Logged mode, because I think (and I could be wrong here) the pointers are more finite, pointing to the actually extents instead of the pages.

So, yes, a Truncate can absolutely be rolled back. In fact, I'd be hard pressed to say what data change (not schema change) couldn't be rolled back at all.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search