Indexing, Querying and Analyzing Text with SQL Server 2012-2014

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.
Course info
Rating
(74)
Level
Intermediate
Updated
Feb 7, 2014
Duration
2h 48m
Table of contents
Description
Course info
Rating
(74)
Level
Intermediate
Updated
Feb 7, 2014
Duration
2h 48m
Description

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.

About the author
About the author

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.

More from the author
Data Mining Algorithms in SSAS, Excel, and R
Intermediate
2h 59m
Jul 24, 2015
Working With Temporal Data in SQL Server
Intermediate
3h 6m
May 28, 2014
Section Introduction Transcripts
Section Introduction Transcripts

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.