3

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?

Thanks

|improve this question
  • What table size and record count are we talking about. Sometimes (often) the simplest solution - recreating the table - is the best option. – eckes Jul 20 at 2:24
  • Each row is about 300 bytes, in production the tables themselves probably between 50 and 100 million rows, depending on the customer. Even at that level table copying takes a while. I was hoping there might be some shortcuts. – user1664043 Jul 20 at 14:10
  • If you have to re-write the row, there are no shortcuts, sorry. You have to pay for the write and for the logging of the write. With newer versions of SQL Server, more of these operations can be metadata-only, or at least online, but not all of them can be. – Aaron Bertrand♦ Jul 20 at 19:07
6

Changing from DATETIME to DATE changes the binary representation of the value, so the overhead will be similar to the one when increasing in size.

IIRC the only change that can be metadata only is one that doesn't change the binary representation, like changing from VARCHAR(100) to VARCHAR(50), and some may not even require scanning the data to make sure it fits, such as changing from VARCHAR(50) to VARCHAR(100).

Any change in data type that is not metadata only, will require physical update of the rows, and will cause a similar overhead as the entire row needs to be logged.

|improve this answer
  • I've seen some articles saying that adding columns is "cheap" and "metadata only" which puzzles me a bit. I mean, adding columns is changing the binary layout of the row; how can that be metadata only? One post had a link to an article "explaining it" but the link was broken. – user1664043 Jul 20 at 15:49
  • @user1664043 when you add a new column to an existing table, all rows will have the same value, typically NULL. Therefor, the engine doesn't need to do anything to the rows themselves - when you SELECT that column in a query, the engine looks at the metadata and returns NULL... Once you start updating the values, you pay the price as each row will need to be modified, logged etc. You will still pay the price, just in much smaller installments. Hope that makes sense... – SQLRaptor Jul 20 at 16:39
  • @user1664043 rusanu.com/2011/07/13/… – Aaron Bertrand♦ Jul 20 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.