How to change the data type of a column in an SQL table from integer to decimal

Question

I assigned the data type of one of the columns in the table that I created as an int . My problem is that it does not show decimal places when I run a query against it.

How to change the data type of this column so that it accepts decimal places?

The table is dbo.Budget , and the corresponding column is called ROE .

Answer 1

Easy - just run this SQL statement

 ALTER TABLE dbo.Budget ALTER COLUMN ROE DECIMAL(20,2) -- or whatever precision and scale you need..... 

See the freely available MSDN documentation for what exactly, scale and length in decimal numbers, and what ranges of values ​​are possible.

Answer 2

Just to make it clear:

If there is no data in the table or not so much, then a simple ALTER TABLE statement (as described in other answers here) is fine.

But, if there is a lot of data (millions of rows, or possibly less depending on the size of the table) and / or a lot of disagreements on the table, and not many opportunities for a full downtime / maintenance window, then this requires a different approach, for example, that I described in this answer: Narrowing data types on a very large table .

Answer 3

You can execute this simple sql statement

 Alter table yourtable Alter Column yourtable_column Decimal(10,2) 

You can set decimal precision and scale everything you need.

Answer 4

Change the data type of this column. But in general, sql does not allow pipe transfers. This will cause you to miss this column. There is a setting to achieve this.
Go to the tool designers - table and database designers and uncheck the box next to "Prevent saving." I accept abt sql server 2008R2