sql server - Set unique value dynamically without loop using T-SQL -

i need join table on either of 2 values. can enough or in join. however, trying create table house data make easier add new records daily. want create table hold data, , keep unique value each result.

i have working example below, uses while loop , extremely slow. convert set-based operation, keep coming short.

table @t source of data. table @hh new table hold unique value (hhid).

example:

declare @t table (appid int, phone varchar(10), bcn varchar(10)); declare @hh table (bcn varchar(10), hhid int default(null));  insert @t        select 1, '1115551212','1' union select 2, '1115551212','1' union select 3, '1115551212','2' union select 4, '9995551212','2' union select 5, '8885551212','3' union select 6, '1115551212','4' union select 7, '1115551212','5' union select 8, '7775551212','1' union select 9, '7785551212','6' union select 10, '7795551212','6'  insert @hh select distinct bcn,null @t;  declare @hhid int = -1; declare @bcn varchar(10);  select top(1) @bcn =  bcn @hh hhid null; select @hhid = isnull((select max(isnull(hhid,-1)) @hh),-1); if @hhid = -1         set @hhid = 4999999; while @bcn not null begin     set @hhid += 1;      update @hh set hhid = @hhid bcn in (     select distinct t2.bcn     @hh h     join @t t on h.bcn = t.bcn     left join @t t2 on t.phone = t2.phone     h.bcn = @bcn , t.phone <> '');      set @bcn = null;     select top(1) @bcn =  bcn @hh hhid null; end  select * @hh 

edit: expected results (as returned code):

 bcn  hhid    1   5000000    2   5000000    3   5000001    4   5000000    5   5000000    6   5000002   

from output, can see records share either bcn or phone value same hhid, , not share in common others own unique id. said, code works, , need, want try replace set-based operations.

this script without loop/cursor. creates 2 indexes important speed query: t_phone_ind , t_bcn_ind. can check actual execution plan, you'll see both being used speed query.

create table #t(appid int primary key, phone varchar(10), bcn varchar(10)); create nonclustered index t_phone_ind on #t(phone,bcn); -- bcn->min(phone) create nonclustered index t_bcn_ind on #t(bcn,phone);   -- phone->min(bcn) insert #t(appid,phone,bcn)        select 1, '1115551212','1' union select 2, '1115551212','1' union select 3, '1115551212','2' union select 4, '9995551212','2' union select 5, '8885551212','3' union select 6, '1115551212','4' union select 7, '1115551212','5' union select 8, '7775551212','1' union select 9, '7785551212','6' union select 10, '7795551212','6';  ;with cte1 (     select bcn,min(phone) phone #t group bcn  ), cte2 (     select phone,min(bcn) bcn #t group phone ) select     cte1.bcn,     hhid=4999999+dense_rank() on (order cte2.bcn)     cte1     left join cte2 on         cte2.phone=cte1.phone order     cte1.bcn;  drop table #t; 

result:

+-----+---------+ | bcn |  hhid   | +-----+---------+ |   1 | 5000000 | |   2 | 5000000 | |   3 | 5000001 | |   4 | 5000000 | |   5 | 5000000 | |   6 | 5000002 | +-----+---------+