Partition elimination is not performed for literal char values (WHERE col = 'a') for datetime2 datatype

Question

Is there a work around to force partition elimination for tables partitioned on a column with datetime2 data type for queries involving a where clause with liter values?

https://connect.microsoft.com/SQLServer/feedback/details/532503/partition-elimination-is-not-performed-for-literal-char-values-where-col-a

Any pointers on how to accomplish this without a cast in the where clause would be helpful.

Thanks,
Saptagiri Tangudu

Answer 1

Hi Saptagiri,

Thank you for your question.
 
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

Best Regards,
Ray Chen

Answer 2

Hi Ray,

Any update would be greatly appreciated.

Thanks,
Saptagiri

Answer 3

Any pointers on how to accomplish this without a cast in the where clause would be helpful.

Why is the explicit cast not a good enough workaround?

Josh

Answer 4

we cant change the application code that easily. It is in far too many places for us to change in a reasonable amount of time.

Answer 5

we cant change the application code that easily. It is in far too many places for us to change in a reasonable amount of time.

Well, not to be harsh, but sometimes it's just a big, bad world.

So there's a bug in SQL Server, or maybe it's a feature, but you're hoping they'll change it whatever it is.  

Meanwhile:

* Doesn't sound like the app design is very good, if it's creating the same SQL from many places.

* So it's a bug in your application, it will be fixed in the next revision or in an emergency release, and that's perfectly normal process.

Is it a shame that SQL Server doesn't provide a hook for magical command rewrites before submitting a command to execution?  Eh, maybe.  Does Oracle have some kind of feature where you can add hints at the last second, I thought I heard someone talking about that recently, but it might have been a feature in the app not Oracle.

Anyway, I'll bet you can fix your app faster than Microsoft is going to fix SQL Server, so even with everything you come out a hero!

Josh

Answer 6

Hi,

After some testing the implicit conversion of a datetime string to datetime2(7) datatype does not prevent partition elimination.

If you want to eliminate the conversion then you will need to use a stored procedure and have string as a datetime2 parameter.

Here is an example script:

use master
go
create database partition_test
alter database partition_test add filegroup part1
alter database partition_test add file (name='partition_test_part1_file1', filename='c:\data\partition_test_part1_file1.ndf') to filegroup part1
go
use partition_test
go

CREATE PARTITION FUNCTION myRangePF2 (datetime2) AS RANGE right FOR VALUES ('2012-01-01');
go
CREATE PARTITION SCHEME myRangePS2 AS PARTITION myRangePF2 TO (part1, part1);
go

create table datetime2part (
a datetime2 not null,
b int not null
)
ON myRangePS2 (a)
go

select * from datetime2part where a = '2011-12-31' --implicit convert to datetime2(7) but partition eliminated

--to see partition elimination look for string RunTimePartitionSummary in XML Showplan ouput

Thanks,

Cathy Miller