PHXHoward (11/30/2015)
Lynn's solution got us through it. I don't understand why it worked but it did.Creating a persisted computed column with '_' + PK and then creating a NCI on that column caused the query to seek on that index.
Run this:
select top 10 * from dbo.<your table name here>;
Look at the data in your PK column and the computed column. You have indexed the computed column which has the same data type as the concatenated value generated in the query. SQL can now do a seek on the NCI you created instead of having to scan the entire table concatenating an underscore and converting an integer value to a character string to complete the comparison.