Datepart(year, ...) vs. Year(...) [英] Datepart(year, ...) vs. Year(...)

问题描述

在以下情况下使用其中一种的优势是什么:

What are the advantages of using one over the other in the following:

DATEPART(YEAR, GETDATE())

相反:

YEAR(GETDATE())

是否有性能差异?如果是这样，哪个最快?

Is there is a performance difference? If so, which one is the fastest?

推荐答案

实际上 - 使用 YEAR(..) 更适合我，因为它被认为是一个确定性函数，所以如果我在计算列定义中使用它

Actually - using YEAR(..) is preferably for me, since it's considered a deterministic function, so if I use this in a computed column definition

ALTER TABLE dbo.MyTable
ADD YearOfDate AS YEAR(SomeDateColumn)

我可以使此列持久化(并将其存储到表中):

I can make this column persisted (and store it into the table):

ALTER TABLE dbo.MyTable
ADD YearOfDate AS YEAR(SomeDateColumn) PERSISTED

这对于DATEPART(YEAR, SomeDateColumn)不起作用(不要问我为什么 - 只是启发式地注意到这一点).

This does not work for DATEPART(YEAR, SomeDateColumn) (don't ask me why - just noticed this heuristically).

同样适用于 MONTH(SomeDate) 与 DATEPART(MONTH, SomeDate).

如果您有需要根据日期的月份和年份(例如 SalesDate 或其他内容)选择的表格，则将月份和年份作为持久计算 列(并为它们编制索引)可以极大地提升性能.

If you have tables that you need to select from based on the month and year of a date (like SalesDate or something), then having month and years as persisted computed columns (and indexing them) can be a huge performance boost.

这篇关于Datepart(year, ...) vs. Year(...)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！