How to generate RowID in Sql server 2000 without using identity column

Let me frame my question ....

I have say

Name

A
B
C
A
D
B

What I want is

ID   Name
1    A
2    B
3    C
4    A
5    D
6    B

If I w开发者_高级运维rite SELECT name, (SELECT COUNT(*) FROM @t AS i2 WHERE i2.Name <= i1.Name) As rn FROM @t AS i1

it will work fine if all the names are distinct/unique...What if they are not(as in this example)

Even NEWID() does not make the trick as it varies overtime?

I am using sql server 2000...

Please help

---

Here are 2 ways of solving it

1.

DECLARE @t TABLE ([ID] [int] IDENTITY(1,1), name CHAR)
INSERT @t VALUES ('b')
INSERT @t VALUES ('a')
INSERT @t VALUES ('c')
INSERT @t VALUES ('b')

SELECT * FROM @t

2.

DECLARE @t2 TABLE (name CHAR)
INSERT @t2 (name) VALUES ('b')
INSERT @t2 (name) VALUES ('a')
INSERT @t2 (name) VALUES ('c')
INSERT @t2 (name) VALUES ('b')

SELECT ID = ROW_NUMBER() OVER (ORDER BY b), name 
FROM (SELECT name, null b FROM @t2) temp