CODEWITHSUNDEEP
sql-server
Alessio Innocenzi
Alessio Innocenzi

Reputation: 471

Alter table nvarchar(100) to nvarchar(150) in production environment in a table with 20 million rows

I should change the nvarchar size from 100 to 150 in my production table. This is a huge table in a production environment (over 20 million rows) with continuous read and write. Are there any best practices to do that safely (without locking table)?

Upvotes: 0

Views: 268

Answers (1)

Stu
Stu

Reputation: 32629

Provided you are not changing the nullability (null > not null or vice versa), this is basically an instant change to the table meta data; ensure you include the existing column's null state in the alter statement.

The operation will need to aquire a schema stability lock to make the change so be aware this itself may briefly cause blocking of other queries against the table, however the operation itself is quick.

Upvotes: 1

Related Questions