0

I need to enlarge the varchar of one column from 64 to 80. The table is quite big(9m rows). One point that make the alter uncertain is that the column is also indexed.

So if I alter the column, since the column is one of the indexes, will any locking happen for row or table?

Thanks.

user4604022
  • 1
  • Related: http://stackoverflow.com/a/4849030/1394393. My advice would be to get rid of the length from the `VARCHAR` *type* while you have the chance. If necessary, replace it with a constraint as discussed in the linked answer/article. – jpmc26 Mar 18 '15 at 01:19
  • Sounds that varchar is not a good option from the start. It looks I should first to alter the column to text, and then add some constraint outside. But 'text' looks not good for index as well in my first feeling, am I right? – user4604022 Mar 18 '15 at 01:20
  • `text` and `varchar` are exactly the same when no length is put on `varchar`. Even with a length, they're both still implemented the same under the hood. Given that all the basic text types share the same underlying implementation, I'd be *extremely* surprised if the index performed any differently for any of them. The drawbacks discussed in that article all have to do with length constraints (except for `char`, which has weird behavior due to legacy standards). – jpmc26 Mar 18 '15 at 01:30
  • I just did the test. 'alter' dose lock the whole table no matter the column you want to change is indexed or not. It looks doomed if the varchar is used at the first place. It's annoying. – user4604022 Mar 18 '15 at 03:20
  • 1
    I would generally expect that the site is simply going to have to be down a few minutes while you perform this operation. Is scheduling some downtime not an option for some reason? How much traffic is your site actually getting? There might be a window when not many users would be impacted. – jpmc26 Mar 18 '15 at 22:08

1 Answers1

0

Not sure about locking part. but standard recommendation would be to drop the index first and then alter table and again create the index.

drop index [[index name]]
go

alter table t1 [[alter column]]
go

create index [[index name]] 
go
Darshan Joshi
  • 362
  • 1
  • 11
  • Yes, exactly. But I have many concurrent queries there using that index. If dropping the index, I think it will blow the db out soon before the alter is finished. – user4604022 Mar 18 '15 at 01:16
  • I still think the only way is to drop the index first. Do you have any instance of the db where you can try this first? – Darshan Joshi Mar 18 '15 at 01:26
  • I did try drop, alter and then create again, but without much traffic, so still not certain about the way. – user4604022 Mar 18 '15 at 02:13