SQL to show 1 for unique and 0 for reapeat

using row_number():

select 
    col
  , [first] = case when row_number() over (partition by col order by (select 1)) > 1 then 0 else 1 end
from t

rextester demo: http://rextester.com/FWA89661

returns:

+---------------+-------+
|      col      | first |
+---------------+-------+
| 6573411981080 |     1 |
| 6573411981080 |     0 |
| 6573411981080 |     0 |
| 6573411981080 |     0 |
| 6573455300000 |     1 |
| 6573455300000 |     0 |
| 6573455300000 |     0 |
| 6573455300000 |     0 |
+---------------+-------+

Use window functions. In your case, you seem to want the first row and mark that, so row_number() looks like the solution:

select t.*,
       (case when row_number() over (partition by number order by ?) = 1
             then 1 else 0 end
        end) as flag
from t;

The ? is for the column that specifies the ordering (which is first). If you want just one row but don't care which, then you can use order by number or order by (select null).

UNIQUE is a SQL keyword (think "unique index"), so it is a bad name for a column. That is why I changed to the generic flag, although you might prefer first_row_flag or something like that.

SELECT
 [number],
 case when rown = 1 then 1 else 0 end as [unique]
FROM
(
 SELECT
  [number], row_number() OVER(partition by [number] order by [number]) as rown 
 FROM 
  t
) a

This doesn't strictly have to be done using a subquery but it's unlikely to make any difference to the overall performance, so it's arranged like this to help you see what is going on. If you run just the inner subquery in isolation you'll see that the most important work is done by row_number; essentially the data is partitioned into buckets based on the value of [number] something like a group by, but it doesn't suppress repeated values. Within the partition each occurrence of [number] is numbered with an incrementing counter. When a different value of [number] is encountered the numbering restarts from 1. The order by clause is just there because sql server demands you have one, and we don't know anything else about your table but if there's something else about your data where one of these occurrences would be more ideal to single out to be labelled with [unique]=1, try and find a way to make it so that row is sorted into position 1; a typical use of this pattern is "latest record" in which case the order by part would be [datecolumn] DESC

Once you have an increment of counter per number that resets itself, all we need to do is use a standard case / else statement to make it a 1 when it's 1 otherwise 0 to match your result desired

select  t.Number,case when t.num=1 then t.num else 0 end [Unique] from(
select Number,row_number() over (partition by number order by number) num from MyTbl)t
order by t.Number