Q:

SQL Server Change Primary Key Data Type

I am working on SQL Server 2012:
I have a table with a primary key column as INT. I need to change this to a GUID.
Do I alter the table and remove int column as primary key?
Add the GUID column and set it as Primary and drop the old INT column?
Thank you.

A:

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

A:

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.

