I'm having an issue here at work.
So the problem is we have a set fo 4 tables that are very big.
Lets take the biggest 125gb.
I have to alter table column from a smalldatetime to a datetime.
My first question is:
Since this is an implicit conversion - no data should be copied, right ?
-- after checking query plan, 1º copy of data then 2º update catalog.
I don't have nearly enough space left to preform the previous, so my option is:
1. drop indexes and triggers
2. bcp out
3. alter table
4. bcp in
5. create triggers and Indexes.
The bcpout % of space is more less how much of original table size?
If you issue just the alter table statememnt, you would only need to drop any indexes that reference that column (or all of them, if the clustered index refers to it). If the table has not had its clustered index rebuilt in quite a while, you may have enough fragmentation built up in the table that you can get away with the straight alter table statement.
I had to do a similar thing a long long time ago, but we did not have any table double up in size as if the system was copying all of the rows to a new table. Some client tools will do that, but the alter table statement does not.