How can I change the Data Type of a Primary Key Column in SQL Server?

SQL Server does not allow direct modification of a column’s data type if it is part of a primary key. To change the data type, you must first drop the primary key. Then, change the data type of the column and recreate the primary key constraint.

However, if Change Tracking is enabled on the table, you must temporarily disable it to drop or modify the primary key. This is done because SQL Server does not allow any schema modifications while Change Tracking is active.

Note: Disabling Change Tracking for a table immediately removes all tracking data. Any changes made while it is disabled are not logged, even when Change Tracking is re-enabled. Ensure that no write operations are performed on the table while you are modifying the primary key, as these changes are not captured and may result in data loss.

Perform the following steps to update the data type of a primary key column:

Note: Ensure that the existing data in the column is compatible with the new data type to avoid any data conversion errors.

1. Pause the data ingestion for the object.

   1. In the Pipeline List View, select the Pipeline you want to modify to open it in the Detailed View.

   2. In the Objects list, check the ingestion position for the object to verify that all data from your SQL Server database was ingested.
      

   3. Click the More () icon corresponding to the object, and then select Pause object.
      

2. Change the primary key data type.

   1. Connect to your SQL Server database as an admin user with any SQL client tool, such as sqlcmd.

   2. Run the following commands:

      -- Disable change tracking at the table level 
      ALTER  TABLE  <schema_name>.<table_name>  DISABLE CHANGE_TRACKING; 
      
      -- Drop the primary key constraint 
      ALTER  TABLE  <schema_name>.<table_name>  DROP  CONSTRAINT  <constraint_name>; 
              
      -- Modify the column data type 
      ALTER  TABLE  <schema_name>.<table_name>  ALTER  COLUMN  <column_name>  <DATA_TYPE>; 
              
      -- Recreate the primary key constraint 
      ALTER  TABLE  <schema_name>.<table_name>  ADD  CONSTRAINT  <constraint_name>  PRIMARY  KEY  (<column_name>); 
              
      -- Re-enable change tracking 
      ALTER  TABLE  <schema_name>.<table_name>  ENABLE CHANGE_TRACKING; 
              
      -- Verify that Change Tracking is re-enabled 
      SELECT CHANGE_TRACKING_CURRENT_VERSION()  AS Current_Change_Tracking_Version;
      

      Note: Replace the placeholder values in the commands above with your own. For example, <schema_name>.<table_name> with dbo.employees.

   Once Change Tracking is re-enabled, SQL Server tracks changes made only after that point. Any changes that occurred while Change Tracking was disabled are not captured. If you notice missing or incomplete data in your Destination, restart the historical load for the Source object to restore consistency.

3. Resume the data ingestion for the object.

   1. In the Pipeline List View, select the Pipeline you want to modify to open it in the Detailed View.

   2. In the Objects list, click the More ( ) icon corresponding to the object, and then select Resume object.

      

See Also

• Change Tracking

Revision History