Column datatype change

We have an 18 GB table in a 39 GB database. In this table,
4 of the columns declared as char(255) and we want to
change these 4 columns to a varchar(255) to save some
space (It saves about ~3 GB).
We also have only 8 GB left on the hard drive. When I
tryed to run it, it ran out of disk space. There are 3
other columns in the table and primary key and an index on
these columns (Not the ones we are trying to change).
How can I change the datatypes of these columns without
running out of space ?. We will add more space but not
immediatelly.
I did put the database on 'Simple' mode before I ran the
query but no help.
Thanks for any suggestions.I do not think there is much you can do unless you get more disk
space...Perhaps you could copy it to another server?
Drop all indexes and do the work, then re-create the indexes... That should
give you some space... ( what a pain tho)
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:8c6f01c3e9bb$b04df100$a501280a@.phx.gbl...> We have an 18 GB table in a
39 GB database. In this table,

quote:

---

> 4 of the columns declared as char(255) and we want to
> change these 4 columns to a varchar(255) to save some
> space (It saves about ~3 GB).
> We also have only 8 GB left on the hard drive. When I
> tryed to run it, it ran out of disk space. There are 3
> other columns in the table and primary key and an index on
> these columns (Not the ones we are trying to change).
> How can I change the datatypes of these columns without
> running out of space ?. We will add more space but not
> immediatelly.
> I did put the database on 'Simple' mode before I ran the
> query but no help.
> Thanks for any suggestions.

---

Column datatype change

We have an 18 GB table in a 39 GB database. In this table,
4 of the columns declared as char(255) and we want to
change these 4 columns to a varchar(255) to save some
space (It saves about ~3 GB).
We also have only 8 GB left on the hard drive. When I
tryed to run it, it ran out of disk space. There are 3
other columns in the table and primary key and an index on
these columns (Not the ones we are trying to change).
How can I change the datatypes of these columns without
running out of space ?. We will add more space but not
immediatelly.
I did put the database on 'Simple' mode before I ran the
query but no help.
Thanks for any suggestions.I do not think there is much you can do unless you get more disk
space...Perhaps you could copy it to another server?
Drop all indexes and do the work, then re-create the indexes... That should
give you some space... ( what a pain tho)
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:8c6f01c3e9bb$b04df100$a501280a@.phx.gbl...> We have an 18 GB table in a
39 GB database. In this table,
> 4 of the columns declared as char(255) and we want to
> change these 4 columns to a varchar(255) to save some
> space (It saves about ~3 GB).
> We also have only 8 GB left on the hard drive. When I
> tryed to run it, it ran out of disk space. There are 3
> other columns in the table and primary key and an index on
> these columns (Not the ones we are trying to change).
> How can I change the datatypes of these columns without
> running out of space ?. We will add more space but not
> immediatelly.
> I did put the database on 'Simple' mode before I ran the
> query but no help.
> Thanks for any suggestions.

Column Datatype

Hi folks, need ur help.

I want to convert strings from a textfile and convert em to appropriate datatype for the destination columns, eg if i have a source value in string 11111 and the destination column is MONEY. I want to use CONVERT(functionretruningdatatype,'11111') in my insert statement.

Any guidance!

Howdy!SQL Server does implicit data type conversion. Refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

If you need any conversion outside of the implicit conversions, you need to use dynamic SQL to construct the INSERT statement with the appropriate datatype

Column Change on Large Table (Revised)

Hello,
I have to change a datatype (int to bigint) for a column in a table with
over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!
Here are two ideas. Both should be preceded by a full backup IMHO.
Change your recovery mode to simple, add a new nullable BIGINT column, then
run an UPDATE in a loop, truncating the log each iteration.
SET ROWCOUNT 10000;
SELECT 'starting...';
WHILE @.@.ROWCOUNT > 1
BEGIN
UPDATE table SET BigIntColumn = IntColumn WHERE BigIntColumn IS NULL;
END
SELECT '...finished';
Then you can drop the old column (you will need to drop
constraints/indexes/schemabound views/functions etc. first) and rename the
new one.
ALTER TABLE table DROP COLUMN IntColumn;
EXEC sp_rename 'table.BigIntColumn', 'IntColumn', 'COLUMN';
To be safe if you have any views that point I would DROP/CREATE or run
sp_refreshview. You didn't say what version of SQL Server you were using...
there may be other factors / consequences...
If you can take the table offline for an extended amount of time, you could
build an almost identical table (the int column changed to bigint) on
another system (which does have the room to duplicate the table), then copy
the data over to the new table, drop the existing table, create the same
table (with int changed to bigint) and copy the data back (there are wizards
and/or DTS/SSIS for this task).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:16AE2454-32A0-46B6-A0DB-01580391562D@.microsoft.com...
> Hello,
> I have to change a datatype (int to bigint) for a column in a table with
> over 10million rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>

Column Change on Large Table (Revised)

Hello,
I have to change a datatype (int to bigint) for a column in a table with
over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!Here are two ideas. Both should be preceded by a full backup IMHO.
Change your recovery mode to simple, add a new nullable BIGINT column, then
run an UPDATE in a loop, truncating the log each iteration.
SET ROWCOUNT 10000;
SELECT 'starting...';
WHILE @.@.ROWCOUNT > 1
BEGIN
UPDATE table SET BigIntColumn = IntColumn WHERE BigIntColumn IS NULL;
END
SELECT '...finished';
Then you can drop the old column (you will need to drop
constraints/indexes/schemabound views/functions etc. first) and rename the
new one.
ALTER TABLE table DROP COLUMN IntColumn;
EXEC sp_rename 'table.BigIntColumn', 'IntColumn', 'COLUMN';
To be safe if you have any views that point I would DROP/CREATE or run
sp_refreshview. You didn't say what version of SQL Server you were using...
there may be other factors / consequences...
If you can take the table offline for an extended amount of time, you could
build an almost identical table (the int column changed to bigint) on
another system (which does have the room to duplicate the table), then copy
the data over to the new table, drop the existing table, create the same
table (with int changed to bigint) and copy the data back (there are wizards
and/or DTS/SSIS for this task).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:16AE2454-32A0-46B6-A0DB-01580391562D@.microsoft.com...
> Hello,
> I have to change a datatype (int to bigint) for a column in a table with
> over 10million rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>

Column Change on Large Table

Hello,
I have to change a datatype for a column in a table with over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!
From what data type? To which data type?
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B760FA66-130F-43F6-B840-C9233AD09F6E@.microsoft.com...
> Hello,
> I have to change a datatype for a column in a table with over 10million
> rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>
|||From int to bigint.
"Aaron Bertrand [SQL Server MVP]" wrote:

> From what data type? To which data type?
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:B760FA66-130F-43F6-B840-C9233AD09F6E@.microsoft.com...
>
>

Column Change on Large Table

Hello,
I have to change a datatype for a column in a table with over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!From what data type? To which data type?
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B760FA66-130F-43F6-B840-C9233AD09F6E@.microsoft.com...
> Hello,
> I have to change a datatype for a column in a table with over 10million
> rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>|||From int to bigint.
"Aaron Bertrand [SQL Server MVP]" wrote:
> From what data type? To which data type?
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:B760FA66-130F-43F6-B840-C9233AD09F6E@.microsoft.com...
> > Hello,
> >
> > I have to change a datatype for a column in a table with over 10million
> > rows
> > and don't have a lot of log space to deal with.
> >
> > What's the best method for achieving this task with the least amount of
> > logging?
> >
> > Any help appreciated.
> > Thanks in advance!
> >
>
>