How to alter column from int to Uniqueidentifier in SQL Server 2008

Question

I have a table with column "ID" as datatype int.

CREATE TABLE [dbo].[STUDENT]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DOB] [datetime] NOT NULL ,
    [NAME] [nvarchar](250) NULL,
    [CLASS] [nvarchar](50) NULL,

    CONSTRAINT [PK_STUDENT_DATA] 
         PRIMARY KEY CLUSTERED ([ID] ASC)
                     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                           ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
)  ON [PRIMARY]

Now I want to change datatype from int to uniqueidentifier. I have deleted constraint PK_STUDENT_DATA. My ALTER statement is

ALTER TABLE dbo.STUDENT COLUMN ID uniqueidentifier

I am getting error:

Identity column 'HISTORY_ID' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

Answer 1

The identity column is noncompatible with UniqueIdentifier data type Column.So you need to drop the column and create the new column with UniqueIdentifier data type.Since IDENTITY cannot be used with GUID.Use NEWID instead.

Perform Following Steps:

1. Initially, you need to remove the constraint from table

DROP Index [PK_STUDENT_DATA]  ON STUDENT

2. After that

ALTER TABLE STUDENT drop COLUMN ID 

3. And finally

ALTER TABLE STUDENT 
ADD [Id] UNIQUEIDENTIFIER NOT NULL 
PRIMARY KEY NONCLUSTERED DEFAULT NEWID()