Alter command

  • Hi All,

    i need to alter multiple column in from null to not null in production server.

    table have millions of records, and taking each alter is taking hours to run. can anybody suggest me better option...

    ALTER TABLE Panelist ALTER Column "RegSourceID" int not null ALTER TABLE Panelist ALTER Column "CultureID" int not null ALTER TABLE Panelist ALTER Column "EmailStatusTypeID" int not null

    thanks in advance

    Puneet

  • Do you know which columns need to be altered? Or are you changing every single column to Not NULL?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Yes i know all those column which needs to be converted to Not Null

  • Here is a quick and dirty that you could run to create all of your statements.

    With MassAlter as (

    Select Table_Schema,Table_Name,Column_Name,Case

    When IsNull(Character_Maximum_length,0) = 0

    Then Data_Type

    Else Convert(Varchar(40),Data_Type + '(' + Convert(Varchar(20),Character_Maximum_Length) + ')')

    End as Data_Type

    ,IS_NULLABLE

    From INFORMATION_SCHEMA.Columns

    Where Is_Nullable = 'Yes'

    )

    Select 'Alter Table ' + TABLE_SCHEMA + '.'+ Table_Name + ' Alter Column ' + Column_Name + ' ' +

    Data_Type + ' Not Null'

    From MassAlter

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks Alot for this,

    can you please tell me a another way through which i can alter all this column as this will take hours to complete.

  • Two options

    Create a cursor that does essentially the same thing I just showed you.

    or

    Run that script, review the commands, and then set it up in a job to execute in batches. You will need to add ';' or go statements in between the statements.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (6/9/2010)


    Two options

    Create a cursor that does essentially the same thing I just showed you.

    or

    Run that script, review the commands, and then set it up in a job to execute in batches. You will need to add ';' or go statements in between the statements.

    Ummmm.... I don't believe that's what the OP means, Jason. I believe the OP wants to know if all the columns can be altered by a single command so only one pass needs to be made on the table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (6/9/2010)


    CirquedeSQLeil (6/9/2010)


    Two options

    Create a cursor that does essentially the same thing I just showed you.

    or

    Run that script, review the commands, and then set it up in a job to execute in batches. You will need to add ';' or go statements in between the statements.

    Ummmm.... I don't believe that's what the OP means, Jason. I believe the OP wants to know if all the columns can be altered by a single command so only one pass needs to be made on the table.

    You're probably right.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • pujain (6/9/2010)


    Hi All,

    i need to alter multiple column in from null to not null in production server.

    table have millions of records, and taking each alter is taking hours to run. can anybody suggest me better option...

    ALTER TABLE Panelist ALTER Column "RegSourceID" int not null ALTER TABLE Panelist ALTER Column "CultureID" int not null ALTER TABLE Panelist ALTER Column "EmailStatusTypeID" int not null

    thanks in advance

    Puneet

    "It depends"...

    If you have the ENTERPRISE edition, you could build a new table and use SPLIT to move the data almost instantaneously based on a trick I saw Paul White use recently.

    If you don't have the ENTERPRISE edition, you can use SELECT/INTO to both create and populate a new table and then rename the tables. The keys for this method are...

    1. If you are using the FULL recovery model, take a full backup (including a transaction log backup, all just to be safe) and temporarily change to the BULK-LOGGED model.

    2. Do NOT using SELECT * to do the SELECT/INTO. Instead, list every column name and for the columns you want to have as NOT NULL, use ISNULL() around each of those columns with the appropriate second operand for whatever datatype the column is.

    Of course, since it's a new table either way, you'll need to make sure that the correct permissions are granted and indexes, keys, and foreign keys are created as well as going through the table renaming process.

    Or... you can use the table designer and it'll actually do all of that for you although it will be a logged process (including the table rename) and take just a little longer.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @jeff

    Which would be the better option ?

    1. USE ALTER TABLE ....ALTER COLUMN approach

    2. Create new table and then push the exisitng table's data into it.

    and does sql server use same locking mechanism in both cases ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (6/9/2010)


    @Jeff

    Which would be the better option ?

    1. USE ALTER TABLE ....ALTER COLUMN approach

    2. Create new table and then push the exisitng table's data into it.

    and does sql server use same locking mechanism in both cases ?

    They're both going to render the table pretty much useless for the duration. However, in light of the fact that the OP wants to change multiple columns, the "Create and Push" method would likely be the fastest even in the STANDARD edition. Using the SPLIT command, it would likely be nearly instantaneous (except for reindexing and rekeying) in the ENTERPRISE edition.

    To know for sure, though, you'd have to do an insitu test using a small table of, say, a million or two rows.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @pujain

    from below link, i learnt that first we need to update concerned (NULLABLE) columns ( with any dummy values) then we can change them to NOT NULL.

    though i am not too sure about that additionally i didnt tested it.

    http://sqlserverplanet.com/sql/alter-table-alter-column/

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Hey,

    i tried both the method but creating a table and inserting it seems a little better but still not an easy one as i am having a table with 15 million records and 85 columns, creating index to the new one looks a difficult task.

    do any body have any suggestion to reduce my headache?

    thanks in advance.

Viewing 13 posts - 1 through 12 (of 12 total)

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