Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

ALTER Expand / Collapse
Author
Message
Posted Saturday, October 2, 2010 7:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 6, 2014 8:04 AM
Points: 24, Visits: 48
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.
Post #997259
Posted Sunday, October 3, 2010 6:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #997289
Posted Tuesday, June 21, 2011 6:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 27, 2014 1:17 AM
Points: 549, Visits: 1,017
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?
Post #1128951
Posted Tuesday, June 21, 2011 9:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 27, 2014 1:17 AM
Points: 549, Visits: 1,017
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!
Post #1129079
Posted Tuesday, June 21, 2011 10:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:32 PM
Points: 382, Visits: 1,188
Shark Energy,

Looks like you are having some fun here


Sankar Reddy | http://SankarReddy.com/
Post #1129109
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse