question

Chalapathi_Pinisetty avatar image
Chalapathi_Pinisetty asked

I have a table with partition function on DATETIME2(3) column and when I try to use the same column in query filter it is not using the partition elmination? Could some one pl

I have a script like below with partition enabled in table and when try to query data from table then optimizer is not using partition elimination and it is using only when I explicitly convert date column into datetime2(3). Please advice. use tempdb go --create partition function based on date column CREATE PARTITION FUNCTION Date_fn (datetime2(3)) AS RANGE RIGHT FOR VALUES ( '20100101 00:00:00.000',' 20100501 00:00:00.000', '20100901 00:00:00.000', '20110101 00:00:00.000',' 20110501 00:00:00.000', '20110901 00:00:00.000', '20120101 00:00:00.000',' 20120501 00:00:00.000', '20120901 00:00:00.000', '20130101 00:00:00.000',' 20130501 00:00:00.000', '20130901 00:00:00.000', '20140101 00:00:00.000',' 20140501 00:00:00.000', '20140901 00:00:00.000', '20150101 00:00:00.000',' 20150501 00:00:00.000', '20150901 00:00:00.000', '20160101 00:00:00.000',' 20140601 00:00:00.000', '20160901 00:00:00.000', '99990101 00:00:00.000','99990101 23:59:59.999' ); go --create partition scheme based on above partition function CREATE PARTITION SCHEME Date_scheme AS PARTITION Date_fn ALL TO ([PRIMARY]); GO --create new table with partiton key and parition scheme Create table dbo.Test (Id INT , DateIn Datetime2(3)) go CREATE UNIQUE CLUSTERED INDEX IX_TestTable ON Test(Id,DateIn) ON Date_scheme (DateIn); go --check whether partition is created or not in TEST table --if count=24 then table is partitioned as it have same number of partitions as our function Select COUNT(1) From sys.partitions Where object_id=object_id('dbo.Test') GO -- Populate data into table Insert into dbo.Test values (1,'20100101 00:00:00.000'), (2,'20100501 00:00:00.000'), (3,'20160101 00:00:00.000'),(4,'20160401 00:00:00.000'), (5,'99991231 23:59:59.999'),(6,'99991231 23:59:59.999'),(7,'99991231 23:59:59.999'),(8,'99991231 23:59:59.999') go --Based on below query, we have 1 row each on 2 and 3 partition, 2 rows on 21 partition and 4 rows on 24 partition Select * From sys.partitions Where object_id=object_id('dbo.Test') and rows>0 go --check if we pass date column directly into filter can use partition elmination or not --if you see the execution plan, then you can notice the below query is not elminating in partitons instead it checking every where Select * from dbo.Test where DateIn='99991231 23:59:59.999' go --But if we convert the date to datetime2(3) or pass variable to filter then partition elmination is happening and query is directly checking on last partition (24) Declare @Date AS DateTime2(3)='99991231 23:59:59.999' Select * from dbo.Test where DateIn=@Date --OR Select * from dbo.Test where DateIn=CAST('99991231 23:59:59.999' AS DATETIME2(3)) go --drop partition scheme Date_scheme --drop partition function Date_fn --drop table dbo.Test
partitioning
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
It's because the partition is defined with DATETIME2(3) and the date string you are using in the where clause is being implicitly converted to DATETIME2(7). Partitions cannot be eliminated when there is a potential for data loss in the comparison. Paul White covers this in some detail in this article: [ http://sqlblog.com/blogs/paul_white/archive/2012/09/12/why-doesn-t-partition-elimination-work.aspx][1] If you define your table and partition function with DATETIME2(7) then you won't have to explicitly convert the string in the where clause - otherwise you must explicitly convert (or cast). [1]: http://sqlblog.com/blogs/paul_white/archive/2012/09/12/why-doesn-t-partition-elimination-work.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.