Make a field monotonic across all rows

I have table in my sql server database which I want to convert to PK column

To do that I want t开发者_Go百科o change value of each row in this column to 1,2,3 ...

Could You write T-Sql query for that task ?

Thanks for help

begin state:

Id | Name |
----------
1  | One  |
2  | Two  |
2  | Three|
x  | xxx  |

result:

Id | Name |
----------
1  | One  |
2  | Two  |
3  | Three|
4  | xxx  |

;with cte as
(
SELECT Id, ROW_NUMBER() over (order by Id) as rn
from YourTable
)
UPDATE cte SET Id = rn

you can also do it with name if you dont have the id!

;with cte as
(
SELECT Id, ROW_NUMBER() over (order by name) as rn
from YourTable
)
UPDATE cte SET Id = rn