What is the fastest way to alter a table

I have sql server table with 70M rows.
I need to increase the field len of a single field.
Last time I did this it toke 5 hours.
What is the fastest way to run something like this.  I don't care about log files or having to revert. I don't have 5 hours to wait.

ALTER TABLE myTable            
ALTER COLUMN [Customer] nvarchar(150)


thanks
LVL 1
JElsterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you adding the column during working hours?
If so, then create a job with the alter command and schedule it to run in non-working hours (during the night for example).
JElsterAuthor Commented:
Yes... Need to do immediate change.  Can't wait for off hours.
Vitor MontalvãoMSSQL Senior EngineerCommented:
This will run faster if no default value is set for that column and also if nobody access the table during the ALTER process.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You may also drop all indexes on the table before running the ALTER command and recreate them after the new column be added.
Scott PletcherSenior DBACommented:
As long as you are lengthening the column, that command should return very quickly; just be sure to specify exactly the same NULL or NOT NULL as the column currently has:

ALTER TABLE myTable            
ALTER COLUMN [Customer] nvarchar(150) NULL --or NOT NULL, based on the current column definition

If it takes too long, it's likely SQL is simply waiting to get the required schema lock to make the change.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.