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.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis