July 16, 2010 at 1:29 am
Very good question and a clear explanation. Specification of NOT NULL is obvious, I just hadn't woken up yet and didn't read the table schema properly... must drink my coffee first.
Thanks Sankar et Hugo.
July 16, 2010 at 5:47 am
For a second I read this:
Option 1 runs faster but Option 2 is the right choice.
instead of this:
Option 1 runs faster than Option 2 and is the right choice.
If I had read it correctly I would have answered it right. :/
Anyways, very nice and detailed question with a very good explanation.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
July 16, 2010 at 3:19 pm
Sankar Reddy (7/15/2010)
[font="Courier New"]I am glad this QoD has helped people learn new things. The credit should directly go to Hugo Kornelis for his excellent feedback otherwise this would have been an incorrect/ordinary question.[/font]
Thanks for the kind words, Sankar. But you are giving me too much credit. The idea of the question was yours. I merely helped you to improve the options and the explanation a bit.
August 23, 2010 at 8:15 pm
I disagree that option 1 is the right choice. It stated that Option 1 was faster and must therefore be the correct choice. However, option 1 lost a constraint that was part of the table in the first place. How could this be the correct choice?
There was nothing in the question the had any performance requirements for getting the fastest update with no concern for the integrity of future data. There was a notion of a large number of records. So, the last answer must be the correct one that either one is acceptable without further qualification.
My 2 Cents.
August 24, 2010 at 12:13 am
taylor_benjamin (8/23/2010)
I disagree that option 1 is the right choice. It stated that Option 1 was faster and must therefore be the correct choice. However, option 1 lost a constraint that was part of the table in the first place. How could this be the correct choice?
Hi Taylor,
I'm afraid I don't understand your concern. You write that option 1 loses a constraint and hence can never be the correct choice. But this is not correct - it is not option 1, but option 2 that loses a constraint.
I do of course agree that performance comes after correctness, so that the "correct" option always has to be picked over the incorrect option regardless of which one is faster. But in this particular case, option 1 is the correct one (and it being faster is an additional bonus).
August 27, 2010 at 10:23 pm
Sadly, receiving the response through comments I cannot see the original question. From my unreliable memory I remember parts of the question as follows: 🙂
The table had a column width that was being increased in size.
The original column definition was VARCHAR(?) NOT NULL.
The Update statements that were there were
1) Alter Table x Alter Column VARCHAR(Bigger Amount) NULL
2) Alter Table x Alter Column VARCHAR(Bigger Amount) NULL
From memory the options were
1) Statement 1 was fastest and therefore the best
2) Statement 2 was fastest and therefore the best.
3)...
4)...
5) Either was a good choice.
The author expected the correct answer to be answer 1.
In my opinion this is not a good choice :w00t:. The result of running the answer that is supposed to be correct is the dropping of the NOT NULL constraint in order to alter the table quickly.
None of the answers were really good...so I picked what I thought was the best answer from the list available...option 5. I chose this answer because the speed of execution was not established as the only/sole requirement of the modification. Option 5 simply stated that either one would work and could be a good choice.
If the table was originally defined as not allowing nulls, how can an alter allowing nulls be a good choice?
I humbly disagree.
Cheers,
Ben
Thanks for taking the time to reply to my response.
August 27, 2010 at 11:14 pm
[font="Courier New"]Taylor,
I agree with you that relying on memory and commenting on the post is NOT only difficult but extremely prone to errors. To go back to the actual question, all you have to do is, click "QotD" on the left side of the page, just below "Blogs" and go back few pages to get to July 14 2010 and the question title is "ALTER".
Please re-read the question without any prior bias/assumptions/thoughts word by word and ask yourself which might be correct. Also test the theory with some sample data involving large volumes of data.
In the requirements, it is explicitly stated that increasing the length of the column is the "ONLY change requested" and another tell is to look for the "right and optimal solution".
>>The result of running the answer that is supposed to be correct is the dropping of the NOT NULL constraint in order to alter the table quickly.
>>If the table was originally defined as not allowing nulls, how can an alter allowing nulls be a good choice?
If you look at the question again, I am sure you will come back and might say that your memory was fuzzy and didn't remember the answer options correctly.
The correct answer is quite the opposite of what you are referring above and I will request you to please read the question and the answer options again.
[/font]
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
August 28, 2010 at 6:57 am
taylor_benjamin (8/27/2010)
Sadly, receiving the response through comments I cannot see the original question. From my unreliable memory I remember parts of the question as follows: 🙂
It's very easy to check the question. Just go to page 1 of this discussion, then clck the link in the first message (the one that reads "Comments posted to this topic are about the item ALTER" - the word ALTER is a hyperlink that will take you directly to the question).
You'll probably find that there is a small, yet significant difference between your memory and the actual question.
September 27, 2010 at 5:11 pm
Learn about Null bit map thanks
October 2, 2010 at 6:33 pm
Thanks Sankar and Hugo for a good question. The performance and NULL handling are interesting and the wording really allowed only 1 realistic answer, knowing that UNIQUE constraint still allows multiple NULL entries. Option 2 might work, albeit slower, but violates existing business rules.
October 2, 2010 at 7:03 pm
Than you for responding to my question.
Respectfully I disagree with your correct solution.
Losing a constraint of not null simply to modify the column length quickly is not a valid option. every. Doing so not only performs the desired result of increasing the column capacity, but it also changes the nullability of a column.
The better option would be to copy the data into a new table using Select ... INTO which would not be transacted. Drop the previous table constraints. Rename the new table, and then re-establish constraints.
If the goal was simply to perform the update quickly, which was not made clear in the problem to be solved, then I can not comprehend how changing the schema is a valid option. I'd revoke rights of any developer who did that on my watch.
Sorry...I'm just bringing real world experience to a theoretical question.;-)
October 3, 2010 at 6:49 am
taylor_benjamin (10/2/2010)
Than you for responding to my question.Respectfully I disagree with your correct solution.
Losing a constraint of not null simply to modify the column length quickly is not a valid option. every. Doing so not only performs the desired result of increasing the column capacity, but it also changes the nullability of a column.
The better option would be to copy the data into a new table using Select ... INTO which would not be transacted. Drop the previous table constraints. Rename the new table, and then re-establish constraints.
If the goal was simply to perform the update quickly, which was not made clear in the problem to be solved, then I can not comprehend how changing the schema is a valid option. I'd revoke rights of any developer who did that on my watch.
Sorry...I'm just bringing real world experience to a theoretical question.;-)
I agree with your arguments, but I don't understand why this leads you to disagree with the correct answer.
There are two options given. Option 1 does not change the nullability, option 2 does. That alone suffices to conclude that only option 1 can be correct. Option 1 is also faster, which is a nice bonus but nothing more.
The correct answer is: "Option 1 runs faster than Option 2 and is the right choice". Perhaps one might interpret this to mean that it is the right choice because it runs faster. That is not the case. It is the right choice because it preserves the constraints on the table.
June 21, 2011 at 6:42 am
Just having a look back over older questions (only just noticed the question of the day on newsletters!).
I got this completely wrong but still think I was right at some point. I went with "Option 2 will fail" because I imagined that the loginname column would never be NULL. Therefore trying to switch it from NOT NULL to allowing nulls would cause an error based on the existing 50 million records surely?!? But then I remembered this was on older version of SQL (I have none to test on) and it allows it in 2008? Can anyone confirm that?
June 21, 2011 at 9:16 am
hahahah I love my mind sometimes. I just realised I was thinking backwards (early start). My example WOULD fail if you were allowing NULLS, had a null entered, and then set to NOT NULL. But in this example its the other way round. FOOL!
June 21, 2011 at 10:09 am
Shark Energy,
Looks like you are having some fun here 🙂
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply