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?