Post reply

ALTER TABLE ALTER COLUMN

  • June 5, 2008 at 7:24 am

    #188716

    HI all,

    I want to Alter a column from Null to NOT Null with NO CHECK is thsi possible?

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • June 5, 2008 at 7:42 am

    #824802

    Hi All,

    Alternatively is it possible to add a nullable field without a default to a table that has data using a WITH NOCHECK?

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • June 5, 2008 at 8:21 am

    #824832

    You can add a nullable column to a table using alter table. It won't affect the rest of the table at all.

    NO CHECK is functionality around check constraints. If you change a column from nullable to NOT NULL, SQL Server is going to check the table to ensure that there are no NULL columns. It has to.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • June 5, 2008 at 8:23 am

    #824833

    HI 🙂

    Thanks I thought that was the case and it makes 100% sense.

    I was just trying my luck. I'm glad that I can't do it to be honest, as this means SQL is keeping it's DATA integrity

    thanks for the answer

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • June 5, 2008 at 8:24 am

    #824834

    Christopher Stobbs (6/5/2008)

    Hi All,

    Alternatively is it possible to add a nullable field without a default to a table that has data using a WITH NOCHECK?

    Thanks

    Chris

    Yes that is possible:

    ALTER TABLE tbl_name WITH NOCHECK

    ADD colx int NULL;

    Now, you will still lock the table. If that is what you are trying to avoid you are out of luck 😉


    * Noel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply