SQLTeam.com | Weblogs | Forums

Alter Query is not working


#1

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


#2

Hi,

I tried the same(Graphical User Interface steps) what you have mentioned in sql server 2008 management studio but i got the error message.

Did you save the changes, after changing from decimal(p,s) to int in management studio?


#3

It is probably dropping and recreating. In SSMS, go to Tools -> Options, navigate down to Designers -> Table and Database Designers. There, if the "Prevent saving changes that require table re-creation" is checked, then you should get a message when saving the changes after changing the data type of the column. If it is unchecked, it silently drops and recreates the table.

This would be fine if your table has only a few rows in it. For large tables, SQL Server has to save all the data in the table, drop and recreate the table, and then repopulate the data. So it can be an expensive operation.


#4

Using Desinger Tools make the changes to the table and them BEFORE you save them use the SCRIPT function to create a SQL script of the actions that SSMS will perform if you press SAVE.

You can then view those to see how SSMS plans to make the change, and how that is different to your ALTER TABLE script.

My guess is that SSMS will drop all constraint, create a temporary table, recreate Defaults etc., copy all data across, drop the original table, rename the temporary table, recreate all indexes & foreign keys, Triggers etc. etc. etc.

We store scripts for all database changes (so we can apply them in DEV, TEST and PRODUCTION at rollout, and we nearly always use SSMS Designer and then generate the Script; we then save the script and ABANDON the Designer Changes in SSMS - i.e. we only ever use the Script and never use the SAVE option in SSMS Designer.