問題描述

我遇到了一個讓我發瘋的問題.運行下面的查詢時，我得到的計數為 233,769

I'm running into a problem that's driving me nuts. When running the query below, I get a count of 233,769

 SELECT COUNT(distinct  Member_List_Link.UserID)  
 FROM Member_List_Link  with (nolock)   
 INNER JOIN MasterMembers with (nolock)  
     ON Member_List_Link.UserID = MasterMembers.UserID   
  WHERE MasterMembers.Active = 1 And
        Member_List_Link.GroupID = 5 AND 
        MasterMembers.ValidUsers = 1 AND 
        Member_List_Link.Status = 1

但是如果我運行相同的查詢沒有不同的關鍵字，我會得到233,748

But if I run the same query without the distinct keyword, I get a count of 233,748

 SELECT COUNT(Member_List_Link.UserID)  
 FROM Member_List_Link  with (nolock)   
 INNER JOIN MasterMembers with (nolock)
   ON Member_List_Link.UserID = MasterMembers.UserID   
 WHERE MasterMembers.Active = 1 And Member_List_Link.GroupID = 5 
  AND MasterMembers.ValidUsers = 1 AND Member_List_Link.Status = 1

為了測試，我重新創建了所有表并將它們放入臨時表中，然后再次運行查詢:

To test, I recreated all the tables and place them into temp tables and ran the queries again:

  SELECT COUNT(distinct  #Temp_Member_List_Link.UserID)  
  FROM #Temp_Member_List_Link  with (nolock)   
  INNER JOIN #Temp_MasterMembers with (nolock)
    ON #Temp_Member_List_Link.UserID = #Temp_MasterMembers.UserID   
  WHERE #Temp_MasterMembers.Active = 1 And 
        #Temp_Member_List_Link.GroupID = 5 AND 
        #Temp_MasterMembers.ValidUsers = 1 AND 
        #Temp_Member_List_Link.Status = 1

并且沒有不同的關鍵字

  SELECT COUNT(#Temp_Member_List_Link.UserID)  
  FROM #Temp_Member_List_Link  with (nolock)   
  INNER JOIN #Temp_MasterMembers with (nolock)
    ON #Temp_Member_List_Link.UserID = #Temp_MasterMembers.UserID   
  WHERE #Temp_MasterMembers.Active = 1 And 
        #Temp_Member_List_Link.GroupID = 5 AND 
        #Temp_MasterMembers.ValidUsers = 1 AND 
        #Temp_Member_List_Link.Status = 1

順便說一下，我通過簡單地運行 (select * from Member_List_Link into #temp...) 重新創建了臨時表

On a side note, I recreated the temp tables by simply running (select * from Member_List_Link into #temp...)

現在，當我使用這些臨時表檢查 COUNT(column) 與 COUNT(distinct column) 之間的差異時，我什么也沒看到！

And now when I check to see the difference between COUNT(column) vs. COUNT(distinct column) with these temp tables, I don't see any!

那么為什么與原始表格存在差異?

So why is there a discrepancy with the original tables?

我運行的是 SQL Server 2008(開發版).

I'm running SQL Server 2008 (Dev Edition).

更新 - 包括統計資料

UPDATE - Including statistics profile

PhysicalOp 列僅用于第一個查詢(無不同)

PhysicalOp column only for the first query (without distinct)

NULL
Compute Scalar
Stream Aggregate
Clustered Index Seek

PhysicalOp 列僅用于第一個查詢(具有不同的)

PhysicalOp column only for the first query (with distinct)

NULL
Compute Scalar
Stream Aggregate
Parallelism
Stream Aggregate
Hash Match
Hash Match
Bitmap
Parallelism
Index Seek
Parallelism
Clustered Index Scan

第一個查詢的行數和執行數(沒有不同的)

Rows and Executes for the 1st query (without distinct)

1   1
0   0
1   1
1   1

第二個查詢的行數和執行數(具有不同的)

Rows and Executes for the 2nd query (with distinct)

Rows    Executes
1   1
0   0
1   1
16  1
16  16
233767  16
233767  16
281901  16
281901  16
281901  16
234787  16
234787  16

將 OPTION(MAXDOP 1) 添加到第二個查詢(具有不同的)

Adding OPTION(MAXDOP 1) to the 2nd query (with distinct)

Rows Executes

1           1
0           0
1           1
233767          1
233767          1
281901          1
548396          1

以及由此產生的 PhysicalOp

And the resulting PhysicalOp

NULL
Compute Scalar
Stream Aggregate
Hash Match
Hash Match
Index Seek
Clustered Index Scan

推薦答案

FROM http://msdn.microsoft.com/en-us/library/ms187373.aspxNOLOCK 相當于 READUNCOMMITTED.有關詳細信息，請參閱本主題后面的 READUNCOMMITTED.

FROM http://msdn.microsoft.com/en-us/library/ms187373.aspx NOLOCK Is equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED later in this topic.

READUNCOMMITED 將讀取行兩次，如果它們是事務的主題 - 因為在事務處理過程中，前滾行和回滾行都存在于數據庫中.

READUNCOMMITED will read rows twice if they are the subject of a transation- since both the roll foward and roll back rows exist within the database when the transaction is IN process.

默認情況下所有查詢都是讀提交的，不包括未提交的行

By default all queries are read committed which excludes uncommitted rows

當您插入臨時表時，選擇只會給您提交的行 - 我相信這涵蓋了您試圖解釋的所有癥狀

When you insert into a temp table the select will give you only committed rows - I believe this covers all the symptoms you are trying to explain

這篇關于SQL Server 2008 中的 COUNT (DISTINCT column_name) 與 COUNT (column_name) 的差異?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！