Datepart(year, ...) vs. Year(...)

Question

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

DATEPART(YEAR, GETDATE()) 

As opposed to:

YEAR(GETDATE()) 

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

Answer 1

There is no difference. In the execution plan both is translated to as datepart(year,getdate()).

This is true for SQL Server 2005, 2008 and 2012.

select datepart(year, getdate()) from (select 1 x) x  select year(getdate()) from (select 1 x) x 

Execution plan.

<?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.5057.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">   <BatchSequence>     <Batch>       <Statements>         <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="1.157E-06" StatementText="select datepart(year, getdate())&#xD;&#xA;from (select 1 x) x&#xD;&#xA;&#xD;" StatementType="SELECT">           <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />           <QueryPlan DegreeOfParallelism="0" CachedPlanSize="8" CompileTime="23" CompileCPU="23" CompileMemory="64">             <RelOp AvgRowSize="11" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Constant Scan" NodeId="0" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">               <OutputList>                 <ColumnReference Column="Expr1001" />               </OutputList>               <RunTimeInformation>                 <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />               </RunTimeInformation>               <ConstantScan>                 <Values>                   <Row>                     <ScalarOperator ScalarString="datepart(year,getdate())">                       <Identifier>                         <ColumnReference Column="ConstExpr1002">                           <ScalarOperator>                             <Intrinsic FunctionName="datepart">                               <ScalarOperator>                                 <Const ConstValue="(0)" />                               </ScalarOperator>                               <ScalarOperator>                                 <Intrinsic FunctionName="getdate" />                               </ScalarOperator>                             </Intrinsic>                           </ScalarOperator>                         </ColumnReference>                       </Identifier>                     </ScalarOperator>                   </Row>                 </Values>               </ConstantScan>             </RelOp>           </QueryPlan>         </StmtSimple>       </Statements>     </Batch>     <Batch>       <Statements>         <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="2" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="1.157E-06" StatementText="select year(getdate())&#xD;&#xA;from (select 1 x) x" StatementType="SELECT">           <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />           <QueryPlan DegreeOfParallelism="0" CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="64">             <RelOp AvgRowSize="11" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Constant Scan" NodeId="0" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">               <OutputList>                 <ColumnReference Column="Expr1001" />               </OutputList>               <RunTimeInformation>                 <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />               </RunTimeInformation>               <ConstantScan>                 <Values>                   <Row>                     <ScalarOperator ScalarString="datepart(year,getdate())">                       <Identifier>                         <ColumnReference Column="ConstExpr1002">                           <ScalarOperator>                             <Intrinsic FunctionName="datepart">                               <ScalarOperator>                                 <Const ConstValue="(0)" />                               </ScalarOperator>                               <ScalarOperator>                                 <Intrinsic FunctionName="getdate" />                               </ScalarOperator>                             </Intrinsic>                           </ScalarOperator>                         </ColumnReference>                       </Identifier>                     </ScalarOperator>                   </Row>                 </Values>               </ConstantScan>             </RelOp>           </QueryPlan>         </StmtSimple>       </Statements>     </Batch>   </BatchSequence> </ShowPlanXML> 

Answer 2

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 

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

The same applies to MONTH(SomeDate) vs. DATEPART(MONTH, SomeDate).

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.