>> Learn all about
full text indexing basics with Azure SQL and SQL Server,
featuring a very interesting and potentially Grimm demo
this week on Data Exposed MVP edition.
>> [MUSIC]
>> Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed MVP edition.
Today I'm joined by John Miner.
John, thanks so much for joining us today.
Can you kick us off by telling us a little bit about what you do?
>> Yes, Anna. Hi. Thank you for having me. My name's John minor.
I've been watered the MVP for seven years.
I work for a company, Inside.
I am a digital architect, data architect.
Today we are going to be talking about
full text indexing and why it's important.
>> Awesome, I love the topic and I think
you're the exact right person to do this from what I understand,
you've been speaking about full text indexing for awhile,
as we'll see shortly from some of your slides.
But I didn't want to start off like why full text indexing is
important and then why is doing it with a tool like
Azure SQL or SQL Server useful?
>> Excellent. In the slide deck
and I can get to it right now if you want.
>> Yeah, let's do it.
>> Excellent. Go back one slide.
We're going to talk about Modern Data Types.
Today's large object binary types
can store up to two gigabytes information.
That's a lot information. This could be unstructured or
structured information that could be like
or document could be text.
Typically nowadays, Microsoft supports like Varchar,
Varbinary, XML, JSON.
Obviously the engines backwards compatible, so Text,
Ntext and image will be out there,
there will be removed obviously in the future release.
The reason why we want to use
full text indexing is we do not want to
use a full table scan or a clustered index scan.
We want to actually do a seek
and full-text index and allows you to do that.
We need to have some type of database to
actually test this out on.
One of my favorite shows a bunch of years ago.
Back is grim, and it's the Brothers Grimm.
They actually had a bunch of fairy tales and they
never ended out really happy.
Someone always died on them.
But we're going to pull that text from the Internet
and we're going to load it up into database
and when they do full-text indexing on it.
Let's look at search pattern one.
The traditional ways to do searching
back in the day on I was like the white command.
We know that if we do
like and then percent B, it's not searchable.
If we're working for
the word wolf and there's always
a big bad wolf eating someone in the fairy tales.
Basically, if we do percent wolf,
it's not going to find anything.
Or if it does find something.
It's not going to use that index where you really want to.
There's been in the past two different ways to do it,
a LIKE command or PATINDEX command.
I'm going to skip a bit to the demo right now.
It's actually going to show you that
we're going to get a non-optimal search.
If we look at a demo here,
we have a table called fairytales,
and it's an anagram database.
We can see Grimm up at the top.
We can see dbo, table_fairy_tales.
If we look for the word wolf and we execute it,
we actually get the title of the venter's table.
We also have the actual texts of the venture.
We did get hits and we did get some results.
But if we look at the execution plan here,
we're getting the clustered index scan and that's not optimal.
Let's try this again with the PATINDEX and guess what?
We're going to get exactly the same results.
We're going to get the same data set
and we get the same execution plan.
Let's try to fix it.
Let's maybe make a non clustered index,
on this quote, unquote the text of the fairy tale.
Create non clustered index on the fairy tales given the field.
We're going to try this,
and oops, it fails.
How do we fix this problem, Anna?
>> I don't know.
Why does it fail actually?
>> It fails because you cannot have
indexes on a large object binary type,
it's not supported, it's not in the actual data page.
To fix this note, we have to do is we
have to create a full text indexing.
In this time period,
we don't have enough time to go through
the steps to show it in a demo.
But I'm going to tell you what you need to do.
You right-click on the table.
You go to the full text index wizard and it's right here,
it's listed here step 0.
Then what we're going to do is we're going to select the table.
Obviously raised had the table
and we're going to pick the primary key,
needs some type of way to identify that route.
Then we're going to pick the column in which we want to search.
That's my tail. There's two ways to
do change track and once to be me annual, another one's automatic.
Now the problem with automatic is it's going to kick this job
off with the full text indexing every time a change happens.
For systems that really busy, we don't want to do that.
Last but not least, we want to create a new catalog.
The catalog can have one or more indexes.
You know what we're going to do is
we're going to create this index in it.
Then we're going to schedule a time to
run the update to the catalog.
Once we have the full text indexing,
we can actually use different keywords
to actually solve this problem.
I'm going to go back to my deck,
cut cross right here,
minimize and talk about why this good.
The performance results of
the first task was we did
a full table scan that wasn't really good.
The second one we could have a full
clustered index scan, still not good.
We tried adding a index,
now in cluster two to the logic object binary.
There's some limitations in the engine.
One is the number of fields and number of bytes.
Again, this deck spinel while in
my repertoire presentation, so things change.
Please look at the MSDN documentation for up-to-date information.
What we're going to do now is we built the full text indexing.
There's a bunch of keywords that allows us to now use
a function call instead of a actual index search.
That function call will be going
ahead and returning a full table value result.
That will give us the pointers to where the data is.
Contains is for exact matches free taxes for fuzzy.
We can see also contained table
and freeze table are the same thing,
the only difference is that adds ranking.
Calving back to the demo.
Let's take a look. We're going to do
the same search, we're going to execute it.
We can see now we have the same results.
What's the execution plan?
If we move up, we can see the execution plan
now is using a table valued function, like I said.
It's using that index.
It's going to be a lot quicker on large datasets.
Obviously, we're not going to see
the performance on self-deploy database.
Now, if we do the contains table, we get a ranking.
Execute. We can see before we
had the matches but we didn't know which one is the best match.
From this we can see that the actual 37 tail.
The Wolf and the seven little kids is the best match.
Back to you Anna. Do you have any questions?
>> Yeah, this is really cool.
Can you explain just a quick moment,
like what the ranking is actually doing?
I know I should know but I don't.
>> It's going through in it's trying.
Again, I'm not an expert at it,
but there is other functions on I'm not showing here.
Please read my article with DMVs. You can go out there.
It actually goes ahead and tries to figure out the number of
occurrences of the word and if
the word wolf happen more than one article versus alpha,
it might rank it higher.
If we're doing a combination of words
like big data will, for something like that,
it might have a higher-ranking.
Now, the question you might say is,
does this ever go wrong?
In life things go wrong.
We can go ahead and we look at fairy tales and say,
hey, let's take the fairy tales.
I'm going to run this first.
I'm going to say, hey, what's due this query,
look for the word wolf.
We got seven results.
We see that 22's here.
I'm going to null out the data for 22.
We can see now if I run this again,
the data is no longer there.
But guess what? If I do my search again,
it's actually going to work. Why is that working?
>> Yeah.
>> Because we have not
updated full text indexing. We set up to manual.
This is the thing that typically you do like ETL load
at night into your database that
you allow and you should do search.
What we need to do is when you say populate the index again.
Now if we go here,
we were seeing that 22 has shown up as number 4.
Guess what, 22 is gone.
>> Got you. That makes a lot of sense.
Best practices, how do you
mitigate this situation and then it's off a bit.
Like what are some situations where you've actually
seen this used in real life?
>> Excellent, good questions Anna.
First one is best-practices.
Run your ETL once a day
or after you run ETL you have to do an update.
It's just one of those things you can do Automatic,
but again, on a transaction system
and it's something that's really busy.
You don't want to kick off a index build
when it's really a busy time today.
I would say try to schedule it.
Second thing is that I'm looking
at the performance results page on the demo.
We can see now that we've turned that index EQ
into now scan into a SQ and that's really great.
Again, we talked about the toy database.
It's small, you're not going to see great timings here.
But when you have huge data, you're going to see a lot.
Again, best-practices run it at night.
As for use cases,
how do you apply the technology.
Two examples I've done in the past.
One is I worked at a bank,
and we had help-desk ticket from a cost system.
We imported the data.
This is going a long time,
go back into 2005 in around there.
Full-text indexing has been in
SQL Server and Azure SQL for a long time.
That last but not least, all the application.
We add an application that was making bad XML,
for the middleware and we want to search for errors and
we couldn't because we can pass XML.
We'll guess what we throw it into
a text field and we put a full text indexing on it.
Next thing you know, we could actually do search on.
>> Amazing, cool.
John this has been so useful.
I learned so much about full text indexing.
I think this was the perfect technology information.
I loved the demo, I
really feel like I've had a good handle on it.
For our viewers.
We're going to include several references to things you've read in
the past and resources to learn more about full text indexing.
I do want to thank you so much John,
for coming on the show.
For our viewers, go ahead and check out those references.
There will be a description,
like this episode and leave a comment and let us know what you
thought about this video and the wolf scenario.
We hope to see you next time on Data Exposed.
[MUSIC]