September 3, 2013 at 4:06 pm
>>>>Scenario:
SQL Server 2008 R2 on a Windows cluster, 256 GB RAM, 32 CPU
there is a non-partitioned table with 2.5 Billion records, 1TB in size, 100 fields.
Due to a changed Business logic, one field in the table needs to be modified from VARCHAR(65) NULL to VARCHAR(85) NULL.
There is some 20% NON-NULL values in the field.
database recovery mode is simple.
<<<<END Scenario
What is the risk [if any] of running simple ALTER COLUMN DDL to make this conversion?
ALTER TABLE MyTable ALTER COLUMN MyCol VARCHAR(85) NULL
What is the best way to approach this in your opinion?
My understanding that ALTER COLUMN will not have to allocate any new pages to the table, so it should run fast, but there are some other opinions in the house..
Thank you.
September 3, 2013 at 4:30 pm
That's a meta-data only operation. Because varchar is variable-length, nothing in the rows has to change as a result of that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2013 at 4:34 pm
in other words, the modiication should execute fast, correct?
Thanks, Gail.
September 3, 2013 at 11:35 pm
Sergei Zarembo (9/3/2013)
in other words, the modiication should execute fast, correct?Thanks, Gail.
Yes. Provided it is not being blocked it will complete practically instantaneously.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 4, 2013 at 7:26 am
opc.three (9/3/2013)
Sergei Zarembo (9/3/2013)
in other words, the modiication should execute fast, correct?Thanks, Gail.
Yes. Provided it is not being blocked it will complete practically instantaneously.
Thank you for your feedback, opc.three.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply