I first posted this on SO but got no replies, this is the reason for why I'm trying here now and I hope I'm not breaking any rules in doing so (fingers crossed).
I'm trying to understand how to use table partitioning and partition elimination correctly so that I can decide if I want to use it or not in a new database (sql server 17 express). I'm leaning towards not using it but that's a different story so this question is more that I wan't to know how it works now. In the following script I create a partitioning function and scheme, a table using the scheme, insert some data and then query it in 3 different ways.
The first query is correctly only accessing the first partition (actual partition count = 1, actual partitions accessed = 1).
I expect the second query to only access the first partition as well given that I've created the partition function as range right on the value 20200101 but it is accessing partition 1 and 2 (actual partition count = 2, actual partitions accessed = 1..2).
I expect the third query to only access partition 2 but it is accessing partition 2 and 3 (actual partition count = 2, actual partitions accessed = 2..3).
I'm aware that I need to have exact type matching for partition elimination to occur but I think I have that here? What am I missing?
create partition function partfun (datetime2(0)) as range right for values ('20200101', '20210101') create partition scheme ps as partition partfun all to ([primary]) create table t ( id int not null identity(1,1), logdate datetime2(0) ) on ps(logdate) insert into t values ('20190101'),('20200102') select * from t where logdate < cast('20191231' as datetime2(0)) select * from t where logdate < cast('20200101' as datetime2(0)) select * from t where logdate >= cast('20200101' as datetime2(0)) and t.logdate < cast('20210101' as datetime2(0))