CODEWITHSUNDEEP
sqlsql-serverdatabasessmssqldatatypes
Irfan Ansari
Irfan Ansari

Reputation: 67

How to change column data type of table in Microsoft SQL Server Management

My Table

How to change the Data Type of column "Price" from int to float

I tried to change the data type directly from table to float but it didn't work. I even deleted the column and added another column with same name but it's not working. Got the following error:

enter image description here

USE [GarageDB]
GO

UPDATE [dbo].[Product]
SET [TypeId] = <TypeId, int,>
,[Name] = <Name, varchar(100),>
,[Price] = <Price, int,>
,[Description] = <Description, text,>
,[Image] = <Image, varchar(150),>
WHERE <Search Conditions,,>
GO

What command should I use to change the data type of price from int to float?

I want it into decimal number. Ex: 40.99

Upvotes: 0

Views: 5655

Answers (2)

Brian MacKay
Brian MacKay

Reputation: 32029

In development, we just enable this... Of course, in production you would not typically want to do this, since it can result in data loss or loss of metadata and other errors, especially if the system is under load.

Here are the steps:

  • Tools menu > Options.
  • In the navigation pane, click Designers.
  • Uncheck the prevent saving changes that require the table re-creation checkbox.
  • Click OK.

You could also look into the ALTER TABLE command if you want a more direct approach, or if you need to do this in production, but make sure you know what you're doing as this command is potentially destructive.

If you want to use the GUI, this is the way.

Upvotes: 3

Thom A
Thom A

Reputation: 95557

Use SQL, not the GUI. For example:

ALTER TABLE dbo.YourTable ALTER COLUMN YourColumn decimal(10,2) NULL;

Note that if you have constraints that reference the column, you will need to DROP these, and then recreate them.

Upvotes: 1

Related Questions