This topic itself is nice and the techniques mentioned in this article can be used in many occasions.

However "change a dimension ID to see what happens" section does not cover...

Here is an easy and quick alternative for all versions of SqlServer(unless you are not looking for any solution beyond CTE).

OUTPUT:

--------

Apple, Banana, Orange, Melon, Grape

CODE:

-----

ALTER TABLE fruit

ADD gr int

GO

UPDATE fruit

SET...