Using The YEAR Function

Kendra Little looks at the performance ramifications of using the YEAR() function:

But if we hover over that index seek, we can see in the tooltip that there’s a hidden predicate that is NOT a seek predicate. This is a hidden filter. And because this is SQL Server 2016, we can see “Number of Rows Read” — it had to read 9.3 million rows to count 1.9 million rows. It didn’t realize the 2006 rows were together– it checked all the females and examined the FakeBirthDateStamp column for each row.

Built-in functions can cause SARGability issues.

Related Posts

Compress Those Indexes

Pamela Mooney reminds us that if you’re going to compress your heap or clustered index, remember those non-clustered indexes as well: We compress to gain space, correct? So why sacrifice it to our indexes? Here is a script (complete with demo databases and tables) that will find the culprits and fix them for you.  Put […]

Read More

READPAST In Action

Erik Darling shows how READPAST is no panacea: Locking hints can be really handy in these situations, especially the READPAST hint. The documentation for it says that it allows you to skip over row level locks (that means you can’t skip over page or object level locks). What it leaves out is that your READPAST […]

Read More

Categories

March 2016
MTWTFSS
« Feb Apr »
 123456
78910111213
14151617181920
21222324252627
28293031