Working with textual data in SQL Server involves indexing, querying and analyzing texts. Improve transactional and analytical applications with Full-Text Search and analyze texts with Semantic Search and Text Mining.
It is hard to imagine searching for something on the Web without modern search engines like Bing or Google. However, most contemporary applications still limit users to exact searches only. For end users, even the standard SQL LIKE operator is not powerful enough for approximate searches. In addition, many documents are stored in modern databases; end users would probably like to get powerful search inside document contents as well. Text mining is also becoming more and more popular. Everybody would like to understand data from blogs, Web sites, and social media. Microsoft SQL Server in versions 2012 and 2014 enhances full-text search support that was substantially available in previous editions. Semantic Search, a new component in Full-Text Search, can help you understand the meaning of documents. Finally, the Term Extraction and Term Lookup components from SQL Server Integration Services also helps with text analysis.
Dejan Sarka, MCT and SQL Server MVP, is an independent consultant, trainer, and developer focusing on database & business intelligence applications. His specialties are advanced topics like data modeling, data mining, and data quality.
Introduction Hello, and welcome to the Indexing, Querying, and Analyzing Text with SQL Server 2012 and 2014 Pluralsight course. My name is Dejan Sarka, and I thank you for listening to this course. In this course, I'm going to describe options that exist in SQL Server versions 2012 and 2014 for working with text. So, what does it mean working with text? Well, end users, and so do we all, love to have approximate search like on search engines Bing, Google, and others. Transect-SQL has LIKE predicate, which is simply not powerful enough for similar searches, so we would like to search in sting columns and also in full documents stored in the SQL Server database. In addition, we want to analyze this text. We want to understand data from blogs, from Twitter, from websites, Facebook, and any other social media. Unfortunately, performing text mining is not a very simple task. SQL Server has many components that help us dealing with this task. We have Full-Text Search and Semantic Search built into the database engine. In SQL Server Integration Services in the data flow, we have the Term Extraction and Term Lookup transformations. Note that although this close is focused on SQL Server 2012 and 2014, many of these components existed in the previous versions as well. For example, Term Extraction and Term Lookup are present in Integration Services from version 2005. Only Semantic Search was added in 2012.
Queries with Full-Text Search Predicates Hello, I'm Dejan Sarka, and this is the Indexing, Querying, and Analyzing Text with SQL Server 2012 and 2014 course. This is the third module, Queries with Full-Text Search Predicates. In this module, I'm going first to introduce what kind of searches can you perform with full-text predicates. Then I'm going to describe the CONTAINS predicate in depth. I will present all valid options for the CONTAINS predicate. The next predicate I'm going to talk about in this module is the FREETEXT predicate. This is useful for quicker searches, although the searches are less precise. After a bunch of demos, I will wrap up the module and make a quick recapitulation.
Full-Text and Semantic Search Table-Valued Functions Hello. I'm Dejan Sarka, and this is the fourth module of the Indexing, Querying, and Analyzing Text with SQL Server 2012 and 2014 course. This module is about Full-Text and Semantic Search Table-Valued Functions. Let me make a brief overview about this module. First, let's recapitulate what we have already seen in the first three modules. After an introduction, I installed Full-Text Search and Semantic Search elements on my SQL Server instance, and then I created a demo database in the demo table and populated the demo table with a couple of documents. Next, in the third module I went over the CONTAINS and FREETEXT Full-Text Search predicates. In this module I will make a brief introduction to Full-Text Search and Semantic Search table-valued functions and then explain in detail with demos the Full-Text Search functions and of course the Semantic Search functions. After more demos, I will wrap up the module with a quick summary.
Extracting Terms from Texts Hello. I'm Dejan Sarka, and this is the fifth module of the Indexing, Querying, and Analyzing Text with SQL Server 2012 and 2014 course. The title of this module is Extracting Terms from Texts. So, what am I going to talk about in this module? First, I will briefly introduce what does it mean to extract terms from texts. So far, we have seen Full-Text Search indexes and queries and Semantic Search queries. The Full-Text Search engine did everything for us, extracted terms, did the stemming, and finding terms in text, and Semantic Search also compared texts to find semantically similar documents. The question is what if I am not satisfied with these results? What if I would like to get more in-depth insight? Well, in this case I must extract terms manually and then analyze them with any kind of tools I can get in the SQL Server Suite. So, I will show you how you can use SQL Server Integration Service's Term Extraction Transformation for this task and also Full-Text Search functions. And then after a lot of demos I will wrap up this module.
Looking Up for Terms in Texts Hello. I'm Dejan Sarka, and this is the Indexing, Querying, and Analyzing Text in SQL Server 2012 and 2014 course. This is the last, the sixth module of this course, and the title of this module is Looking Up for Terms in Texts. In this module, I'm going first to introduce what does further analysis of terms and texts mean. Then I will explain the SQL Server Integration Services Term Lookup transformation in depth. And then I will show you further analysis of extracted terms. I will mine the Term Extraction and Term Lookup results. I will also show you how you can use the Excel Power Pivot data model to analyze terms extracted with Full-Text Search functions and how to create a SQL Server Reporting Services report to present the results of the Term Extraction transformation. Then I will wrap up the module and the course.