• Sorry, don't have much time but here's a quick overview of one method to consider:

    1) create another table with the new bigint data type to copy the existing data to [btw, if you're on Enterprise Edition, and you're not using data compression, you should use at least row compression for the new table]. put the necessary indexes in place, so that time is already included in the initial load time.

    2) capture the current identity value, which you'll need later to make sure all recently inserted rows get copied to the new table

    3) copy the data in batches from the old table to the new one using the clustering key. Commit each batch immediately. Typically batch size is ~20K to 100K rows, depending on disk speed and logging capacity.

    4) after all rows are copied, verify that no identity value(s) are missing from the new table

    Then you'll need some minor down time to be sure:

    5) drop fk constraints on the current table

    5) rename the current table so no one can access it

    6) one final verification that all identity values are in the new table

    7) rename the new table to the original table name

    8) recreate the fk constraints, and be sure to explicitly WITH CHECK them so that they are trusted in SQL Server

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.