| Author |
Topic |
|
tal_olier
Starting Member
13 Posts |
 Posted - 2002-09-01 : 08:42:59
|
| Hello, I'm trying the following test (which works like a charm on Oracle) create table x1(c1 numeric(10), c2 numeric(5,1))create index x1_c2_idx on x1(c2)alter table x1 alter column c2 numeric(9,1) I get the following error:Server: Msg 5074, Level 16, State 8, Line 1The index 'x1_c2_idx' is dependent on column 'c2'.Server: Msg 4922, Level 16, State 1, Line 1ALTER TABLE ALTER COLUMN c2 failed because one or more objects access this column.Is there a way to alter the column WITHOUT dropping the index ? Regards, Tal Olierotal@mercury.co.il |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-09-01 : 08:54:32
|
| The general gist of the question here is: does SQL Server need to rebuild an index if you change the data type of one or more dependent columns? Of course it does. I suspect Oracle does too; it might rebuild in the index in the background for you perhaps.In any event, if SQL Server wants you to drop the index, then drop it and re-create it after the ALTER TABLE.Jonathan{0} |
 |
|
|
tal_olier
Starting Member
13 Posts |
Posted - 2002-09-02 : 03:38:13
|
quote:
The general gist of the question here is: does SQL Server need to rebuild an index if you change the data type of one or more dependent columns? Of course it does. I suspect Oracle does too; it might rebuild in the index in the background for you perhaps.In any event, if SQL Server wants you to drop the index, then drop it and re-create it after the ALTER TABLE.Jonathan{0}
1. Why does it have to rebuild the index in the change of a column from numeric(5,1) to numeric(9,1) ?2. So what you are telling me that if I have a 50M records in a table and I want to enlarge a column I have to drop all related indexes - there's got to be a better solution than this one. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-09-02 : 07:21:58
|
| 1. because it has to allocate more space to the index pages....indices need to be stored somewhere...expanding a column in the index, then means more room needed for the index items.2. send MS a 'please include me as a feature' email? you wouldn't be the only one frustrated by it's absence. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-09-02 : 07:51:00
|
quote:
1. because it has to allocate more space to the index pages....indices need to be stored somewhere...expanding a column in the index, then means more room needed for the index items.
I think the point was that numeric(5,1) and numeric(9,1) both take 5 bytes to store, according to BOL. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-09-02 : 08:54:15
|
| yea....but is MS SQL Server smart enough to spot that???or is it utilising a generic rule of.....oldfielddefinition <> newfielddefinition => index column must be dropped!!! |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-09-02 : 10:50:37
|
quote:
2. So what you are telling me that if I have a 50M records in a table and I want to enlarge a column I have to drop all related indexes - there's got to be a better solution than this one.
Your question seems to imply that there's a great deal of cost associated with rebuilding all involved indexes - there isn't. I still don't quite see what you're driving at here. Are you arguing that SQL Server shouldn't have to rebuild the index b/c both types take the same amount of storage?Jonathan{0} |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-02 : 12:14:01
|
| Another thing, how often are you altering this column? Also, how many indexes does it participate in? If there's only one index on that column and you only need to alter it once, just drop the index, alter the column, and re-create the index, and get on with it. If this is too much trouble and Oracle saves you the trouble, convert your databases over to Oracle then. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-09-04 : 21:14:52
|
Or you could just make your change in Enterprise Manager and let EM handle all the dropping and rebuilding behind the scenes for you, and you won't know that it happened or be bothered with error messages.  Of course, I wouldn't really recommend this method because 1) You don't know exactly what's going on, 2) It will probably take longer because 3) EM will probably build a new table with the new definition, copy all records into it, drop the old one and still have to drop/rebuild the indices. |
|
|
|
|
Altering a column which has an index defined on it