SQL Server 2019: Issues with Date Range Filtering on Partitioned Tables
I'm prototyping a solution and I'm working through a tutorial and I'm experiencing unexpected behavior when trying to filter on date ranges from a partitioned table in SQL Server 2019. The table has been partitioned by a date column (`OrderDate`), and while I expect the queries to leverage partition elimination effectively, I notice that performance is not as good as anticipated. Specifically, I'm running the following query: ```sql SELECT * FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate < '2023-02-01'; ``` Despite this being a straightforward filter, the execution plan indicates that all partitions are being scanned rather than just the relevant ones. The estimated execution plan shows the partitions being accessed, but the actual execution time is considerably longer than expected. I have verified that the statistics on the `OrderDate` column are up to date using: ```sql UPDATE STATISTICS Orders; ``` However, I still see a significant performance hit. I've also tried using `OPTION (RECOMPILE)`, but it hasn't made a noticeable difference. Furthermore, I've created the partition function and scheme correctly and have been able to query other partitions without issue. Here’s a snippet of how the partitioning is set up: ```sql CREATE PARTITION FUNCTION OrderDatePartitionFunction (DATE) AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-02-01'); CREATE PARTITION SCHEME OrderDatePartitionScheme AS PARTITION OrderDatePartitionFunction TO (FileGroup1, FileGroup2); CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATE, Amount DECIMAL(10, 2) ) ON OrderDatePartitionScheme(OrderDate); ``` I'm wondering if there’s anything specific I should be checking in the partitioning setup or if there are any additional optimizations I can apply to ensure that the partition elimination works as intended. Any insights would be greatly appreciated! I'm working on a web app that needs to handle this. This is part of a larger service I'm building. What are your experiences with this?