locked
How to Alter the Data type of Two column in one operation?? RRS feed

  • Question

  • How to Alter the Data type of Two column in one operation??
    Monday, November 15, 2010 12:05 PM

Answers

  • Hi .netmax,

    This will always require two statements. If it's important that the
    entire operation succeeds or fails as a whole, you should wrap it in a transaction and add proper error handling:

    BEGIN TRANSACTION;
    BEGIN TRY;
      ALTER TABLE xxx
        ALTER COLUMN yyy int NOT NULL;
      ALTER TABLE xxx
        ALTER COLUMN zzz nvarchar(250) NOT NULL;
      COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH;
      ROLLBACK TRANSACTION;
      RAISERROR ('Informative text goes here', 16, 1);
    END TRY;

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Chirag Shah Monday, November 15, 2010 3:04 PM
    • Marked as answer by Alex Feng (SQL) Monday, November 22, 2010 10:28 AM
    Monday, November 15, 2010 1:37 PM
  • Hugo posted the good script that shows how to do 2 statements in the transaction. As the side note, if your table have a lot of data AND you alter the data in the way that requires either rebuild or check entire data in the table (see: http://aboutsqlserver.com/2010/09/01/hidden-facts-about-table-alteration/ ), you can consider to create another table with the new structure, copy the data from the old table to the new one, drop the old one and rename the new one to the old name. This operation should be faster than 2 table rebuilds/scans.

    And as another side note, if you do everything within the table, don't forget to rebuild the clustered index after alteration.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Naomi NEditor Monday, November 15, 2010 6:34 PM
    • Marked as answer by Alex Feng (SQL) Monday, November 22, 2010 10:28 AM
    Monday, November 15, 2010 6:16 PM

All replies

  • I am afraid you cannot do that in one operation
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, November 15, 2010 12:13 PM
  • Hi .netmax,

    This will always require two statements. If it's important that the
    entire operation succeeds or fails as a whole, you should wrap it in a transaction and add proper error handling:

    BEGIN TRANSACTION;
    BEGIN TRY;
      ALTER TABLE xxx
        ALTER COLUMN yyy int NOT NULL;
      ALTER TABLE xxx
        ALTER COLUMN zzz nvarchar(250) NOT NULL;
      COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH;
      ROLLBACK TRANSACTION;
      RAISERROR ('Informative text goes here', 16, 1);
    END TRY;

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Chirag Shah Monday, November 15, 2010 3:04 PM
    • Marked as answer by Alex Feng (SQL) Monday, November 22, 2010 10:28 AM
    Monday, November 15, 2010 1:37 PM
  • Hugo posted the good script that shows how to do 2 statements in the transaction. As the side note, if your table have a lot of data AND you alter the data in the way that requires either rebuild or check entire data in the table (see: http://aboutsqlserver.com/2010/09/01/hidden-facts-about-table-alteration/ ), you can consider to create another table with the new structure, copy the data from the old table to the new one, drop the old one and rename the new one to the old name. This operation should be faster than 2 table rebuilds/scans.

    And as another side note, if you do everything within the table, don't forget to rebuild the clustered index after alteration.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Naomi NEditor Monday, November 15, 2010 6:34 PM
    • Marked as answer by Alex Feng (SQL) Monday, November 22, 2010 10:28 AM
    Monday, November 15, 2010 6:16 PM
  • Not in SQL Server, but you can change two columns at once in Visual FoxPro :)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, November 15, 2010 6:35 PM
    Answerer