SQL Server Change Primary Key Data Type
You can't change primary key column,unless you drop it..Any operations to change its data type will lead to below error..
The object 'XXXX' is dependent on column 'XXXX'.
Only option is to
1.Drop primary key
2.change data type
3.recreate primary key
ALTER TABLE t1
DROP CONSTRAINT PK__t1__3213E83F88CF144D;
GO
alter table t1
alter column id varchar(10) not null
alter table t1 add primary key (id)
From 2012,there is a clause called (DROP_EXISTING = ON)
which makes things simple ,by dropping the clustered index at final stage and also keeping old index available for all operations..But in your case,this clause won't work..
So i recommend
1.create new table with desired schema and indexes,with different name
2.insert data from old table to new table
3.finally at the time of switch ,insert data that got accumulated
4.Rename the table to old table name
This way you might have less downtime
You can change the date type of the primary key in three steps
Step 1 :- Drop the constraint associated with the Primary key
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Step 2 :- Alter the Primay key column to a valid primary key data type
ALTER TABLE table_name
ALTER COLUMN pk_column_name target_data_type(size) not null;
Step 3 :- Make the altered column primary key again
ALTER TABLE table_name
ADD PRIMARY KEY (pk_column_name);
PS :-
You can get the Constraint name from the error message when you try to alter the pk_column
If you already have data in the pk_column make sure the source and target data type of the column both can be used for the existing data. else another two steps would be needed to move the existing data to a temporary column and then perform the steps and bring back that data after vetting and dropping that temporary column.