Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
1  
Curious why Visual Studio scripts it as DROP/CREATE INDEX. Probably unnecessary, unconditional CYA. –  Aaron Bertrand Jan 16 at 17:20
1  
@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 at 17:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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