选择不同的 * 查询有多昂贵 [英] How expensive is select distinct * query

问题描述

在 sql server 2012 中，我有一个包含超过 2500 万行重复的表.该表没有唯一索引.它只有一个非聚集索引.我想消除重复项，所以，我在考虑以下

In sql server 2012, I have got a table with more than 25 million rows with duplicates. The table doesn't have unique index. It only has a non-clustered index. I wanted to eliminate duplicates and so, I m thinking of the below

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

我想知道 select distinct * 查询的成本有多高.如果我上面的程序很贵，我想知道是否有另一种替代方法.

I wanted to know how expensive is select distinct * query. If my procedure above is very expensive, I wanted to know if there is another alternate way.

推荐答案

我不知道它有多贵，但另一种方法是创建另一个带有主键的表，将所有数据插入那里并静默拒绝如此处所述的重复

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

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

基本上，使用 IGNORE_DUP_KEY

basically, using IGNORE_DUP_KEY

这篇关于选择不同的 * 查询有多昂贵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！