| Author |
Topic |
|
xpandre
Posting Yak Master
212 Posts |
 Posted - 2009-12-29 : 10:09:01
|
| Hi,We would be resizing a column from char(5) to nvarchar(100). And this column has an non clustered index as well.Would resizing effect the index in any way? ( rowcount - 1/10th of a million)Thanks |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-29 : 10:59:23
|
| How were you going to go about this? Just by editing the table in management studio?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
xpandre
Posting Yak Master
212 Posts |
Posted - 2009-12-29 : 11:04:35
|
| By command on SSMSalter table xyz alter column abc nvarchar(100) |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-29 : 11:13:16
|
| When you change the columns then what's happening behind the scenes is that temp table is made with the new column definitions and all the information is copied into it. The old table is then dropped and the indices and constraints are recreated on the new table.Your non clustered index should be OK as a 100 length NVARCHAR is small enough.You should run profiler some time while doing this. The steps are quite interesting.Everything looks OK on my test table with 100,000 rowsCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-29 : 11:14:42
|
| If you do it that way (alter table) you'll have to drop and recreate the index.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-29 : 12:25:17
|
| Need to drop/recreate the index, else you'll getMsg 5074, Level 16, State 1, Line 1The index 'idx_name' is dependent on column 'xyz'.Msg 4922, Level 16, State 9, Line 1ALTER TABLE ALTER COLUMN abc failed because one or more objects access this column. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-29 : 12:35:53
|
Me too. Maybe it's on purpose since the storage requirement for the index is changed, AND it has to be rebuilt since the row layout in page would be changed...thus MS wants to make sure there is manual intervention...or it's just an oversight  |
|
|
|
xpandre
Posting Yak Master
212 Posts |
Posted - 2009-12-29 : 13:57:09
|
| oh yes...we have to drop n recreate..sheesh..should have checked before..Sorry for the trouble.Would recreation of Index affect the performance of other queries accessing this columns? Would those queries re-create a new execution plan? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Column resizing