SQL Server Keyword search

[dj4uk]

I have a table that has a column that will contain a comma separated list of keywords associated with each record.

In my application a user has a free text field to enter search terms into that I want to match against the comma separated list in the database. How would you go about doing this? Simple query or implement a FULLTEXT index? Just interested to see what people think would be the best method. Is hold keywords in a comma separated list even the best way of doing this?

Advice appreciated

DJ

[kaffenils]

I have a table that has a column that will contain a comma separated list of keywords associated with each record.

In my application a user has a free text field to enter search terms into that I want to match against the comma separated list in the database. How would you go about doing this? Simple query or implement a FULLTEXT index? Just interested to see what people think would be the best method. Is hold keywords in a comma separated list even the best way of doing this?

Advice appreciated

DJ

If you do a standard where query you would need surround your search string with % wildcards. That means that SQL Server can't use any indexes and will execute a full table scan. If the table contains a lot of records (houndreds of thousands) this will most likely result in poor performance.

SQL Server full text search would probably do the trick for you. It's fast and you can perform quite advanced searches, i.e. fuzzy search. It also implements result ranking.

[MarkDorf]

I would put the keywords in a separate table, with a foreign key referencing the "original" table.