How do I alter the precision of a decimal column in Sql Server

Question

Is there a way to alter the precision of an existing decimal column in Sql Server

User · Answer

Go to enterprise manager  design table  click on your field   Make a decimal column  In the properties at the bottom there is a precision property

User · Answer

ALTER TABLE  tableName  CHANGE   columnName  DECIMAL 16 1  NOT NULL    I uses This for the alterration

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How do I alter the precision of a decimal column in Sql Server?

Examples related to sql-server