March 19, 2017 at 3:41 am
Tried to change the datatype from nvarchar(250) to nvarchar (20) using below script and its threw error ,but the same able to achieve using SSMS table design. I have some column datatypes needs to be changes like this from higher to lower value. Can anyone help us understand why this works fine through SSMS ,what happens to Index at that time?
ALTER TABLE [IndexTest] alter COLUMN [varchar] nvarchar(20);
Msg 5074, Level 16, State 1, Line 19
The index 'NonClusteredIndex-20170319-133137' is dependent on column 'varchar'.
Msg 4922, Level 16, State 9, Line 19
ALTER TABLE ALTER COLUMN varchar failed because one or more objects access this column.
March 19, 2017 at 4:16 am
I didn't check it for a long time, but as far as I remember the GUI creates a new table, copies the data to the new table, drops the old table and renames the new table to have the same name as the old one. Because the GUI creates a new table and doesn't alter the existing table, you don't get an error message that you should drop any existing object that depends on the column that you try to modify. When you are using the alter table statement, the server alters the existing table and doesn't create a new one. This is of course a much better approach, but sometimes you'll might get an error message. In your case you need to drop the index that is based on this column (and if you also have constraint that is based on it, you'll need to drop it too), modify the column and then recreate the index and any other object that you dropped.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2017 at 6:46 am
Rechana Rajan - Sunday, March 19, 2017 3:41 AMTried to change the datatype from nvarchar(250) to nvarchar (20) using below script and its threw error ,but the same able to achieve using SSMS table design. I have some column datatypes needs to be changes like this from higher to lower value. Can anyone help us understand why this works fine through SSMS ,what happens to Index at that time?ALTER TABLE [IndexTest] alter COLUMN [varchar] nvarchar(20);
Msg 5074, Level 16, State 1, Line 19
The index 'NonClusteredIndex-20170319-133137' is dependent on column 'varchar'.
Msg 4922, Level 16, State 9, Line 19
ALTER TABLE ALTER COLUMN varchar failed because one or more objects access this column.
Drop the index NonClusteredIndex-20170319-133137 before altering the column then create it again
😎
March 20, 2017 at 5:07 am
Adi Cohn-120898 - Sunday, March 19, 2017 4:16 AMI didn't check it for a long time, but as far as I remember the GUI creates a new table, copies the data to the new table, drops the old table and renames the new table to have the same name as the old one. Because the GUI creates a new table and doesn't alter the existing table, you don't get an error message that you should drop any existing object that depends on the column that you try to modify. When you are using the alter table statement, the server alters the existing table and doesn't create a new one. This is of course a much better approach, but sometimes you'll might get an error message. In your case you need to drop the index that is based on this column (and if you also have constraint that is based on it, you'll need to drop it too), modify the column and then recreate the index and any other object that you dropped.Adi
Thanks Adi. Why NO with SSMS?
March 20, 2017 at 5:08 am
Eirikur Eiriksson - Sunday, March 19, 2017 6:46 AMRechana Rajan - Sunday, March 19, 2017 3:41 AMTried to change the datatype from nvarchar(250) to nvarchar (20) using below script and its threw error ,but the same able to achieve using SSMS table design. I have some column datatypes needs to be changes like this from higher to lower value. Can anyone help us understand why this works fine through SSMS ,what happens to Index at that time?ALTER TABLE [IndexTest] alter COLUMN [varchar] nvarchar(20);
Msg 5074, Level 16, State 1, Line 19
The index 'NonClusteredIndex-20170319-133137' is dependent on column 'varchar'.
Msg 4922, Level 16, State 9, Line 19
ALTER TABLE ALTER COLUMN varchar failed because one or more objects access this column.Drop the index NonClusteredIndex-20170319-133137 before altering the column then create it again
😎
Thanks Eirikur , Why the difference in SSMS and TSQL. Why you recommend to drop and modify the column and then add the index again?
March 20, 2017 at 5:18 am
Rechana Rajan - Monday, March 20, 2017 5:08 AMEirikur Eiriksson - Sunday, March 19, 2017 6:46 AMRechana Rajan - Sunday, March 19, 2017 3:41 AMTried to change the datatype from nvarchar(250) to nvarchar (20) using below script and its threw error ,but the same able to achieve using SSMS table design. I have some column datatypes needs to be changes like this from higher to lower value. Can anyone help us understand why this works fine through SSMS ,what happens to Index at that time?ALTER TABLE [IndexTest] alter COLUMN [varchar] nvarchar(20);
Msg 5074, Level 16, State 1, Line 19
The index 'NonClusteredIndex-20170319-133137' is dependent on column 'varchar'.
Msg 4922, Level 16, State 9, Line 19
ALTER TABLE ALTER COLUMN varchar failed because one or more objects access this column.Drop the index NonClusteredIndex-20170319-133137 before altering the column then create it again
😎Thanks Eirikur , Why the difference in SSMS and TSQL. Why you recommend to drop and modify the column and then add the index again?
I recommend dropping the index before altering the column as the column is used in the index.
😎
March 23, 2017 at 12:34 am
Rechana Rajan - Monday, March 20, 2017 5:07 AMAdi Cohn-120898 - Sunday, March 19, 2017 4:16 AMI didn't check it for a long time, but as far as I remember the GUI creates a new table, copies the data to the new table, drops the old table and renames the new table to have the same name as the old one. Because the GUI creates a new table and doesn't alter the existing table, you don't get an error message that you should drop any existing object that depends on the column that you try to modify. When you are using the alter table statement, the server alters the existing table and doesn't create a new one. This is of course a much better approach, but sometimes you'll might get an error message. In your case you need to drop the index that is based on this column (and if you also have constraint that is based on it, you'll need to drop it too), modify the column and then recreate the index and any other object that you dropped.Adi
Thanks Adi. Why NO with SSMS?
As Adi has explained, the SSMS/GUI drops and recreates the table with relevant indexes and constraints.
You can check the script generated under "Table Designer -> Generate Change Script"
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 1, 2017 at 6:55 am
Kingston Dhasian - Thursday, March 23, 2017 12:34 AMRechana Rajan - Monday, March 20, 2017 5:07 AMAdi Cohn-120898 - Sunday, March 19, 2017 4:16 AMI didn't check it for a long time, but as far as I remember the GUI creates a new table, copies the data to the new table, drops the old table and renames the new table to have the same name as the old one. Because the GUI creates a new table and doesn't alter the existing table, you don't get an error message that you should drop any existing object that depends on the column that you try to modify. When you are using the alter table statement, the server alters the existing table and doesn't create a new one. This is of course a much better approach, but sometimes you'll might get an error message. In your case you need to drop the index that is based on this column (and if you also have constraint that is based on it, you'll need to drop it too), modify the column and then recreate the index and any other object that you dropped.Adi
Thanks Adi. Why NO with SSMS?
As Adi has explained, the SSMS/GUI drops and recreates the table with relevant indexes and constraints.
You can check the script generated under "Table Designer -> Generate Change Script"
Thanks Kingston
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply