HI,
I am using SQL Server Management Studio tool.
I have one column in a table that I need to do an update datatype .
What I need the update to do is update the datatype decimal(18,2) to int (5).
Create table script:
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MathFuntion](
[Length] [decimal](18, 5) NULL,
[Breadth] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
Script for table Index ;
USE [Test]
GO
CREATE CLUSTERED INDEX [DeletedFuntionTest] ON [dbo].[MathFuntion]
(
[Length] ASC
)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
i tried to update the column datatype using alter table , but it return to Error message, reason is the Index using the column "Length"
Alter table [camos_keiasen].[dbo].[MathFuntion]
Alter column [length] int
Error :
Msg 5074, Level 16, State 1, Line 1
The index 'DeletedFuntionTest' is dependent on column 'length'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN length failed because one or more objects access this column.
again i tried below steps:
In Object Explorer, navigate to the table node, and then select Table name (MathFuntion). Right click on the table and select "Design" , now the table in design Mode.
Here i updated the data type decimal(18,5) to int and save the table.
Now the table Updated without no errors.
Using Alter query i need to drop the index and again alter the table. but using tools options it is working .i need to know how this is working,is it right way ?
Thanks