Questions on SQL Server 2008 Full-Text Search

Question

I have some questions about SQL 2K8 integrated full-text search.

Say I have the following tables:

1. Car with columns: id (int - pk), makeid (fk), description (nvarchar), year (int), features (int - bitwise value - 32 features only)
2. CarMake with columns: id (int - pk), mfgname (nvarchar)
3. CarFeatures with columns: id (int - 1, 2, 4, 8, etc.), featurename (nvarchar)

If someone searches "red honda civic 2002 4 doors", how would I parse the input string so that I could also search in the "CarMake" and "CarFeatures" tables?

Answer 1

Trying to parse search criteria like that will be a pain. A possible alternate solution would be to create a view that creates a long description of the car and create a full text index on that. So that view might look like:

Create View dbo.CarData
WITH SCHEMABINDING
As

Select dbo.Cars.Id
    , dbo.CarMake.Manufactuer
        + ' ' + dbo.Cars.[Year]
        + Coalesce(' ' + dbo.Cars.Description,'')
        + ' ' + Case When Features & 1 <> 0 Then (Select Name From dbo.CarFeature Where Id = 1) Else '' End
        + ' ' + Case When Features & 2 <> 0 Then (Select Name From dbo.CarFeature Where Id = 2)  Else '' End
        + ' ' + Case When Features & 4 <> 0 Then (Select Name From dbo.CarFeature Where Id = 4)  Else '' End
        + ' ' + Case When Features & 8 <> 0 Then (Select Name From dbo.CarFeature Where Id = 8)  Else '' End
        + ' ' + Case When Features & 16 <> 0 Then (Select Name From dbo.CarFeature Where Id = 16)  Else '' End As Description
From dbo.Cars
    Join dbo.CarMake
        On CarMake.Id = Cars.MakeId

With a fulltext index on that view, then you might be able to take your search criteria and do:

Select ...
From CarData
Where Contains(Description, Replace('red honda civic 2002 4 doors', ' ', ' AND '))

Now, this is far from perfect. For example, it will result in '...4 AND doors' and thus find car models in 2004 with 2 doors or 4WD and 2 doors. In addition, I did not see color in your schema so I'm not sure how that would get into the mix.

It would obviously be substantially simpler to force the user to break up the search criteria into its constituent pieces instead of trying to implement a Google-like search. Thus, you would restrict the user to selecting the color from a drop list, selecting the make from another drop list and so on. If you did this, then you wouldn't need the above mentioned View and could instead query against the columns in the tables.

Btw, the features column being a bitwise value makes searches more of a pain as you will need to do a bitwise AND operation on each value to determine if it has the feature in question. It would be better to break out the Feature to Car mapping into a separate table.