Can I change colum width of varchar in production

Question

Post reply

Can I change colum width of varchar in production

indianscorpion29

Right there with Babe

Points: 750
More actions
October 17, 2014 at 3:20 am

#291029

Hi,
I keep getting requests to increase the width of a varchar colum every now and then.
I want to ask if its perfectly ok when you have active users connecting to the application to do this?
Just want to be on the safe side.
thanks,
October 17, 2014 at 4:22 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

This is something that might take some planning.

How big is the table?

Is the column used in indexes or in constraints?

Be aware that changing the width of the column might update all of the rows in the table and thus will lead to a lot of logging.

Furthermore, the table is also locked with a schema lock during the update.

More info:

ALTER TABLE (Transact-SQL)

(check the Remarks section)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

As far as I know, increasing the width of the column is a metadata only operation and doesn't require updating all the rows.

Reducing the width of the column does.

-- Gianluca Sartori

indianscorpion29 Right there with Babe Points: 750 More actions · Answer 3

Thanks you folks.

I did this through T-SQL alter table statement and users were unaffected with the operation.

thanks,

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I'm glad you were able to successfully do what needed to be done, but I wouldn't recommend making a habit of just making changes like this in production. You should be testing things like this before doing them in production.

You also should be monitoring performance because if you have a situation where one of your varchar values gets updated to take advantage of the new size, you could get page splits.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Jack Corbett (10/20/2014)
You should be testing things like this before doing them in production.

+1000

-- Gianluca Sartori

ScottPletcher SSC Guru Points: 101105 More actions · Answer 6

Increasing the size of a [n]varchar column isn't directly a problem.

But, if you think people will go back and increase the length of those columns in a significant number of existing rows, monitor the table closely for page splits and rebuild the table when necessary.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".