Hi everyone
I wanted to know that what may be the best practice to search for keywords or sentence in sql server.
Suppose I have a table say products with name, description and keywords. I have a search function in my website where the user may type in anything (sentence or single word).
I query the product table columns using like clause and shortlist the list to rows containing the search criteria.
To reduce the response time in case if the volume of data in product table is large, I add a pagination query of 10.
Is the above explained scenario a good practice?
Thanks in advance
What I have tried:
My current query
select * from (select srno as productid, name +' '+ descriptions +' '+ keywords as search
from ProductDetails) PD
where PD.search LIKE '%bed%' OR PD.search LIKE '%sheets%'
Here I have combined the three columns namely name,descriptions and keywords and I am searching for "bed sheets" in my product table
Table Structure
ProductDetails example
srno|Name|Description|keywords
1|Floral Bed Sheets|..some text..|bed sheets
2|Square Pillow Covers|.. some text..|pillow covers square
Is it proper to place keywords in a separate column or should I create a new table for keywords.
If I have to create a new table for keywords, how to modify the above query?