最近遇到一个用distinct查询数据插入表变量但发生主键冲突的问题
表结构:
create table dis_test(eid bigint not null,
uid varchar(20) not null,
name varchar(20)
status smallint,
lasttime datetime
constraint [pk_eid] primary key nonclustered(eid asc)
) on [primary]
create clustered index idx_uid on dis_test(uid)
--eid是主键但是非聚集索引,uid为聚集索引
sql:
declare @tmp table (eid bigint primary key)
insert into @tmp
select distinct eid from dis_test (nolock) where status=1
--查询有nolock,允许脏读
出现error:
Violation of PRIMARY KEY constraint 'PK__#A3470C3__B40CC6ED0174E9CC'. Cannot insert duplicate key in object 'dbo.@tmp'.
问题1:为什么有重复数据
问题2:为什么加了distinct还会主键冲突,即distinct失效
重复数据问题
允许脏读,报错的时间点刚好数据发生更新出现了页拆分,每次页拆分会有一半数据到新页,该条数据读到两次(这一点需要了解关于数据页的知识,这里不深入)
distinct失效问题
我们看看查询的执行计划
select distinct eid from dis_test (nolock) where status=1
![--没有聚合,忽略distinct]
(https://img-blog.csdnimg.cn/2020081314443184.png#pic_center)
--换个输出字段对比
select distinct uid from dis_test (nolock) where status=1
![--做了聚合,distinct生效]
(https://img-blog.csdnimg.cn/20200813144725218.png#pic_center)
/*
**在输出字段包含pk的情况下,distinct会忽略,
所以在出现页拆分读了重复数据后,distinct并不会再做去重**
*/