如何改变 Microsoft SQL Server 中小数列的精度？

小开

最佳答案

ALTER TABLE Testing ALTER COLUMN TestDec decimal(16,1)

Just put decimal(precision, scale), replacing the precision and scale with your desired values.

I haven't done any testing with this with data in the table, but if you alter the precision, you would be subject to losing data if the new precision is lower.

小开

There may be a better way, but you can always copy the column into a new column, drop it and rename the new column back to the name of the first column.

to wit:

ALTER TABLE MyTable ADD NewColumnName DECIMAL(16, 2);
GO


UPDATE  MyTable
SET     NewColumnName = OldColumnName;
GO


ALTER TABLE CONTRACTS DROP COLUMN OldColumnName;
GO




EXEC sp_rename
@objname = 'MyTable.NewColumnName',
@newname = 'OldColumnName',
@objtype = 'COLUMN'
GO

This was tested on SQL Server 2008 R2, but should work on SQL Server 2000+.

小开

ALTER TABLE (Your_Table_Name) MODIFY (Your_Column_Name) DATA_TYPE();

For you problem:

ALTER TABLE (Your_Table_Name) MODIFY (Your_Column_Name) DECIMAL(Precision, Scale);

小开

In Oracle 10G and later following statement will work.

ALTER TABLE <TABLE_NAME> MODIFY <COLUMN_NAME> <DATA_TYPE>

If the current data type is NUMBER(5,2) and you want to change it to NUMBER(10,2), following is the statement

ALTER TABLE <TABLE_NAME> MODIFY <COLUMN_NAME> NUMBER(10,2)