Does ALTER TABLE ALTER COLUMN interrupt ongoing db access?

Question

I have a column in a table so that it is no longer NVARCHAR(256) but is NVARCHAR(MAX). I know the command to do this (ALTER TABLE ALTER COLUMN NVARCHAR(MAX)). My quesiton is really about disruption. I have to do this on a production environment and I was wondering if while I carry this out on the live environment there is a chance that there may be some disruption to usage to users. Will users who are using the database at the time be booted off? Will this operation likely take too long?

Thanks,

Sachin

Answer 1

I've deleted my previous answer which claimed that this would be a metadata only change and am submitting a new one with an entirely different conclusion!

Whilst this is true for changing to up to nvarchar(4000) for the case of changing to nvarchar(max) the operation does seem extremely expensive. SQL Server will add a new variable length column and copy the previously existing data which will likely mean a time consuming blocking operation resulting in many page splits and both internal and logical fragmentation.

This can be seen from the below

CREATE TABLE T
(
Foo int IDENTITY(1,1) primary key,
Bar NVARCHAR(256) NULL
)

INSERT INTO T (Bar)
SELECT TOP 4 REPLICATE(CHAR(64 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))),50)
FROM sys.objects

ALTER TABLE T ALTER COLUMN Bar NVARCHAR(MAX) NULL

Then looking at the page in SQL Server Internals Viewer shows

The white 41 00 ... is wasted space from the previous version of the column.

Answer 2

Any ongoing queries will not be affected. The database has to wait until it can make an exclusive table lock before it can be altered.

While the update is done, no queries can use the table, so if there is a lot of records in the table, the database will seem unresponsive to any queries that would need to use the table.