Post reply

Renaming Primary Key - Impact ?

  • August 23, 2012 at 10:07 am

    #263336

    I need to rename a primary key, example IX_Cust# to IX_Cust_ID. There will be no change to the key, just the name is changing. If I right-click and choose "Rename", will it drop & recreate, or will it simply rename it ? I would like to avoid having to wait until the weekend, but don't want to impact production.

  • August 23, 2012 at 5:36 pm

    #1529495

    You could easily test it out using a test database and Profiler, but why risk it?

    USE DatabaseName;

    GO

    EXEC sys.sp_rename

    @objname = N'SchemaName.TableName.ExistingPrimaryKeyName',

    @newname = 'NewPrimaryKeyName';

    GO

    It is only a metadata change so will happen with a simple schema lock and update, should be sub-second provided there are no schema-bound objects referencing the table that would prevent the rename and nothing else schema-related happening in the DB involving the table that would block it for a time. The real impact analysis is if someone referenced the PK in a table hint, e.g.

    SELECT Something FROM SchemaName.TableName WITH (INDEX(ExistingPrimaryKeyName))

    Would yield this after the rename:

    Msg 308, Level 16, State 1, Line 1

    Index 'ExistingPrimaryKeyName' on table 'SchemaName.TableName' (specified in the FROM clause) does not exist.

    I am not 100% sure about this, but I think all plans that reference the table would also be invalidated by the schema change so any statements referring to the table would be recompiled the time of their next execution, which for an oft-used table on a busy system could amount to a lot of CPU activity.

    Edit: PS watch out for Replication impacts too

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • August 23, 2012 at 8:12 pm

    #1529518

    Great reply, thanks !

    We are not using replication, and I ran a string search in all the stored procedures, so I think I'm good. This is actually correcting a mistake, and should fix a problem we were having. It took about 1 second.

  • August 23, 2012 at 8:33 pm

    #1529520

    Excellent, you're welcome!

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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