CTE top - порядок предложений не всегда работает

Question

У меня есть следующий оператор T-SQL:

;WITH DataSource ([ColumnA]) AS
(
    SELECT TOP 100 [ColumnA] 
    FROM [dbo].[TEST] 
    WHERE [ColumnB] = 40 
    ORDER BY [ColumnC] DESC
)
SELECT [ColumnA]
      ,COUNT([ColumnA])
FROM DataSource
GROUP BY [ColumnA]

Это просто не получение того же результата. Я не могу понять путь, но некоторое время оператор T-SQL в CTE не возвращает правильные значения, потому что из-за этого разные результаты дают не каждый раз, но иногда (если быть более точным, я получаю три разных результата),

Если я извиняюсь только следующее утверждение:

SELECT TOP 100 [ColumnA] 
FROM [dbo].[TEST] 
WHERE [ColumnB] = 40 
ORDER BY [ColumnC] DESC

Я получаю одинаковые результаты каждый раз. И если я использую производную таблицу, нет проблем:

SELECT [ColumnA]
      ,COUNT([ColumnA])
FROM
(
    SELECT TOP 100 [ColumnA] 
    FROM [dbo].[TEST] 
    WHERE [ColumnB] = 40 
    ORDER BY [ColumnC] DESC

) DataSource
GROUP BY [ColumnA]

Может кто-нибудь объяснить, почему CTE не возвращает правильные результаты каждый раз?

Answer 1

Как Martin Smith указал в своем комментарии:

ColumnC уникален? Если нет, вам нужно будет добавить гарантированный уникальный столбец (столбцы) в ORDER BY, чтобы он действовал как прерыватель связей для обеспечения детерминированных результатов.

В этом нет ничего плохого CTE сам по себе - проблема в том, что я не сортирую по unique колонка.

Answer 2

ORDER BY не допускается внутри конструкции CTE. Пожалуйста, смотрите раздел замечаний здесь: MSDN. Обеспечьте заказ как часть CTE_QUERY_DEFINITION. Я удивлен, что SQL-сервер даже позволил вам запустить его.