Conversion from 'uniqueidentifier' to 'int' is not supported on the connected database server

Question

I'm about to migrate my database from using old membership to the one included in mvc4, which uses int instead of guid.

When changing the type I get following error:

Conversion from int to uniqueidentifier is not supported on the connected database server.

How can I change UserId to int via SQL Server Management Studio?

Answer 1

You have to add a new column ( ALTER TABLE ADD [NewId] INTEGER ) then run the following to populate the new id column :

WITH Cte
AS
(
    SELECT *
    , ROW_NUMBER() OVER(ORDER BY [Your GUID Column Here] DESC) AS RowNumber
    FROM YourTable
)
UPDATE Cte
SET [NewId]= RowNumber
GO

There you have a new ID column that you can use a clustered primary key