Altering a Column

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    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

  • Keld Laursen (SEGES)

    SSC Eights!

    Points: 843

    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.

  • anders-731262

    Ten Centuries

    Points: 1256

    Interesting one.

    Thanks.

  • Skanker

    Hall of Fame

    Points: 3059

    Thanks for this. I should have read the option more closely before answering!!! πŸ˜›

  • This was removed by the editor as SPAM

  • Toreador

    SSChampion

    Points: 11261

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

  • paul.knibbs

    SSCoach

    Points: 15270

    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:

  • Michael Lysons

    SSCertifiable

    Points: 6478

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

  • DugyC

    Hall of Fame

    Points: 3804

    Bugger πŸ™

    Great question though, thanks.

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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    (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/

  • marlon.seton

    SSCrazy

    Points: 2623

    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.

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    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

  • Toreador

    SSChampion

    Points: 11261

    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 42 total)

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