January 25, 2018 at 11:54 am
Hi,
I have some non sargable queries like below:
select * from dbo.test
where name like '%john%'
Upon talking to the application team, I found out that one of the search requirement is that the users can enter any 3 letters of their names and get the result set back. This prompted me to look into the full text engine. However, I found out that full text is good at searching the entire words or phrases, but not really great at searching certain characters inside of a word. I was wondering someone with more knowledge of the full text search can help me with this issue.
On anther note, is there a way I can work around this issue without having to use full text engine?
Thanks
January 25, 2018 at 11:57 am
Srl832 - Thursday, January 25, 2018 11:54 AMHi,
I have some non sargable queries like below:
select * from dbo.test
where name like '%john%'
Is there a question you'd like to ask?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 25, 2018 at 3:09 pm
Srl832 - Thursday, January 25, 2018 11:54 AMHi,
I have some non sargable queries like below:
select * from dbo.test
where name like '%john%'Upon talking to the application team, I found out that one of the search requirement is that the users can enter any 3 letters of their names and get the result set back. This prompted me to look into the full text engine. However, I found out that full text is good at searching the entire words or phrases, but not really great at searching certain characters inside of a word. I was wondering someone with more knowledge of the full text search can help me with this issue.
On anther note, is there a way I can work around this issue without having to use full text engine?
Thanks
There is a post about the same issue and alternatives you are mentioning. I haven't tried it but It may be worth a try for you to see if something like this works in your case:
One way to get an index seek for a leading %wildcard
Sue
January 26, 2018 at 4:26 pm
Hi Sue,
It does look like the solution will work very well if
1. The data has a fixed small length.
2. The table is fairly small.
In my case, the data type is fairly small as it is the name field. However, the table has over half a million records and if I have to create a child table with the fragments, the child table is going to be even bigger and harder to maintain.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply