I'm doing a project that's adding some columns to a fairly badly designed table, and I noticed that it's got a lot of wasted space.
I see a lot of posts about adding columns on the end being relatively quick and cheap (for reasons I don't quite get – the follow-on links were broken), and I see a lot of posts about how expensive it is to grow columns, and how it essentially boils down to "make a new table and copy all the old data into it" (in fact ssms does that with Generate Script no matter what change you make).
I'm curious about a more niche concern I guess – altering columns to smaller data sizes and how to do that efficiently.
Specifically, this table has multiple datetime columns that really only want the date. In fact the sql is doing all the date arithmetic to strip time off of GETDATE(). I want to
ALTER TABLE Foo ALTER COLUMN BAR DATE NULL
But I don't want to incur all the expense of creating a temp table and re-writing the old data (if I can avoid it).
Seems like all the old data in place would be fine, just smaller.
And I'm hoping that freeing up that space in the block would make the new requirements I have to add less onerous (but I obviously don't get the deep internals of row allocation).
So what about going to smaller fixed-size types with an ALTER statement? Will that just be okay and not blow up the log?