Changing the data type of a field used in the primary key of a table is a task not to be entered into lightly!

First you would need to drop the primary key constraint, then change the data type, then add a new primary key constraint. This will surely result in an outage and probably lots of other potential gotchas, so you'll need to think through all those implications.

Alter Table my_table Drop Constraint pk_my_table;
Alter Table my_table Alter Column ID varchar(11) Not Null;
Alter Table my_table Add Constraint pk_my_table Primary Key Clustered (ID);

(Note, this assumes that you don't have IDENTITY specified for the column. If you do then see http://stackoverflow.com/questions/8230257/remove-identity-from-a-column-in-a-table).

An alternative which may meet your needs is to add a computed column to the table (or to a view over the table) that contains the same value as the ID field but converted to a character type. This is less likely to interfere with other things, but still might cause trouble if there are SELECT * FROM my_table statements being used in applications.

Alter Table my_table Add ID_string As Convert(varchar(11), ID);

Changing the data type of a field used in the primary key of a table is a task not to be entered into lightly!

First you would need to drop the primary key constraint, then change the data type, then add a new primary key constraint. This will surely result in an outage and probably lots of other potential gotchas, so you'll need to think through all those implications.

Alter Table my_table Drop Constraint pk_my_table;
Alter Table my_table Alter Column ID varchar(11) Not Null;
Alter Table my_table Add Constraint pk_my_table Primary Key Clustered (ID);

(Note, this assumes that you don't have IDENTITY specified for the column. If you do then see https://stackoverflow.com/questions/8230257/remove-identity-from-a-column-in-a-table).

An alternative which may meet your needs is to add a computed column to the table (or to a view over the table) that contains the same value as the ID field but converted to a character type. This is less likely to interfere with other things, but still might cause trouble if there are SELECT * FROM my_table statements being used in applications.

Alter Table my_table Add ID_string As Convert(varchar(11), ID);

Changing the data type of a field used in the primary key of a table is a task not to be entered into lightly!

First you would need to drop the primary key constraint, then change the data type, then add a new primary key constraint. This will surely result in an outage and probably lots of other potential gotchas, so you'll need to think through all those implications.

Alter Table my_table Drop Constraint pk_my_table;
Alter Table my_table Alter Column ID varchar(11) Not Null;
Alter Table my_table Add Constraint pk_my_table Primary Key Clustered (ID);

An alternative which may meet your needs is to add a computed column to the table (or to a view over the table) that contains the same value as the ID field but converted to a character type. This is less likely to interfere with other things, but still might cause trouble if there are SELECT * FROM my_table statements being used in applications.

Alter Table my_table Add ID_string As Convert(varchar(11), ID);

Changing the data type of a field used in the primary key of a table is a task not to be entered into lightly!

First you would need to drop the primary key constraint, then change the data type, then add a new primary key constraint. This will surely result in an outage and probably lots of other potential gotchas, so you'll need to think through all those implications.

Alter Table my_table Drop Constraint pk_my_table;
Alter Table my_table Alter Column ID varchar(11) Not Null;
Alter Table my_table Add Constraint pk_my_table Primary Key Clustered (ID);

(Note, this assumes that you don't have IDENTITY specified for the column. If you do then see http://stackoverflow.com/questions/8230257/remove-identity-from-a-column-in-a-table).

An alternative which may meet your needs is to add a computed column to the table (or to a view over the table) that contains the same value as the ID field but converted to a character type. This is less likely to interfere with other things, but still might cause trouble if there are SELECT * FROM my_table statements being used in applications.

Alter Table my_table Add ID_string As Convert(varchar(11), ID);