• Christian Buettner-167247 (1/11/2011)


    Second, two answers of the QOTD are incorrect.

    a) The usage of an index on a computed column, and

    b) the possibility to alter an index on a computed column.

    Lesson learned: Do not trust Microsofts documentation 😉

    It was certainly wrong on your (a), and the MS documentation is right on that point (so the error in the question was my failure).

    On (b) things are more confused - it seems to be possible to do some things to indexes on a computed column when ansi padding is off. Other things are not possible, though; the MS documentation says it all doesn't work, wbut you discovered by experiment that that is incorrect, so there's no telling what does work and what doesn't except by experiment.

    I knew some index changes couldn't be made when ANSI padding was off (a friend had problems and got me to look at what was going wrong - she now always sets all the sql-92 related settings after I gave her a strong lecture) so I believed the documentation when it said no index changes could be made to indexes on computed a computed column when the option was set off. I guess I should have done some experimentation to verify that, as I'm fairly used to documentation being wrong, or overgeneralising, or saying something can't be done simply because it's a bit risky (eg some things will fail sometimes and work sometimes because of race conditions or other sources of indeterminacy).

    Tom