Monday, January 3, 2011

Varchar(n) vs Varchar(Max)

Two questions:
1) You have a 1tb table with 1m rows, and need to change a field from varchar(5) to varchar(10). How long does it take, and how much log space is used?
2) You have a 1tb table with 1m rows, and need to change a field from varchar(5) to varchar(MAX). How long does it take, and how much log space is used?


The answer for (1) is simple: instantly, and none. Fortunately, it's a metadata operation, and since it knows everything already in the database must be the right size, no work needs be done.
For (2), I can't tell you - we rolled back after 90 minutes (it then took another 3+ hours to roll back), and it had consumed 200+gb of log space. On the plus side, it's table-partitioned, so we'll upgrade it that way.

No comments: