1

Could we change attribute's data type when the database table has record in SQL?

I am using Microsoft Management Studio 2008. The error that i am getting is: ** Error converting data type nvarchar to float. **

Yusubov
  • 5,815
  • 9
  • 32
  • 69
Yilmaz Paçariz
  • 186
  • 1
  • 4
  • 18

4 Answers4

3

In short: It is possible with alter column command ONLY if the altered data type is compatible with newly modified one. In addition, it is recommended to be done with transaction.

For example: You may change a column from a varchar(50) to a nvarchar(200), with a script below.

alter table TableName 
alter column ColumnName nvarchar(200)

Edit: Regarding your posted error while altering column type.

** Error converting data type nvarchar to float. **

One way would be to create a new column, and convert all good (convertible and compatible) records to new column. After that you may wanna to clean-up the bad records that do not convert, delete old column and re-name your newly added and populated column back to the original name. Important: use testing environment for all this manipulations first. Usually, playing with productions tables turns to be a bad practice to screw things up.

References to look for more discussions on similar SE posts:

Community
  • 1
  • 1
Yusubov
  • 5,815
  • 9
  • 32
  • 69
  • it is not possible by this way. The error message is, **Error converting data type nvarchar to float.** – Yilmaz Paçariz Jan 05 '13 at 18:48
  • Read my answer carefully again, i said that if data types are compatible. Of course it will not work if you try alter type double to become int. – Yusubov Jan 05 '13 at 18:49
  • Added a suggestion what to do when having conversion error. Hope that helps. – Yusubov Jan 05 '13 at 19:30
1

Obviously, there is no default conversion to your new datatype. One solution could be to create a second column with the requested type, and write your own conversion function. Once this done, delete the first column and rename the second one with the same name.

Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72
0

Things to consider: How big your table is. You then use the alter table syntax. We do not know what data type you want to change, so just for e.g.

alter column:

Alter Table [yourTable] Alter column [yourColumn] varchar(15)
  • You could also try to add a new column and then update that column using your old column. Drop the old column. Rename the new column. This is a safe better way, becasue at times the data that you hold might not react well to the new data type...

A post to look into for ideas: Change column types in a huge table, How to change column datatype in SQL database without losing data

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
0

Alter datatype of that column ..But In general sql wont allow to channge.It will prompt u drop that column..There is setting to achive that thing.
Go to Tool->Option->designers->Table and Database designers and Uncheck Prevent saving option.I m taking abt sql server 2008R2.Now u can easily alter data type.enter image description here

saktiprasad swain
  • 340
  • 2
  • 8