468,101 Members | 1,315 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,101 developers. It's quick & easy.

Changing Datatype length

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
Jul 20 '05 #1
2 32124
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
Jul 20 '05 #2

"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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Sanjay Minni | last post: by
1 post views Thread by Savas Ates | last post: by
1 post views Thread by shsandeep | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.