The issue is that your query through the view is the same as doing
WHERE CAST(SomeTable.ID as NVARCHAR(10)) = N'1'
Pretty much any
CAST of a column in a predicate will render that predicate unsargable. The only exceptions that I am aware of are a
datetime column to
NVARCHAR under some collations.
There is no such exception for
You might hope that it would do something like
WHERE SomeTable.ID = TRY_CAST('Your search string' as int)
but it is not as simple as that. If your search string is
'1' the two would return the same results but for the search string
'¹' (superscript 1) the casting to
int fails but the string comparison compares equal under some collations. Conversely for the search string
' 1' (with leading space) the
int cast and comparison would discard the leading space and compare equal but the string comparison would compare unequal (and similarly with empty string which is cast to
0 when converted to
In general you could create either an indexed view or a new index on the base table referencing a computed column but both seem quite suboptimal compared with removing the
CAST from the view so the existing index can be seeked.
You could create a computed column on
SomeTable with definition
CAST(ID as NVARCHAR(10)) and then index that.
ALTER TABLE SomeTable ADD strID AS CAST(ID as NVARCHAR(10));
CREATE INDEX IX ON SomeTable (strID ) INCLUDE (ID, SomeColumn1, SomeColumn2);
WHERE ID = N'1';
Given the restrictions in the question the computed column idea seems ruled out. An alternative will be to create an indexed view but likely you will need to change the query text to get this to work.
- In all editions except Enterprise Edition the
NOEXPAND hint will be needed to get the indexed view to be matched.
- If you are on Enterprise Edition the automatic matching in principle would work whether you indexed the original view or created a copy...
- ... but index view matching will only be considered at later stages of optimisation. If the query is cheap enough optimisation will end before it gets to that point. In your case in particular you will also need to fight against trivial plan. I added a million rows of data to the table and the indexed view still wasn't hit - as the plan was below the cost threshold for parallelism and it didn't proceed to further optimisation phases beyond trivial.