Does ALTER TABLE ALTER COLUMN interrupt ongoing db access?

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 t开发者_开发问答he database at the time be booted off? Will this operation likely take too long?

Thanks,

Sachin

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.

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.

The advice has to be - make a backup and do it out of hours if you can.

That having been said, I would not expect your database to be disrupted by the change and it will not take very long to do it.

What about your client software ? How will that be affected ?

It should be fine, unless you have a massive amount of rows (millions).. Yes, it will lock the table while it's updating but pending requests will just wait on it.