How long can this ALTER TABLE statement take? Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL


Suppose


there are 45 GB data space and 2GB index space;
about 3 million rows in this table;
column YYY is varchar(8000) now and can be updated (is writable).
the table has about 30 other columns.
There are about 3000G data from other database &amp; tables on this machine.


Some other information:


99.99% of rows have NULL in this varchar(8000);
the web application may hit this table 5 times per minute;
the hardware is enterprise level (8 core CPU and 256GB RAM).
there are other tables and database on this machine, about 3000GB data.


Relevant @@VERSION details: 


  Microsoft SQL Server 2014 - 12.0.4422.0 (X64) Enterprise Edition (64-bit)
Nobody can tell you with any degree of certainty or accuracy how long this operation will take on your system. However I suggest the change will likely have less impact than you think. I set up a quick test to compare changing a table from int -> bigint and from varchar(8000) -> varchar(max). First, two simple tables:

CREATE TABLE dbo.t0(a int primary key, b int);
GO
CREATE TABLE dbo.t1(a int primary key, b varchar(8000));
GO


Now, insert at least one non-NULL value, and then over 5 million rows (on my system; YMMV):

INSERT dbo.t0 VALUES(0,1);
INSERT dbo.t0 SELECT rn, NULL FROM 
( SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS x;
GO -- 5,299,204 rows for me

INSERT dbo.t1 VALUES(0,'what');
INSERT dbo.t1 SELECT rn, NULL FROM 
( SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS x;
GO -- 5,299,204 rows for me


Then I tested the ALTER change, with stats I/O enabled:

SET STATISTICS IO ON;
GO
ALTER TABLE dbo.t0 ALTER COLUMN b bigint NULL; 
GO -- 9 seconds, 122,506 reads
SET STATISTICS IO OFF;


This finished in 9 seconds and required 122,506 reads. 

SET STATISTICS IO ON;
GO
ALTER TABLE dbo.t1 ALTER COLUMN b varchar(max) NULL; 
GO -- 5 seconds, 8,562 logical reads
SET STATISTICS IO OFF;


This finished in 5 seconds and only required 8,562 reads. 

So while neither operation happened online, and while your actual results may differ based on hardware and your table structure, this was on a lowly Windows VM on a MacBook, so I would expect your time to be better than this.

I also tested with a lot more data populated in the nullable column. Dropped the tables, re-created and re-populated with the above scripts, then ran this to put something in 100,000 rows:

;WITH x AS (SELECT TOP (100000) a,b FROM dbo.t1 ORDER BY NEWID())
UPDATE x SET b = a;
GO
;WITH x AS (SELECT TOP (100000) a,b FROM dbo.t1 ORDER BY NEWID())
UPDATE x SET b = REPLICATE(RTRIM(a), 1000);
GO


This took way longer than the subsequent ALTERs (over 3 minutes). The first ALTER still took 9 seconds, but the second one took 17, simply because the number of reads shot up (to 189,854). This is similar to what would happen if you had, say, 30 other columns in this table. I still think 17 seconds is pretty good for what is actually happening here, and again, this is far from an enterprise-level environment.

As Paul points out, starting with SQL Server 2016, you can get this to happen online, but there are restrictions and limitations. For more information see the docs.

ALTER TABLE dbo.t1 ALTER COLUMN b varchar(max) NULL WITH (ONLINE = ON);


This option is not available in SQL Server 2014, though.

One way you could get this to happen more online is to simply move that column to a separate, related table. There are some intricacies here with how you cut over, and it might impact a bunch of code, but you can solve most issues with a view (at least temporarily).