Changing Datatype length

[Kelly Prendergast]

Hi all,
I need to change a varchar from 35 to 50. In the SQL Server books on
line it says that SQL Server actually creates a new table when you
change the length. I ran a test in a test database and it appears the
only thing that changes is the length. All the data remains in tact.

The table with the column I want to modify is very critical. Is there
any chance I would loose data if I change the length to a larger size? I
am making a back up of the table just in case. Thanks,
Kelly

[Erland Sommarskog]

Kelly Prendergast (ke***************@noaa.gov) writes:

I need to change a varchar from 35 to 50. In the SQL Server books on
line it says that SQL Server actually creates a new table when you
change the length. I ran a test in a test database and it appears the
only thing that changes is the length. All the data remains in tact.

The table with the column I want to modify is very critical. Is there
any chance I would loose data if I change the length to a larger size? I
am making a back up of the table just in case. Thanks,

If you use "ALTER TABLE tbl ALTER COLUMN col varchar(50)"
all that will happens is that metadata will be updated, which will occur
in a snap. If you were to change a char(35) column to char(50), I
would expect it to be different, because in this case SQL Server would
move around data to leave room for the value.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Greg D. Moore \(Strider\)]

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...

Kelly Prendergast (ke***************@noaa.gov) writes:

I need to change a varchar from 35 to 50. In the SQL Server books on
line it says that SQL Server actually creates a new table when you
change the length. I ran a test in a test database and it appears the
only thing that changes is the length. All the data remains in tact.

The table with the column I want to modify is very critical. Is there
any chance I would loose data if I change the length to a larger size? I
am making a back up of the table just in case. Thanks,
If you use "ALTER TABLE tbl ALTER COLUMN col varchar(50)"
all that will happens is that metadata will be updated, which will occur
in a snap. If you were to change a char(35) column to char(50), I
would expect it to be different, because in this case SQL Server would
move around data to leave room for the value.

I want to add to Erland's answer to address the final question. You will
NOT lose data.

SQL Server treats this as a transactional change so either the change will
complete in full, or nothing will change.

If it DOES create a new table the pseudo-SQL is:

Begin Tran
select into TEMP from FOO
drop table FOO
sp_renameobject TEMP to FOO
if error ROLLBACK Tran
else End tran

So the change is completely atomic. Nothing to worry about.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp