Topic:
Today’s large data fields (LDF) are full of unstructured information stored in varchar, text, varbinary or xml data types. How do you write an application to search the column for patterns? Traditional SQL techniques using a column INDEX and LIKE operator result in a query plan that contains a full table scan.
I will be introducing the brothers Grimm database that has the full text of each fairy tale. I will create a full text catalog / index, select a change tracking strategy, define optional stop list / thesaurus file, and then populate the index. I will use CONTAINS and FREETEXT operators in SELECT queries to leverage the FTI. This resulting query plan performs index seek.
Coverage:
- Creating a database from scratch.
- Creating a table with LOB field.
- Loading files via BULK INSERT.
- Performance via traditional techniques.
- Creating a full text index.
- Performance with the full text index.
- Using FTI in a Azure SQL database.
Speaker:
John Miner
John has twenty-five years of data processing and project management experience. His expertise encompasses all phases of the project life cycle, including design, development, implementation, and maintenance of systems. He is currently a Senior Consultant at Atrion Networking Corporation providing customers with Database and Business Intelligence solutions. When he is not busy at PASS events, he spends time with his wife and daughter enjoying outdoor activities.
Links:
No YouTube for this presentation, but we do have the presentation materials!
https://github.com/JohnMiner3/community-work/tree/master/full-text-indexing