How do I modify a column type if the DB has many values

Question

I'm currently trying to convert a column in SQL Server from nvarchar(255) to decimal(18,0). However, this is the error I get:

'member_details' table - Unable to modify table.
Error converting data type nvarchar to numeric.

Before this error, Management Studio gave a warning about changing the datatype of columns with many rows. My DB contains about 1,000,000 rows.

Can anybody help? Any help will be greatly appreciated.

Answer 1

The problem isn't the number of rows, the problem is that you have a row which does not fit into the decimal(18, 0) data type. If you're on SQL 2012 you can identify the rows fairly easily (and can do your own number checking function on other versions).

Create Table dbo.Moo (
 Data Nvarchar(255) 
)
Insert dbo.Moo Values ('-1'), ('100'), ('1.1'), ('Cows')
Select Convert(Decimal(18, 0), Data)
From dbo.Moo
-- Msg 8114, Level 16, State 5, Line 7
-- Error converting data type nvarchar to numeric.
Select *
From dbo.Moo
Where Data Is Not Null
And Try_Convert(Decimal(18, 0), Data) Is Null
-- Cows

Once they're identified you can fix the data on those rows and then convert the table over as you originally attempted.