How to create an index for Wildcard search on an Int Column in SQL Server ?

Question

I've got a table with millions of rows. The PK of that table is an Int column. User wants to be able to do wildcard search on rows of that table by that PK. When doing this query the trivial way, it will be horribly slow, as Sql server does an implicit conversion of the column value from int to char in order to apply the wildcard every single time that query is run.

The idea would be to create an index that stores the "converted to char" value of that int column, so Sql Server can reuse that instead of reconverting millions of values every query.

How can I implement that?

Note: changing the business requirement of doing the wildcard search or changing the column in DB schema to be a char are not options in this case.

Cheers!

Answer 1

SQL Server doesn't have function based indexes, but you can simulate the behaviour by adding a computed column that casts/converts the data type to VARCHAR/etc, and then index this computed column. Still, the benefit is moot if you're wildcarding the left side of a LIKE comparison.