假设存在如下表：

CREATE TABLE Products (
ProductID int,
ProductName nvarchar (40),
Unit char(2),
UnitPrice money
)

表中数据如下：
insert Products
select 1,产品1,包,   9.9 union all
select 2,产品2,箱,   20 union all
select 2,产品2,箱,   20 union all
select 3,产品3,件,   5 union all
select 4,产品4,桶, 100 union all
select 4,产品4,桶, 100 union all
select 4,产品4,桶, 100 

1、在表中增加自增长字段
alter table products add p_id int IDENTITY(1,1)
delete from products where p_id not in (select max(p_id) from products group by ProductID)
alter table products drop column p_id

2、 利用临时表删除表中数据
select distinct * into #temp from products
truncate table products
insert into products select * from #temp
drop table #temp