Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ALTER


ALTER

Author
Message
taylor_benjamin
taylor_benjamin
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 51
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.;-)
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8289 Visits: 11537
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Shark Energy
Shark Energy
Mr or Mrs. 500
Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)

Group: General Forum Members
Points: 573 Visits: 1018
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?
Shark Energy
Shark Energy
Mr or Mrs. 500
Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)

Group: General Forum Members
Points: 573 Visits: 1018
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!
Sankar Reddy
Sankar Reddy
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1250
Shark Energy,

Looks like you are having some fun here :-)

Sankar Reddy | http://SankarReddy.com/
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