SQL Server table running out of int values on primary key

Question

I have a VERY big table (let's say the table name is ASTable) thats need to be handled in 2 ways:

• The primary key (ASTableID) is running out of int values and needs to be changed to bigint
• The (ASTable) then need to be moved to an archive server

I would like to share my plan of action for discussion please.

High level plan of action:

1. Stop the application using the database
2. Rename the table (ASTable) with sp_rename to ASTable_Old
3. Create a new table with name that will be the production table: ASTable with ASTableID bigint
4. Create ASTable foreign keys with name ending with _New
5. Create ASTable indexes with name ending with _New
6. Alter all stored procedures to change references to ASTableID from int to bigint
7. Start application
8. Move ASTable_Old to archive server

Some stats regarding the ASTable:

• Rowcount: 1 791 184 696 - 1.7 billion rows
• Data Space Used (KB): 210 547 352 - that is 210 GB
• Index Space Used (KB): 420 563 976 - that is 420 GB

I am running out of time to handle this issue and any comments would be very welcome.

question from:https://stackoverflow.com/questions/65897327/sql-server-table-running-out-of-int-values-on-primary-key

Answer 1

Use the power of schemas. This way your naming of columns and keys separate easily.

The power lies in the CREATE SCHEMA command. Once the archive (schema or shorter "arch") is created the option is to move the table with ALTER SCHEMA. I have never tested this approach on such a big table. I am not sure if this recreates the table or just updates the schema_id in the system tables.

Dummy code:

CREATE TABLE dbo.Test
(
  testcolumn  int   NOT NULL
)
;

INSERT INTO dbo.Test
VALUES (100000)
;

ALTER SCHEMA conf
TRANSFER dbo.Test
;

(*) In my database the schema "conf" already exists.

Using schemas allows to re-use all existing columns. Therefore apart from the change of ID creation from int to bigint all existing code and references keeps working.

On another note for the archive: Drop all your foreign key referencing on the archive table once moved to the new schema.

Additionally there is the option to move the table in whole to a read only file group to prevent it from being manipulated later. However this induces a data transfer between the file groups so best done all the work is finished and downtime can be scheduled.