SQL Server Performance

Altering column width

Discussion in 'General DBA Questions' started by gkrishn, Apr 6, 2005.

  1. gkrishn New Member

    Hi

    i want to alter column width of one column in a table. Does it make any impact on current running activities?

    eg: varchar(2) to varchar(5) for column emp_name.Please advice
  2. dineshasanka Moderator


    You will not be able not alter if that column is

    A column with a text, image, ntext, or timestamp data type.


    The ROWGUIDCOL for the table.

    A computed column or used in a computed column.

    A replicated column.

    Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.

    Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.

    Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.

    Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.

    Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.

    Other than this I can't see any other issues
  3. satya Moderator

    What is the record volume on this table?
    Make sure to update statistics on this table in order to update the information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. FrankKalis Moderator

    To minimize the impact on runnin activities don't use EM!!! Use Query Analyzer instead


    set nocount on
    create table #test
    (
    c1 varchar(2)
    )
    insert into #test values('ab')
    insert into #test values('abcde')
    alter table #test
    alter column c1 varchar(5)
    go
    insert into #test values('abcde')
    select * from #test
    drop table #test
    set nocount off

    Server: Msg 8152, Level 16, State 9, Line 7
    String or binary data would be truncated.
    The statement has been terminated.
    c1
    -----
    ab
    abcde

    I would also do this during off-peak hours.

    --
    Frank
    SQL Server MVP
    http://www.insidesql.de
  5. gkrishn New Member

    Sathya, this table has lakhs of records. thts y i asked any impact on current data or table. There is only one procedure which insert data to this table .no other appli or procedure accessing it.


    This is the query i am supposed to run
    ALTER TABLE useractivity ALTER COLUMN [program_name] [varchar] (80) NOT NULL

    currently its [program_name] [varchar] (30) only. Please advice. What you told me abt update statistics??
  6. satya Moderator

    Follow as suggested by Frank to use QA instead of EM.

    In order to update the change of the schema UPDATE STATISTICS is essential, other wise you may get performance degradation due to this change. Its better to avoid such hassle before in hand.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page