使用CTE更新临时表异常：CNID_RecordCount未按预期更新为2

DDL代码 #

IF OBJECT_ID('tempdb..#Tab2And3Pop')  is not null
DROP TABLE #Tab2And3Pop;

SELECT     CAST(115704   AS INT) AS ConflictNoticeID
     , CAST(16003573 AS INT) AS MerchantID
     , CAST(13       AS INT) AS WebUserID
     , CAST(NULL     AS bit) AS CNID_RecordCount
INTO #Tab2And3Pop
UNION SELECT 115704, 16003573, 356459, NULL
UNION SELECT 129110, 6140405 , 28    , NULL
UNION SELECT 129110, 6140405 , 251787, NULL
UNION SELECT 134799, 14868652, 30    , NULL
UNION SELECT 134799, 14868652, 251787, NULL;

--------------------------------------

;with RecordCount AS (
    select ConflictNoticeID, COUNT(*) AS CountOfCNID
    from #Tab2And3Pop
    GROUP BY ConflictNoticeID
)
update p
SET p.CNID_RecordCount = rc.CountOfCNID
--select *
from #Tab2And3Pop p
    JOIN RecordCount RC on rc.ConflictNoticeID = p.ConflictNoticeID

执行上述代码后，预期临时表中CNID_RecordCount字段会被更新为每个ConflictNoticeID对应的记录数2，但实际所有记录的该字段值均为1：

问题原因 #

CNID_RecordCount字段被定义为bit类型，SQL Server中bit类型仅能存储0或1。当你将大于1的数值（此处为2）赋值给bit字段时，SQL Server会自动将其转换为1，因此所有记录的该字段值都显示为1。

解决方法 #

将CNID_RecordCount的字段类型从bit改为int，修改后的DDL对应行如下：

SELECT     CAST(115704   AS INT) AS ConflictNoticeID
     , CAST(16003573 AS INT) AS MerchantID
     , CAST(13       AS INT) AS WebUserID
     , CAST(NULL     AS INT) AS CNID_RecordCount  -- 此处将bit改为INT
INTO #Tab2And3Pop

重新执行完整代码后，CNID_RecordCount字段会正确显示每个ConflictNoticeID对应的记录数2。

内容的提问来源于stack exchange，提问作者Jeff.Clark