Без использования цикла:

create table #tmp (pk int identity(1,1), name varchar(30) not null, Idx int null)

insert into #tmp (name) values ('Bill')
insert into #tmp (name) values ('Bill')
insert into #tmp (name) values ('John')
insert into #tmp (name) values ('Bill')
insert into #tmp (name) values ('John')
insert into #tmp (name) values ('Bill')
insert into #tmp (name) values ('Elvis')
insert into #tmp (name) values ('Brian')
insert into #tmp (name) values ('John')
insert into #tmp (name) values ('Brian')

select * from #tmp

update t
Set Idx=(Select Count(*) from #tmp tt where tt.Name=t.Name AND tt.PK<=t.PK)
From
#tmp t

Select * from #tmp

drop table #tmp