April 22, 2010 at 10:27 am
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[/url]
Learn Extended Events
April 22, 2010 at 10:28 am
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.
April 22, 2010 at 10:28 am
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[/url]
Learn Extended Events
April 22, 2010 at 10:53 am
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 😛
April 22, 2010 at 10:58 am
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 😛
: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[/url]
Learn Extended Events
April 22, 2010 at 1:20 pm
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.
David
:hehe::hehe::hehe::-P:-P:-D:-D
Thanks for the links... I'm sure many of us need to read this.
April 22, 2010 at 7:24 pm
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.
April 23, 2010 at 5:42 am
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 ha:hehe::-D
- Swaroop R -
April 23, 2010 at 5:53 am
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.
David
:hehe::hehe::hehe::-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 value:cool: to me when i discussed this Myth;-) point with my friends..
- Swaroop R -
April 23, 2010 at 8:55 am
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.
April 23, 2010 at 1:58 pm
Brandie Tarvin (4/23/2010)
SwaroopRaj (4/23/2010)
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.
If I understand all the mexchanisc correct, then the actual TRUNCATE is done by deallocating entire pages. The log file will contain only the fact that page so-and-so was deallocated, but the log backup (if one is taken) will also include a copy of that page. So the pages that were deallocated are not available for reuse until the tran log has been backed up.
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.
This is not quite correct. A DELETE processes individual rows. Each row deleted gets an entry in the log file, in ALL recovery models (even simple - otherwise, SQL Server would be unable to rollback or to recover after a crash). And all those entries are also written to the log backup.
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.
One that has already been committed?:Whistling:
Seriously, I agree. In fact, even most schema changes can be rolled back.
April 23, 2010 at 2:01 pm
Wow 72 posts for this "little" question. I can offically say this the best QOTD I ever posted :w00t::hehe:.
April 23, 2010 at 3:29 pm
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.
That's fair. How about stating that all questions must be based on SQL 2008?
April 23, 2010 at 4:32 pm
Hugo Kornelis (4/23/2010)
The log file will contain only the fact that page so-and-so was deallocated, but the log backup (if one is taken) will also include a copy of that page.
I'm not sure whether I'm misunderstanding you, but to my mind this makes no sense (and does not correspond to my practical experience):
- Use a DB in a test environment where you can mess with the data and backups, and let's assume the DB is set to is fully logged.
- Fill a table with a couple GBs of data (using your favorite data-generation method)
- Checkpoint, just to be safe
- Backup the transaction log (ignore this backup file - if you like you can use WITH NO_LOG/TRUNCATE_ONLY, we don't need log chain continuity)
- Shrink the transaction log
-> the transaction log is down to a few MB in size
- Truncate the large table with all that test data.
- Checkpoint, just to be safe
- Back up the transaction log
-> take a look at the size of the transaction log backup... a few MB in size?
I must admit I have not followed these explicit steps in preparation for this post, but does anyone expect behaviour different from this? (does anyone expect the transaction log backup to contain copies of the deallocated pages??)
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 23, 2010 at 5:22 pm
Tao Klerks (4/23/2010)
Hugo Kornelis (4/23/2010)
The log file will contain only the fact that page so-and-so was deallocated, but the log backup (if one is taken) will also include a copy of that page.I'm not sure whether I'm misunderstanding you, but to my mind this makes no sense (and does not correspond to my practical experience):
- Use a DB in a test environment where you can mess with the data and backups, and let's assume the DB is set to is fully logged.
- Fill a table with a couple GBs of data (using your favorite data-generation method)
- Checkpoint, just to be safe
- Backup the transaction log (ignore this backup file - if you like you can use WITH NO_LOG/TRUNCATE_ONLY, we don't need log chain continuity)
- Shrink the transaction log
-> the transaction log is down to a few MB in size
- Truncate the large table with all that test data.
- Checkpoint, just to be safe
- Back up the transaction log
-> take a look at the size of the transaction log backup... a few MB in size?
I must admit I have not followed these explicit steps in preparation for this post, but does anyone expect behaviour different from this? (does anyone expect the transaction log backup to contain copies of the deallocated pages??)
That's an interesting theory, care to script it out and prove it?
Viewing 15 posts - 61 through 75 (of 87 total)
You must be logged in to reply to this topic. Login to reply