Help regarding the full-text search

Question

Hi All,

 We are using SQL Server 2005 Enterprise edition. I have a table with 41 columns and one column is defined with primary key. Now my requriement is that when the user search for the data by submitting the value, the data must be displayed. If similra data exists in 40 columns all the records must be displayed. My thought is the implementing full-text search is the best option. I don't have any idea on full-text search. Please suggest me how to achieve this.Please provide the useful links.

waiting for valuable replies.  

Answer 1

Hi,

Please refer the following link on full text searching. Hope you find this helpful.

http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

Regards,

---

Sharath

Answer 2

http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

Answer 3

If your 40 columns aren't of character types but for example are decimal, you may use IN predicate

... WHERE value IN (col1, ..., col40)

I wonder if optimizer will choose full table scan even with 40 indexes in place.

---

Serg

Answer 4

Hi Serg

  To achieve this one i want to define all the columns with character datatype. My doubt is that how to define no if indexes on 40 columns. Plz guide me .

Answer 5

If you have 40 character type columns, full-text option looks like preferable solution.

If your DB is still at design stage may I ask why you need 40 columns in the table? There may exist other options.

---

Serg

Answer 6

Hi Sorry for late reply

   We got the data in spread sheet and which consists the 40 columns and i need to design the table like that. Any advise

Answer 7

There is no problem then - follow the link previously mentioned

http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

And do the job - it looks pretty straightforward.

Thanks,

---

Ali Hamdar (alihamdar.com - www.ids.com.lb)

Answer 8

One option is to refactor every row of the  spreadsheet into 40 separate rows. Another is to split spreadsheet into number of tables holding some loosely  related "facets" of data. I can't say if it's exactly what you need without further details. 

---

Serg

Answer 9

Hi All,

   I have a table with 41 columns and one column in primary key. I need to findout the the records based on a related value. The related value may exist

in 40 columns. I know that i can use LIKE operator in query. I have to include 40 columns with LIKE operator in query. I think including 40 columns  in query results slow performance. How i can achieve this. Please suggest me with your ideas and valuable replies.

Answer 10

Like Naomi, I not sure exactly what you want.  But if it is something like you have a table like (this only has 4 columns plus a primary key, not 40, but you get the idea)

Create Table #Test(PK int Identity Primary Key, ColA varchar(20), ColB varchar(20), ColC varchar(20), ColD varchar(20));
Insert #Test(ColA, ColB, ColC, ColD)
Select 'John', 'Billy', 'Tom', 'Kathy'
Union All Select 'Susan', 'Larry', 'George', 'Mary'
Union All Select 'Robert', 'Kathy', 'John', 'Mike'
Union All Select 'Billy', 'Jim', 'Susan', 'Susan'
Union All Select 'Kathy', 'Susan', 'John', 'Larry';

and you want to know which rows have, for example, an 'r' in any of the columns ColA, ColB, ColC, or ColD.  Then I think the fastest way is likely to be either do the LIKE on each column or to do a concatenate of all the columns with some delimiter and do the LIKE on that concatenated result.  So either,

Select t.PK, t.ColA, t.ColB, t.ColC, t.ColD
From #Test t
Where t.ColA Like '%r%' Or t.ColB Like '%r%' Or t.ColC Like '%r%' Or t.ColD Like '%r%'

-- or

Select t.PK, t.ColA, t.ColB, t.ColC, t.ColD
From #Test t
Where t.ColA + '/' + t.ColB + '/' + t.ColC + '/' + t.ColD Like '%r%'

You could also do it with an UNPIVOT, but it will probably be slower that the ways above, that would be

;With cte As
(Select Distinct PK
From #Test
Unpivot
(Name For Value In (ColA, ColB, ColC, ColD)) As unpvt
Where Name Like '%r%')
Select t.PK, t.ColA, t.ColB, t.ColC, t.ColD
From #Test t
Inner Join cte c On c.PK = t.PK;

Tom

Answer 11

Hi Tom Cooper,

    in my reply i mentioned that i can go with LIKE operator. In condition if i m going to include LIKE operator for 40 columns performance will be poor. Suggestion plz. 

Naomi : your suggestion plz. 

Answer 12

My suggestion is to use UNION, e.g.

select * from myTable where Col1 LIKE '%Value%'

UNION 

select * from myTable where Col2 LIKE '%Value%'

etc.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 13

Hi Naomi,

    Thank you for your reply. If i  follow your suggestion for 40 columns, how the performance will be affected if there are thousands of records. I will trace out this with execution plan. Before that i want to take suggestion from you. Please guide me on this.

 

Answer 14

For 40 columns check I don't think you'll get good performance either way. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 15

Hi Naomi,

     Can  i go for full-text search. Is it possible to include 40 columns in search condition(in full-text search).

     I don't have idea on full-text search. I will study and try to implement it. Please let me know can i approach full-text search. Please guide me.

     Waiting for valuable replies.

Answer 16

Hi Naomi,

    Any suggestion.