I have 3 tables:
tblMaps - ID, Map
tblLocations - ID, Location
tblMap_Location - ID, MapID, LocationID
Each Map have between 1000-3000 Locations. Each Location is limited to 100 characters in length. tblMaps is expected to have between 2 to 2.5 million records.
I'm currently using full-text indexing on tblLocations.
I found that the CONTAINS statement has some limitations. It is unable to perform wildcard search such as: *ork, Y*k or *or*. I would have to use LIKE statement for those searches.
My question are:
1) Is there a way to perform the wildcard searches that I specified using CONTAINS statemnt?
2) Does the LIKE statement benefit from full-text indexing?