DATEADD递归生成的日期插入临时表后为何乱序且无法正常排序？

预期生成指定起止时间范围内按升序排列的小时级日期列表并插入临时表，但实际记录在2023年3月8日19点后跳至2023年8月14日12点，缺失的日期出现在表末尾，后续排序也无法修正。对应的SQL代码如下：

DECLARE @StartDateTime DATETIME = '2023-02-17 00:00:00'
DECLARE @EndDateTime DATETIME = '2023-12-31 00:00:00';

CREATE TABLE #NewDates (ExpandedDateTime DATETIME);

WITH ExpDates AS
    (
    SELECT @StartDateTime AS ExpandedDateTime
    UNION ALL
    SELECT  DATEADD(HOUR, 1, ExpandedDateTime)
    FROM    ExpDates
    WHERE   DATEADD(HOUR, 1, ExpandedDateTime) <= @EndDateTime
    )
INSERT INTO #NewDates
SELECT  ExpandedDateTime
FROM    ExpDates
OPTION (MAXRECURSION 0)

SELECT * FROM #NewDates

核心原因 #

问题本质是堆表的无序存储特性：你创建的临时表#NewDates没有定义主键或聚集索引，属于无索引的堆表。递归CTE虽然逻辑上按小时递增生成时间，但SQL Server向堆表插入数据时，会优先选择存储空间中最空闲的位置写入，而非遵循查询结果的顺序，这就导致后续查询时数据返回顺序被打乱，出现时间跳段、缺失数据后置的现象。

DATETIME类型的精度限制（仅精确到3.33毫秒）不会直接引发这种大范围的顺序异常，堆表的无序存储才是直接诱因。

解决办法 #

可以通过以下方式彻底解决问题：

• 给临时表添加聚集索引：
  创建表时指定主键（自动生成聚集索引），强制数据按时间顺序存储：

  CREATE TABLE #NewDates (ExpandedDateTime DATETIME PRIMARY KEY CLUSTERED);
  

• 插入时显式排序：
  即使使用堆表，插入阶段通过ORDER BY强制按时间顺序写入，避免顺序混乱：

  INSERT INTO #NewDates
  SELECT  ExpandedDateTime
  FROM    ExpDates
  ORDER BY ExpandedDateTime
  OPTION (MAXRECURSION 0)
  

• 查询时必须显式排序：
  SQL Server的核心规则：无ORDER BY的查询，返回顺序不做任何保证。因此无论表结构如何，查询时都要加上排序语句：

  SELECT * FROM #NewDates ORDER BY ExpandedDateTime
  

内容的提问来源于stack exchange，提问作者Galen Brown