Sample Header Ad - 728x90

Causing SQL to prefer casted time seek in view query

0 votes
0 answers
49 views
We have ETL process executed by Kafka. It can only get and query by datetime2 date data type. It generates queries like this: EXEC sp_executesql N'SELECT * FROM ViewName WHERE ViewName.UpdateDate > @P0 AND ViewName.UpdateDate '2022-07-01' --Can add UpdateDate > DATEADD(d, -2, GETDATE()) GO But in this case SQL decides first to scan this index and only then apply parameters filtering causing heavy reads. I can add commented out filter to reduce reads, but I'm searching for solution to make SQL "understand" that it can use parameters instead. We cannot create non filtered index because of table size. Also adding Cast to UpdateDate in where clause causes SQL not to use index at all. Is there something that can be done in this case code wise?(Forceseek hint throws error)
Asked by Michael Cherevko (742 rep)
Aug 6, 2024, 02:13 PM