Old Sql2008 box and I need to change 4 fields length on a table which contains 18+ million records.
It's going from varchar(10) to varchar(12)
I tried from SSMS but timed out ...
If I do an alter
ALTER TABLE [xxx]
ALTER COLUMN yyy VARCHAR (12) NULL.
I have 8 active connections to the database. Will I lock the table?
sorry I know old version trying to get it upgraded .. didn't know where else to post ..
THanks.
November 18, 2022 at 12:37 pm
The simplest way would be to create a new table, insert the data from the original table, rename both tables and run an update to catch any changes made between the insert and the renaming if necessary.
😎
There are many factors to consider and without further details, detailed advice would be pure guesswork.
November 18, 2022 at 12:43 pm
There are around 35 fields defined in the table.. 13 non-clustered indexes, and triggers on the table to boot..
I was hoping I could get away with the alter....
Thanks
November 18, 2022 at 1:17 pm
There are around 35 fields defined in the table.. 13 non-clustered indexes, and triggers on the table to boot..
I was hoping I could get away with the alter....
Thanks
As I already stated, it is impossible to advise in any more detail without further information.
😎
November 18, 2022 at 1:33 pm
What other info can I provide?
thx.
November 18, 2022 at 2:54 pm
okay thanks for suggestions... I'll do some benchmarks.
November 18, 2022 at 4:58 pm
If you try running the alter table command, check whether it is being blocked. I think alter the table requires a SCH-M lock which may not be possible if the table is in use. Once the lock is obtained the change should be almost immediate as it is a metadata change only, assuming you don't go to varchar(max) or change the null setting of the column . (Someone correct me if I am wrong). I have altered varchar columns in sql server 2008 on much bigger tables and not had any difficulties. Fixed with columns are a different matter. I believe that while you wait for the sch-m lock, subsequent queries will also be blocked.
I'm not sure whether the inclusion of the column in an index complicates things, but if you run sp_whoisactive while the alter table is running you will see whether it's blocked, or actually doing work that is taking a long time.
November 18, 2022 at 7:12 pm
Thanks for info...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply