SQLTeam.com | Weblogs | Forums

Error in - Alter table column datatype size

Hi,

I have one column in a table that I need to do an update datatype size. In the column the datatype is decimal(18,2).
What I need the update to do is update the datatype decimal(18,2) to decimal (18,3).
if he column has these values:
0.33
0.22
After update the size, the column values should look like this.
0.330
0.220
0.666

So i tried to alter the table column. but i am getting the below error.

Query:

Alter table Price_Hour
Alter column Value_Each decimal(18,3).

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

Table Design:

Table Name - Price_Hour.

ColumnName
1.GUID (PK, Varchar(36, not null)
2. PriceGUID (PK,FK,Varchar(36, not null)
3. Value_Each (decimal(18,2, null).

Could you please help me, how to alter the column size, i have 50000 records in that table also.
how to fix this error.

Advance Thanks.

Drop the index and alter the column data type.
This error message shows the column is used in that index

Thank you,

Yes , but Index using the column GUID and PriceGUID only, not for Value_Each.
is that the issue.

Please post the CREATE INDEX statement for 'Idx_Deleted_Absolutes'

I am using SQL server Management studio , how to get the script (CREATE INDEX statement) for Index

In Object Explorer, navigate to the table node, and then the Indexes node. Right click on the index listed and select Script Index as -> Create To -> New Window

....or To Clipboard

Thank you

Index Script

USE [Main]
GO

CREATE NONCLUSTERED INDEX [Idx_Deleted_Absolutes] ON [dbo].[Price_Hour]
(

[PriceGUID] ASC

)
INCLUDE ( [GUID],
[Value_Each]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

YOu can see that Value_Each is an included column. That's what SQL is complaining about. You'll need to drop that index to change the size of that column

Yes, Thank you.

So i need to follow the below steps.

  1. Drop Index. - DROP INDEX Price_Hour.Idx_Deleted_Absolutes.
  2. alter table [dbo].[Price_Hour]
    alter column Value_Each decimal(18,3)
  3. Again Create a Index with above CREATE INDEX script.

anything wrong in the steps please correct me.
At that time any record insert to table , is that any issue.

That should do it.