How expensive it is to choose a separate * request

Question

In sql server 2012, I have a table with over 25 million rows with duplicates. The table does not have a unique index. It has only a non-clustered index. I wanted to eliminate duplicates and so, I think about below

select distinct * into #temp_table from primary_table
truncate primary_table
select * into primary_table from #temp_table

I wanted to know how expensive it is to choose a separate * request. If my procedure is higher, it is very expensive, I would like to know if there is another alternative way.

Answer 1

I don’t know how expensive it is, but an alternative way is to create another table with a primary key, insert all the data there and silently reject the duplicates, as indicated here.

http://sqlblog.com/blogs/paul_white/archive/2013/02/01/a-creative-use-of-ignore-dup-key.aspx

, IGNORE_DUP_KEY