COUNT (DISTINCT column_name) Несоответствие или COUNT (имя_столбца) в SQL Server 2008?

У меня проблема, которая сводит меня с ума. При выполнении запроса ниже я получаю счет 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

 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

Чтобы протестировать, я воссоздал все таблицы и поместил их в временные таблицы и снова запустил запросы:

  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...)

И теперь, когда я проверяю разницу между COUNT (столбец) и COUNT (отдельный столбец) с этими временными таблицами, я не вижу никаких!

Итак, почему существует расхождение с исходными таблицами?

Я запускаю SQL Server 2008 (Dev Edition).

UPDATE - включение профиля статистики

Столбец PhysicalOp только для первого запроса (без четкого)

NULL
Compute Scalar
Stream Aggregate
Clustered Index Seek

Столбец PhysicalOp только для первого запроса (с отличным)

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

Строки и выполнение для первого запроса (без четких)

Строки и выполнение для второго запроса (с четким)

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) во второй запрос (с четким)

Rows Executes

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

И результат PhysicalOp

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

Ответ 1

FROM http://msdn.microsoft.com/en-us/library/ms187373.aspx NOLOCK эквивалентен READUNCOMMITTED. Для получения дополнительной информации см. Раздел READUNCOMMITT позже в этом разделе.

READUNCOMMITED будет считывать строки дважды, если они являются предметом трансации, поскольку в базе данных существуют как ряды строк, так и откатные строки, когда транзакция является процессом IN.

По умолчанию все запросы считываются, что исключает незафиксированные строки

При вставке в временную таблицу выбор даст вам только фиксированные строки - я считаю, что это охватывает все симптомы, которые вы пытаетесь объяснить

Ответ 2

Я думаю, что у меня есть ответ на ваш вопрос, но сначала скажите, является ли userid основным ключом в вашей исходной таблице?

если да, то запрос CTAS для создания таблицы temp не будет копировать первичный ключ исходной таблицы, он только скопирует ограничение NOT NULL, которое не является частью первичного ключа.??

теперь то, что произошло в вашей исходной таблице, имело первичный ключ, поэтому count (distinct column_name) не включает кортежи с нулевыми записями, а во время создания временных таблиц первичный ключ не копируется и, следовательно, ограничение NOT NULL не попадает в таблицу temp!

это ясно для вас?

Ответ 3

Трудно воспроизвести это поведение, так что я пробиваю в темноте:

Оператор WITH (NOLOCK) позволяет считывать незафиксированные данные. Я предполагаю, что вы добавили, что не блокируете что-либо для своих пользователей? Если вы удалите их и выпустите

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Перед выполнением запроса вы должны получить более надежные результаты. Но тогда таблицы могут получать блокировки во время выполнения запроса.

Если это не сработает, я предполагаю, что DISTINCT использует индекс для оптимизации. Проверьте запрос и перестройте индексы по мере необходимости. Может быть источником вашей проблемы.

Ответ 4

Какой результат вы получите с помощью

SELECT count(*) FROM (
    SELECT 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
) as m

И С:

SELECT count(*) FROM (
    SELECT distinct  Member_List_Link.UserID
    FROM Member_List_Link  
    INNER JOIN MasterMembers
      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
) as m

Ответ 5

Луч, пожалуйста, попробуйте следующее

SELECT COUNT(*)
FROM 
(
    SELECT Member_List_Link.UserID, ROW_NUMBER() OVER (PARTITION BY Member_List_Link.UserID ORDER BY (SELECT NULL)) N
    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
) A
WHERE N = 1

Ответ 6

когда вы используете счетчик с отдельным столбцом, он не учитывает столбцы со значениями null.

создать таблицу #tmp (name char (4) null)

вставить в значения #tmp (null)

вставить в значения #tmp ( "AAA" )

Query: - 1 > выберите count (*) из #tmp 2 > go

1 > выберите count (отличное имя) из #tmp 2 > go

1 > выберите отличное имя из #tmp 2 > go имя

NULL

AAA

но он работает в производной таблице

1 > выберите count (*) from (выберите другое имя из #tmp) a

2 > go

Примечание. - Я протестировал его в Sybase