Points to Consider While Writing Queries - Microsoft SQL Server 2012 Performance Tuning - page 265

2. Now, if there is a need to list out all people from the Person table whose last

modified date is in the year 2003 . A developer will be tempted to use the DATEPART
function, as shown here:
SELECT
BusinessEntityID
,ModifiedDate
FROM
Person.Person
WHERE
DATEPART(YYYY,ModifiedDate)='2003'
GO

3. This is really a bad way to draft the query; a shorter query is not always good. Let us
twist the WHERE part a little bit to get a workaround to remove a function from the
predicate in the WHERE clause, which will return the same logic:
SELECT
BusinessEntityID
,ModifiedDate
FROM
Person.Person
WHERE
ModifiedDate >= '01/01/2003' AND ModifiedDate <= '12/31/2003'
GO

The following screenshot shows both the queries:

The first query has used scan on the non-clustered index, whereas the second query
has used seek in the non-clustered index and used only 6 percent of total query
execution cost. Isn't the second one better?