When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

Question

In our database a big table exists that more or less looks like this:

CREATE TABLE dbo.production_data
(
    pd_id BIGINT PRIMARY KEY,
    serial NVARCHAR(16) NOT NULL UNIQUE,
    ...
);

but now the size of the serial field has become to low, so I want to change it to 32. The Visual Studio schema compare tool suggests doing this by:

DROP INDEX ux_production_data_serial ON dbo.production_data;
GO
ALTER TABLE dbo.production_data ALTER COLUMN serial NVARCHAR(32) NOT NULL;
GO
CREATE INDEX ux_production_data_serial ON dbo.production_data(serial ASC);

Is this really needed? Or more like a ultra save way of doing this?

Also when recreating the unique index, will my table get locked? Because this would be a big problem (as the table has 30 million rows and i guess recreating the index will take quite some time), because the next maintenance window is a few month in the future. What are my alternatives?

Answer 1

There is no need to drop and recreate the index.

Just use

ALTER TABLE dbo.production_data
  ALTER COLUMN serial NVARCHAR(32) NOT NULL; 

This is a metadata only change.

Altering a column from NVARCHAR(16) to NVARCHAR(32) does not affect the storage at all.

Going the other way round (from NVARCHAR(32) to NVARCHAR(16)) would give you an error about objects being dependent on the column though so maybe Visual Studio just always generates that boiler plate code in lieu of checking whether it is actually required.