5

I have a table where I mistakenly allowed NULL values in a column:

CREATE TABLE EXAMPLE (
    EXAMPLE_ID INT IDENTITY(1, 1) NOT NULL,
    FOO_ID INT,
    CREATED_ON DATETIME DEFAULT CURRENT_TIMESTAMP, -- Oops!
    CODE VARCHAR(50),
    QUOTE DECIMAL(18,2),
    BAR_ID TINYINT NOT NULL DEFAULT 1,

    CONSTRAINT EXAMPLE_PK PRIMARY KEY (EXAMPLE_ID),

    CONSTRAINT EXAMPLE_FK1 FOREIGN KEY (FOO_ID)
    REFERENCES FOO (FOO_ID)
    ON DELETE CASCADE,

    CONSTRAINT EXAMPLE_FK2 FOREIGN KEY (BAR_ID)
    REFERENCES BAR (BAR_ID)
);

CREATE INDEX EXAMPLE_IDX1 ON EXAMPLE (FOO_ID);
CREATE INDEX EXAMPLE_IDX2 ON EXAMPLE (BAR_ID);

It's a fairly unimportant column (not indexed, not foreign key, not used in triggers...) and this is how I'd normally fix it:

ALTER TABLE EXAMPLE
ALTER COLUMN CREATED_ON DATETIME NOT NULL;
-- Runs in 1 second

However, I decided to use a GUI tool (SQL Server Management Studio, but all other tools I've tried do a similar thing) and found the whole process took like 20 minutes. The reason? The automatically generated change code is a 3351-line script that apparently tries to edit every single table on database that has a foreign key pointing to EXAMPLE.EXAMPLE_ID!

ALTER TABLE dbo.SOME_OTHER_TABLE
DROP CONSTRAINT SOME_OTHER_TABLE_FK
GO


BEGIN TRANSACTION
GO
ALTER TABLE dbo.SOME_OTHER_TABLE ADD CONSTRAINT
    SOME_OTHER_TABLE_FK FOREIGN KEY
    (
        EXAMPLE_ID
    ) REFERENCES dbo.EXAMPLE
    (
        EXAMPLE_ID
    ) ON UPDATE  NO ACTION
    ON DELETE  CASCADE

GO
ALTER TABLE dbo.SOME_OTHER_TABLE SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Further digging reveals that the script creates a new Tmp_EXAMPLE table with the final definition and copies all data from EXAMPLE.

Is this complex process necessary? What's the exact problem with plain ALTER TABLE?

  • 1
    Can't imagine anything going wrong with alter table. Curious why SSMS thinks it has to alter all the other tables, when the referenced column is not changing! – Andomar May 21 '15 at 9:56
  • @Andomar - That's a side effect of dropping the original table and moving data to a new one. Yet I wonder why it does so in the first place. – Álvaro González May 21 '15 at 10:04
  • 2
    Looks like SSMS is programmed to re-create tables to change a column's nullability Perhaps they have a list of safe operations, and altering the nullability of a non-PK was not on it? – Andomar May 21 '15 at 10:13
  • @Andomar - Thanks for the link, that thread gives a pretty good hypothesis about why it does it that way. I guess the only question left is: is raw ALTER TABLE safe enough for this particular usage? – Álvaro González May 21 '15 at 10:53
  • Sure, alter is very safe, not sure why you'd doubt that – Andomar May 21 '15 at 11:51
-1

Just take care of current NULL values by updating them to a default value.
Then do your ALTER TABLE

Example for an integer

UPDATE [MyTable]
SET [MyColumn] = 0
WHERE [MyColumn] IS NULL;
ALTER TABLE [MyTable] ALTER COLUMN [MyColumn] INTEGER NOT NULL;

Example for a varchar

UPDATE [MyTable]
SET [MyColumn] = ''
WHERE [MyColumn] IS NULL;
ALTER TABLE [MyTable] ALTER COLUMN [MyColumn] VARCHAR(max) NOT NULL;

Altering a column: null to not null

Security Measure

Before, make sure you backup your database in case of you screw it all

To make short

This whole complex scenario made by SSMS is not necessary there is no problem doing a plain ALTER TABLE

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.