0

I need to change the table 's field from Numeric(5,10) to int.

This field is a primary keys and related to more than 20 other Tables (some tables has more than millions records).

Can someone know the easiest and safest way to change the field type?

I tried to change it form SQL Server Studio management but my concern the table will be dropped and re-created again and that slows the process and even when I tried it was giving timeout error , can someone has an experience advice on the best way ... Regards

Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
JPNN
  • 383
  • 1
  • 7
  • 23
  • take a look at this http://dba.stackexchange.com/questions/82233/update-datatype-of-primary-key-without-deleting-the-table-sql-server – user1666620 Feb 29 '16 at 16:12
  • Might also want to look at this if you need to increase the SSMS timeout: http://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studio – Bacon Bits Feb 29 '16 at 16:14

1 Answers1

0

I would proceed as follows:

  1. Drop all FKs referencing the current PK column
  2. Create a new int column that is nullable
  3. Populate the new column for all rows, so it is unique
  4. Make the new column non-null
  5. Drop the existing PK and column
  6. (Optional) rename the new column
  7. Add the new PK
  8. For each FK you dropped
    • Perform a similar transform to switch to an int column
    • Create the new FK
  9. Update all SProcs, UDFs, Triggers, ...
  10. Update any applications using the table
  11. Update any reporting, SSIS, ... using the table

(Ie. potentially this is a lot of work: hence legacy databases tend to have things like this left in place.)

All of this could be scripted, which would certainly minimise downtime.

NB. Doing this is a great time to work with a DBA if you have one. They are likely to be interested in ensuring any such change is as smooth and as quick as possible (eg. allocating extra temporary space to ensure adding a new clustered PK doesn't cause problems).

Richard
  • 106,783
  • 21
  • 203
  • 265