How to change column datatype in SQL Server database without losing data?

Question

I have SQL Server database and I just realized that I can change the type of one of the columns from int to bool.

How can I do that without losing the data that is already entered into that table?

Have you tried creating a new bit column, copying the values from the old column into the new one, deleting the old one and renaming the new one? All this in a transaction, of course, to rollback on problems. — Radu Caprescu
– Radu Caprescu, Commented Feb 27, 2011 at 21:19
You say "I just realized that I can change the type from one of the columns from int to bool" There is no boolean datatype. There is bit though. Are you asking how you can do this (as the 2 answers so far have covered). Or is your question "I just realised this is possible - How does SQL Server do this?" — Martin Smith
– Martin Smith, Commented Feb 27, 2011 at 21:28
possible duplicate of How do you change the datatype of a column in MS SQL? — nandeesh
– nandeesh, Commented Dec 20, 2012 at 13:21

marc_s · Accepted Answer · 2011-02-27 21:28:17Z

363

You can easily do this using the following command. Any value of 0 will be turned into a 0 (BIT = false), anything else will be turned into 1 (BIT = true).

ALTER TABLE dbo.YourTable
   ALTER COLUMN YourColumnName BIT

The other option would be to create a new column of type BIT, fill it from the old column, and once you're done, drop the old column and rename the new one to the old name. That way, if something during the conversion goes wrong, you can always go back since you still have all the data..

edited Feb 27, 2011 at 21:28

answered Feb 27, 2011 at 21:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Álvaro González Over a year ago

In other words: NULL remains NULL, 0 becomes False, non-zero values (1, -1, 1999, -987...) become True.

HLGEM Over a year ago

And never make a change like this from the GUI. Always make it through a script like this. The GUI will drop and recreate the table and that is much more time consuming. If the table is large and on production, this can be disastrous. Plus all table changes should have a script that is in source control like all other code.

HLGEM Over a year ago

I would add, is make sure you have a current backup of the database before making any structural change to a table with data. And do not run a change like this on production during peak usage hours if the table is frequently used or large.

marc_s · Accepted Answer · 2018-04-15 19:35:26Z

28

ALTER TABLE tablename
ALTER COLUMN columnname columndatatype(size)

Note: if there is a size of columns, just write the size also.

edited Apr 15, 2018 at 19:35

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 8, 2015 at 3:09

Xyed Xain Haider

4985 silver badges16 bronze badges

Comments

Ajay2707 · Accepted Answer · 2018-04-10 10:50:57Z

24

If it is a valid change.

you can change the property.

Tools --> Options --> Designers --> Table and Database designers --> Uncheck --> Prevent saving changes that required table re-creation.

Now you can easily change the column name without recreating the table or losing u r records.

edited Apr 10, 2018 at 10:50

Ajay2707

5,8286 gold badges44 silver badges61 bronze badges

answered Jul 25, 2016 at 6:50

Sathish

2,10918 silver badges13 bronze badges

3 Comments

HLGEM Over a year ago

Under no circumstances should you be making table changes using the GUI. It will completely recreate the table rather than use Alter table and this will cause an issue if you you uncheck this and the table is large. Further you should have all changes to tables in a script in source control.

JMarsch Over a year ago

Look more closely at that option -- it's turning off a safety that will prevent GUI from dropping the table. You won't appear to lose data, because the GUI will recreate the table, but on the server it will be copy/drop. So, if there is a lot of data in the table, it will cause a very large operation. Also, I think (not positive) that it occurs in a single transaction, therefore you could fill your transaction log.

JMarsch Over a year ago

I guess I'd like to add a little clarification to my previous comment. If you are just doing this on your development machine, you are probably fine. But I would not advise using this method on a production database -- especially if it is a mission critical one.

NullException · Accepted Answer · 2017-09-21 13:32:37Z

11

if you use T-SQL(MSSQL); you should try this script:

ALTER TABLE [Employee] ALTER COLUMN [Salary] NUMERIC(22,5)

if you use MySQL; you should try this script:

ALTER TABLE [Employee] MODIFY COLUMN [Salary] NUMERIC(22,5)

if you use Oracle; you should try this script:

ALTER TABLE [Employee] MODIFY [Salary] NUMERIC(22,5)

answered Sep 21, 2017 at 13:32

NullException

1791 silver badge5 bronze badges

Comments

Philippe Leybaert · Accepted Answer · 2011-02-27 21:19:59Z

8

Why do you think you will lose data? Simply go into Management Studio and change the data type. If the existing value can be converted to bool (bit), it will do that. In other words, if "1" maps to true and "0" maps to false in your original field, you'll be fine.

answered Feb 27, 2011 at 21:19

Philippe Leybaert

172k33 gold badges213 silver badges224 bronze badges

6 Comments

Tony O'Hagan Over a year ago

If you have data in the table this won't work. When you attempt to change a column type, SMS claims that it needs to drop the table first ... which of course is incorrect since the ALTER TABLE ... ALTER COLUMN command works just fine even for non NULL fields. This is why they thought they could loose data.

Philippe Leybaert Over a year ago

@TonyO'Hagan That's not true. You can turn off the warning and it will work just fine with existing data. See also stackoverflow.com/questions/2947865/…

Tony O'Hagan Over a year ago

Ok cool! Didn't know that. Just tried to up vote you (back) but SO is preventing me unless you edit your answer. Perhaps a minor change and I can do so ;) .

HLGEM Over a year ago

Under no circumstances should you be making table changes using the GUI. It will completely recreate the table rather than use Alter table and this will cause an issue if you you uncheck this and the table is large. Further you should have all changes to tables in a script in source control.

Jack Over a year ago

@HLGEM That's what database projects are for.

|

saktiprasad swain · Accepted Answer · 2017-05-31 06:05:34Z

4

Go to Tool-Option-designers-Table and Database designers and Uncheck Prevent saving option

answered May 31, 2017 at 6:05

saktiprasad swain

3502 silver badges8 bronze badges

1 Comment

HLGEM Over a year ago

Under no circumstances should you be making table changes using the GUI. It will completely recreate the table rather than use Alter table and this will cause an issue if you you uncheck this and the table is large. Further you should have all changes to tables in a script in source control.

MNF · Accepted Answer · 2019-01-10 12:55:45Z

4

for me , in sql server 2016, I do it like this

*To rename column Column1 to column2

EXEC sp_rename 'dbo.T_Table1.Column1', 'Column2', 'COLUMN'

*To modify column Type from string to int:( Please be sure that data are in the correct format)

ALTER TABLE dbo.T_Table1 ALTER COLUMN Column2  int;

answered Jan 10, 2019 at 12:55

MNF

72710 silver badges14 bronze badges

Comments

Ebrahim Sabeti · Accepted Answer · 2019-02-26 09:13:09Z

4

Alter column data type with check type of column :

IF EXISTS(
       SELECT 1
       FROM   sys.columns
       WHERE  NAME = 'YourColumnName'
              AND [object_id] = OBJECT_ID('dbo.YourTable')
              AND TYPE_NAME(system_type_id) = 'int'
   )
    ALTER TABLE dbo.YourTable ALTER COLUMN YourColumnName BIT

answered Feb 26, 2019 at 9:13

Ebrahim Sabeti

514 bronze badges

Comments

Hardik · Accepted Answer · 2016-07-07 07:34:13Z

0

In compact edition will take size automatically for datetime data type i.e. (8) so no need to set size of field and generate error for this operation...

answered Jul 7, 2016 at 7:34

Hardik

338 bronze badges

Comments

Ismayil S · Accepted Answer · 2017-06-20 05:25:07Z

-3

I can modify the table field's datatype, with these following query: and also in the Oracle DB,

ALTER TABLE table_name
MODIFY column_name datatype;

answered Jun 20, 2017 at 5:25

Ismayil S

2431 gold badge4 silver badges21 bronze badges

2 Comments

HLGEM Over a year ago

Not in SQl Server

Nick Over a year ago

This should be ALTER COLUMN instead of MODIFY

Max · Accepted Answer · 2017-03-21 09:05:03Z

-5

Replace datatype without losing data

alter table tablename modify columnn  newdatatype(size);

edited Mar 21, 2017 at 9:05

Max

1,0883 gold badges13 silver badges19 bronze badges

answered Mar 21, 2017 at 5:00

user7743491

11

Collectives™ on Stack Overflow

How to change column datatype in SQL Server database without losing data?

11 Answers 11

3 Comments

Comments

3 Comments

Comments

6 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

3 Comments

Comments

3 Comments

Comments

6 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related