Create a new column and generate numbers in that column using TSQL

I have a table as below:

employee_name employee_address
RON           23-B, TORONTO
PETER         15-C, NY
TED           23-C, LONDON
RON           23-B, TORONTO

I have to add a new column to this table as follows:

employee_name employee_address   employee_no
RON           23-B, TORONTO      1
PETER         15-C, NY           2
TED           23-C, LONDON       3
RON           23-B, TOR开发者_JAVA技巧ONTO      1

Basically I have to assign unique numbers if the employee_name and employee_address column is different. If these two columns are same then assign same number as shown in above table. I have to do this using TSQL. Can anyone please help?

Thanks.

---

To answer the question asked

/*To add the new column*/
ALTER TABLE emps ADD employee_no INT NULL

/*To populate it*/
;WITH T
     AS (SELECT employee_no,
                dense_rank() OVER (ORDER BY employee_name, employee_address) AS
                new_employee_no
         FROM   emps)
UPDATE T
SET    employee_no = new_employee_no   

But what is the use case for this? If you only have those 3 columns just delete the dupes and add an identity column.

Even if you do have additional columns not shown in the question that would mean that the rows are not exact duplicates you would still be left with a denormalised table that should very probably be re factored into two.