Post reply

Altering a Column

  • April 24, 2012 at 9:40 pm

    #255421

    Comments posted to this topic are about the item Altering a Column

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • April 25, 2012 at 12:15 am

    #1478700

    Darn. I should have done a little research there.

    I have to admit that I were wrong 🙁

    Thanks for the question. Always good to get these little tidbits.

  • April 25, 2012 at 1:21 am

    #1478717

    Interesting one.

    Thanks.

  • April 25, 2012 at 1:29 am

    #1478728

    Thanks for this. I should have read the option more closely before answering!!! 😛

  • April 25, 2012 at 1:51 am

    This was removed by the editor as SPAM

  • April 25, 2012 at 2:49 am

    #1478766

    Steve's presumably got rid of the code that automatically told you how many options to choose on a multi-select question 🙁

  • April 25, 2012 at 2:56 am

    #1478773

    I didn't see anything in the ALTER COLUMN documentation indicating that a change from NULL to NOT NULL wouldn't work--what am I missing? :unsure:

  • April 25, 2012 at 3:02 am

    #1478778

    Nice question. QotD consistently reminds me how little I actually know!

  • April 25, 2012 at 4:07 am

    #1478822

    Bugger 🙁

    Great question though, thanks.

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • April 25, 2012 at 4:08 am

    #1478824

    Thanks! Great question.

    Unfortunately, I managed to work out the right solution in my head - and then clicked the wrong options. :facepalm: <-- need a smiley for that!

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • April 25, 2012 at 4:25 am

    #1478836

    paul.knibbs (4/25/2012)

    I didn't see anything in the ALTER COLUMN documentation indicating that a change from NULL to NOT NULL wouldn't work--what am I missing? :unsure:

    Good fun question that points up (i) a definite error in BoL for 2008 R2 (or maybe a bug in 2008 R2 if what's in BoL is what it is supposed to do) and (ii) a possible error in BoL for 2012.

    Maybe the BoL error is part of why there are so many incorrect answers (87% wrong to date; 67% agreed with th BoL error).

    The reference in the explanation is to the SQL 2012 version, although the question says it was only tested with the 2008 R2 version; as the 2012 version of the page explicitly states that a column can be changed from NULL to NOT NULL provided there are currently no NULL values in the column

    BoL

    NOT NULL can be specified in ALTER COLUMN only if the column contains no null values

    this is a bit unfortunate. I haven't tested with 2012 (must acquire a platform that will run 2012) and I suspect that this is an error on that BoL page.

    Of course referring to the 2012 page is harmless here because the 2008 R2 version has exactly the same statement. For 2008 R2 this is definitely false, since a column which is indexed can not be changed from NULL to NOT NULL even if the table is empty - the only way to get around this is to drop the index, alter the column, and recreate the index. Both the 2008 R2 and 2012 versions give the same code example of updating the table to set nulls to some non-null default and then using alter column, which just doesn't work (at least in 2008 R2) if the column is indexed.

    edit: bitten by the "quote the wrong comment" bug; edited to fix.

    Tom

  • April 25, 2012 at 4:35 am

    #1478841

    (edit: hit send too soon)

    L' Eomot Inversé (4/25/2012)

    BoL

    NOT NULL can be specified in ALTER COLUMN only if the column contains no null values

    this is a bit unfortunate. I haven't tested with 2012 (must acquire a platform that will run 2012) and I suspect that this is an error on that BoL page.

    No, I don't think so (though I have not tested either). As far as I know, this is accurate - for unindexed columns.

    The actual error in Books Online is here:

    The modified column cannot be any one of the following:

    (...)

    Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, the new size is equal to or larger than the old size, and the index is not the result of a PRIMARY KEY constraint.

    This quote suggests that ANY change of nullability is prohibited if the column is indexed.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • April 25, 2012 at 4:35 am

    #1478840

    Toreador (4/25/2012)

    Steve's presumably got rid of the code that automatically told you how many options to choose on a multi-select question 🙁

    What he said.

  • April 25, 2012 at 5:28 am

    #1478867

    Sorry, guys, for not adding a note on how many answers should be selected.

    Next time I'll pay more attention. 🙂

    BOL had me a little confused on this concept because the column is in an index and has statistics (which also has alter restrictions). But the question was based upon page 283 of Kalen Delaney's SQL Server Internals which states:

    "Be aware of the following restrictions:

    If the modified column participates in an index, the only type changes that are allowed are increasing the length of a variable-length type (for example, varchar(10) to varchar(20)), changing nullability of the column, or both."

    After a test I saw that I could change from NOT NULL to NULL but not the other way around so I made a little research. I found this part of that same BOL documentation that states:

    "The modified column cannot be any one of the following:

    Used in statistics generated by the CREATE STATISTICS statement unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or greater than the old size, or if the column is changed from not null to null. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN."

    I'm not sure the section above has something to with anything but I was always trying to figure out why I could not change a column from NULL to NOT NULL.

    So this question was more for me to learn than anything else.

    Any opinions on this subject is much appreciated.

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • April 25, 2012 at 5:32 am

    #1478869

    codebyo (4/25/2012)

    Sorry, guys, for not adding a note on how many answers should be selected.

    Steve always used to do this (or something in the QotD publishing widget did it automatically).

Viewing 15 posts - 1 through 15 (of 41 total)

You must be logged in to reply to this topic. Login to reply