Error in - Alter table column datatype size

Transact-SQL
1

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.

2

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

3

Thank you,

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

4

Please post the CREATE INDEX statement for 'Idx_Deleted_Absolutes'

5

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

6

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

7

....or To Clipboard

8

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

9

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

10

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.

11

That should do it.