Whats the Best Practice for a Search SQL Query?

Question

I have a SQL 2008 Express database, which have following tables:

CREATE TABLE Videos
    (VideoID bigint not null, 
    Title varchar(100) NULL, 
    Description varchar(MAX) NULL,
    isActive bit NULL  )

CREATE TABLE Tags
    (TagID bigint not null, 
    Tag varchar(100) NULL )

CREATE TABLE VideoTags
    (VideoID bigint not null, 
    TagID bigint not null )

Now I need SQL query to search for word (i.e. Beyonce Halo Music Video) against these tables.

Which videos have:

• For Title exact phrase will get 0.5 points
• For Description exact phrase will get 0.4 points
• For tags exact phrase will get 0.3 points
• For title all words will get 0.2 points
• For description all words will get 0.2 points
• For title one or more words will get 0.1 points
• For description one or more words will get 0.1 points

And I will show these videos on basis of points. What will be the SQL Query for this? A LINQ query will be more better.

If you know a better way to achieve this, please help.

Answer 1

You should Full Text Search for this.

Other than that you could make a search query per table and then use a case for weights. but this method is very bulky and not really good.

select case when Title = 'yourPhrase' then 0.5 when Title like '%yourPhrase%' then 0.1 when Title like '%your% %Phrase%' then 0.2 end as Weight
from yourTable
where Title like %yourPhrase%