MS Sql Full-text search vs. LIKE expression
- by Marks
Hi.
I'm currently looking for a way to search a big database (500MB - 10GB or more on 10 tables) with a lot of different fields(nvarchars and bigints).
Many of the fields, that should be searched are not in the same table.
An example: A search for '5124 Peter' should return all items, that ...
have an ID with 5124 in it,
have 'Peter' in the title or description
have item type id with 5124 in it
created by a user named 'peter' or a user whose id has 5124 in it
created by a user with '5124' or 'peter' in his street address.
How should i do the search? I read that the full-text search of MS-Sql is a lot more performant than a query with the LIKE keyword and i think the syntax is more clear, but i think it cant search on bigint(id) values and i read it has performance problems with indexing and therefore slows down inserts to the DB. In my project there will be more inserting than reading, so this could be a matter.
Thanks in advance,
Marks