14

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?

24

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.

  • 2
    Curious why Visual Studio scripts it as DROP/CREATE INDEX. Probably unnecessary, unconditional CYA. – Aaron Bertrand Jan 16 '14 at 17:20
  • 2
    @AaronBertrand - I suppose it's just a missing optimisation for the cases where it isn't required. Books online indicated that this was needed in a few cases where the product did not actually require it as well until this update – Martin Smith Jan 16 '14 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.