how Alter Table datatype with null values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how Alter Table datatype with null values

Author

Topic

teamjai
Yak Posting Veteran

52 Posts

Posted - 2015-05-07 : 05:06:46

Hi,

I am trying to change a column from a decimal(18,2) to a decimal(18,3). What is the SQL command to alter this table?

SQL Command:

alter table TableName
alter column ColumnName decimal(18,3) [null]

the above command is right ?

Kristen
Test

22859 Posts

Posted - 2015-05-07 : 05:33:17

I do this in SSMS (using the GUI) and then instead of "Save" I use the toolbar button that generates a Script.

Some types of data type alteration require that a new table is created, data copied over, constraints / foreign keys / indexes all recreated and the original table dropped ... other data type conversions, and addition of new columns, can be done with a simple ALTER of that column

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-05-07 : 05:35:13

Yes. But you do not need a square brace around NULL

Madhivanan

Failing to plan is Planning to fail

teamjai
Yak Posting Veteran

52 Posts

Posted - 2015-05-07 : 08:04:29

Hi,

i got an below error message

SQL: alter table Hour
alter column Absolute decimal(18,3) null

Msg 5074, Level 16, State 1, Line 1
The index 'Idx_Deleted_Absolutes' is dependent on column 'Absolute'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Absolute failed because one or more objects access this column.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-07 : 09:09:22

You'll have to disable the index first, then change the column, then rebuild the index

Gerald Britton, MCSA
Toronto PASS Chapter

Kristen
Test

22859 Posts

Posted - 2015-05-07 : 14:30:57

That's why I do it in SSMS and let that build the appropriate Script for me