Also the simpler and maybe better approach is just make the decision every time as an operation in the plan, attempt the cast if it fails then scan and cast a many times the other way, if it succeeds then use the index, this isn't hard and adds one extra cast attempt on the slow path otherwise it does what everyone has to do manually in their code like this article but transparently.
Running the optimizer for every execution of the same query is... not very optimal.
It can run it for a range of values: https://learn.microsoft.com/en-us/sql/relational-databases/p...
Also the simpler and maybe better approach is just make the decision every time as an operation in the plan, attempt the cast if it fails then scan and cast a many times the other way, if it succeeds then use the index, this isn't hard and adds one extra cast attempt on the slow path otherwise it does what everyone has to do manually in their code like this article but transparently.
The adaptive join operator does something much more complex: https://learn.microsoft.com/en-us/sql/relational-databases/p...
It's not "the value", it's "the values".