sql server - Adding a unique number for multiple same records in sql -

i have 1 big table named pub column ndc has multiple records of them occurs frequently. first want create sequence number distinct ndc. example in (1) original in (2) distinct ndc.

(1) ndc: b c d a c v b (2) ndc:  b c d v 

sequence number distinct ndc in example (0,1,2,3,4)

after want create new column represent original ndc column numbers. each of ndcs presented unique number. looking @ (1) ndc, needed column

newcolumn  0 1 2 3 0 0 2 4 1 

for sure while doing calling whole table. don't want insert each record alone cause distinct ndc number large.

in summary instead of having these strings in ndc column want have numbers same ndcs have same unique number in whole table.

try this:-

  ;with cte   (select distinct ndc yourtable   ),cte2    (select ndc,           rn =row_number() on (order ndc ) - 1     cte   )    update t    set t.newcolumn = c.rn    yourtable t    inner join cte2 c    on t.ndc = c.ndc; 

sql fiddle