Post reply

Question on altering column

  • March 15, 2011 at 3:37 am

    #99558

    If I alter tables from varchar(10) to varchar(20), Should i go ahead with disable-enable index or drop-create index ?

  • March 15, 2011 at 3:43 am

    #1298738

    Either should work, and they're pretty synonymous. That said, you only want to disable indexes that reference the column you're changing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 15, 2011 at 4:11 am

    #1298740

    Ok. in case of disable-enable condition, if we have primary key constraint, will that constaint work same way [once enabled after changing the column] as it work before disabling ? Also hope we don't have to think about triggers.

  • March 15, 2011 at 4:18 am

    #1298742

    Its giving me error while altering the column after disabling the primary constraint.

    Msg 1974, Level 16, State 3, Line 1

    Cannot perform the specified operation on table 'temp' because its clustered index 'pk_temp' is disabled.

  • March 15, 2011 at 4:21 am

    #1298743

    Ok, clustered index you'd have to drop (that's an alter table since it's the primary key). Ss per Books online, disabling the clustered index completely prevents access to the table.

    Since the cluster is on the column that you're changing, drop all the nonclustered indexes first and recreate them after.

    You didn't mention in your initial post that was the primary key/clustered index column.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 18, 2011 at 6:28 am

    #1300442

    Learned few things from this operation.

    First, If the column to be altered is a part of Primary contraint, foreign key constraint or uniqu constraint, you need to drop them as per the dependency, thereafter alter the column [with not null option, if required] and recreate those constraint, again on the basis of dependency.

    Second, if that column is a part of clustered or nonclustered index, we can only alter in case of increasing the length. If we decrease, it will be a violation and we need to drop the index, alter the column and then recreate the index.

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

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