I would like to do a search across columns of joined tables. What would the best index configuration be for the query below?
Table A ------------ Id ValueA -- Table A has many more columns Table B ------------ Id AId ValueB -- Table B has many more columns
At the moment, I have clustered indexes on the Primary Keys and non-clustered indexes (which don't include any columns) for columns
SELECT * FROM TableA a INNER JOIN TableB b ON a.Id = b.AId WHERE a.ValueA LIKE 'SearchTerm%' OR b.ValueB LIKE 'SearchTerm%'
Update Table A is likely to run into the low millions while Table B will only have low tens of thousands of entries.
In the real world Table A will be a list of transactions, where the value is some sort of a transaction reference. Table B will be a list of users where the value could be anything such as a name or any other descriptive information.
At the moment we use an ORM which will essentially do a
SELECT *. Naturally this could be changed, but there would be a fairly large impact on the application side.