# The Case of DATETIME2 and Partition Elimination

Page content

Data types are hard.

I’ve been working on some demo code for table partitioning. I have a table partitioned by a column named FakeBirthDateStamp, which is a DATETIME2(0) column. The table is a partitioned heap. At this point in the demo, I hadn’t built any indexes. I wanted to show that partition elimination could occur on a partitioned table, even without a clustered index.

So I ran this simple COUNT() query:

SET STATISTICS TIME, IO ON;
GO

SELECT COUNT(\*)
FROM dbo.FirstNameByBirthDate\_pt
WHERE FakeBirthDateStamp >= '1989-01-01' AND
FakeBirthDateStamp < '1990-01-01';
GO


It took much longer than I expected, and did 367K logical reads.

SQL Server Execution Times: CPU time = 15797 ms, elapsed time = 4431 ms.

Here’s what the actual execution plan looks like. I can see that a nonclustered index would be a smaller thing to scan to do a count, but we can’t tell by this view if it was able to get partition elimination or not. I’m suspicious that it didn’t because of that high logical read count.

Right clicking the table scan operator and looking at the properties, I can confirm that it accessed all 43 partitions– not just the partition for 1989.

That isn’t what I meant to demo.

It’s the opposite!

## Why Didn’t Partition Elimination Work?

I found the answer when I hovered over the tooltip for the table scan and looked more closely at the properties of my predicate.

SQL Server is implicitly converting my date value to DATETIME2(7). That is a larger, more precise value than the data type I have in the table–FakeBirthDateStamp is DATETIME2(0).

That data type mismatch is preventing partition elimination!

## The Fix: Explicit Conversion

Rewriting the query this way yields different results:

SET STATISTICS IO, TIME ON;
GO

SELECT COUNT(*)
FROM dbo.FirstNameByBirthDate_pt
WHERE FakeBirthDateStamp >= CAST('1989-01-01' AS DATETIME2(0)) AND
FakeBirthDateStamp < CAST('1990-01-01' AS DATETIME2(0));
GO


I’ve told SQL Server to use a smaller data type for these dates which matches the column data type. SQL Server doesn’t have to do an implicit conversion anymore, and can eliminate partitions.

My performance immediately improves. I’m down to 52K logical reads (from 367K), and my CPU time and duration are waaaay better.