TSQL DateTime 到 DateKey Int

Question

在 Scaling Up Your Data Warehouse with SQL Server 2008 R2 ，作者建议使用格式为 YYYYMMDD 的整数日期键作为事实表上的聚集索引，以帮助优化查询速度。

将关键日期字段转换为日期键的最佳方法是什么？我觉得以下方法可行，但有点草率:

select Replace(CONVERT(varchar,GETDATE(),102),'.','')

显然，我没有使用 getdate，而是使用表中的一个日期列，它将在我的聚合中使用。

首先，您建议如何进行这种转换？我的想法可以接受吗？

其次，有没有人使用日期键作为聚集索引取得了很大的成功？

Answer 1

ISO long (112) 可以解决问题:

SELECT CONVERT(INT, CONVERT(VARCHAR(8), GETDATE(), 112))

出于某种原因，使用 ISO 112 将 getdate() 直接转换为 int 会给出 41008，但通过 VARCHAR 似乎有效 - 如果我想到更快的转换，我会更新。

编辑 :关于仅 int 与 varchar 的辩论，这是我的发现(可在我的测试设备和生产服务器上重复)Varchar 方法使用更少的 CPU 时间进行 50 万次转换，但总体上慢了一点 - 可以忽略不计，除非您处理数十亿行

编辑 2 :修改测试用例以清除缓存和不同的日期

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
WITH    RawDates ( [Date] )
          AS ( SELECT TOP 500000
                        DATEADD(DAY, N, GETDATE())
               FROM     TALLY
             )
    SELECT  YEAR([Date]) * 10000 + MONTH([Date]) * 100 + DAY([Date])
    FROM    RawDates
SET STATISTICS TIME OFF 

(500000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 255ms.    
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
WITH    RawDates ( [Date] )
          AS ( SELECT TOP 500000
                        DATEADD(DAY, N, GETDATE())
               FROM     TALLY
             )
    SELECT  CONVERT(INT, CONVERT(VARCHAR(8), [Date], 112))
    FROM    RawDates
SET STATISTICS TIME OFF 

(500000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 266 ms,  elapsed time = 602ms