Learn to Store, Analyze Documents on Windows File System With SQL Server

The focus of the article is on comparing documents that can be stored on Windows File System in one respect and in the other respect their comparative analysis that can be performed with Semantic Search in SQL Server.

Additionally, the readers will learn how to store unstructured data by exploring File Table and creating MS Word documents on the fly (instantly) to be consumed by Semantic Search.

This part of the article is related to the use of Semantic Search on unstructured data for the extraction of basic level business-crucial information provided standard naming is in place.


The readers need to go through **Learn to Store and Analyse Documents on Windows File System with SQL Server Semantic Search Part 1 **to proceed further with it.

This article assumes the readers are familiar with most (if not all) of the following:

This article also assumes that following steps have already been completed.

  • FILESTREAM Enabled Database EmployeesFilestreamSample has been set up.
  • File Table has been set up.
  • MS Word document **Asif Permanent Employee **and **Peter Permanent Employee **has been stored in a windows folder managed through File Table.
  • Querying the database File Table shows the information about the stored documents.

Please refer to the article Learn to Store and Analyse Documents on Windows File System with SQL Server Semantic Search Part 1 to complete any of the above missing steps, which are mandatory to cover the walkthrough completely.

Performing Name-Based Documents Comparative Analysis

As we are preparing to perform documents comparative analysis based on their standard naming, at this point it is worth doing a quick check by querying the FILESTREAM Enabled database we set up in the first part of the article.

Run the following script against EmployeesFilestreamSample database:

-- View stored documents managed by File Table to check
SELECT stream_id
FROM EmployeesFilestreamSample.dbo.EmployeesDocumentStore

#sql server #full-text search #semantic search #sql server #sql server 2016

What is GEEK

Buddha Community

Learn to Store, Analyze Documents on Windows File System With SQL Server
Cayla  Erdman

Cayla Erdman


Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Ray  Patel

Ray Patel


Python Packages in SQL Server – Get Started with SQL Server Machine Learning Services


When installing Machine Learning Services in SQL Server by default few Python Packages are installed. In this article, we will have a look on how to get those installed python package information.

Python Packages

When we choose Python as Machine Learning Service during installation, the following packages are installed in SQL Server,

  • revoscalepy – This Microsoft Python package is used for remote compute contexts, streaming, parallel execution of rx functions for data import and transformation, modeling, visualization, and analysis.
  • microsoftml – This is another Microsoft Python package which adds machine learning algorithms in Python.
  • Anaconda 4.2 – Anaconda is an opensource Python package

#machine learning #sql server #executing python in sql server #machine learning using python #machine learning with sql server #ml in sql server using python #python in sql server ml #python packages #python packages for machine learning services #sql server machine learning services

Ruth  Nabimanya

Ruth Nabimanya


System Databases in SQL Server


In SSMS, we many of may noticed System Databases under the Database Folder. But how many of us knows its purpose?. In this article lets discuss about the System Databases in SQL Server.

System Database

Fig. 1 System Databases

There are five system databases, these databases are created while installing SQL Server.

  • Master
  • Model
  • MSDB
  • Tempdb
  • Resource
  • This database contains all the System level Information in SQL Server. The Information in form of Meta data.
  • Because of this master database, we are able to access the SQL Server (On premise SQL Server)
  • This database is used as a template for new databases.
  • Whenever a new database is created, initially a copy of model database is what created as new database.
  • This database is where a service called SQL Server Agent stores its data.
  • SQL server Agent is in charge of automation, which includes entities such as jobs, schedules, and alerts.
  • The Tempdb is where SQL Server stores temporary data such as work tables, sort space, row versioning information and etc.
  • User can create their own version of temporary tables and those are stored in Tempdb.
  • But this database is destroyed and recreated every time when we restart the instance of SQL Server.
  • The resource database is a hidden, read only database that holds the definitions of all system objects.
  • When we query system object in a database, they appear to reside in the sys schema of the local database, but in actually their definitions reside in the resource db.

#sql server #master system database #model system database #msdb system database #sql server system databases #ssms #system database #system databases in sql server #tempdb system database

Michael  Hamill

Michael Hamill


Get Started with SQL Server Machine Learning Services

We know Humans learn from their past experiences. Mean while Machines follow Instructions given by Humans. But what if Human can train Machines to learn from the past data?. In simple, this is what Machine learning is !!!. SQL Server has capabilities of Machine Learning. In this article, we will discuss about the capabilities of Machine Learning in SQL Server.

#machine learning #sql server #machine learning with sql server #ml in sql server using python #python in sql server ml #sql server machine learning services

Learn to Store , Analyze Documents on Windows File System with SQL Server

Being the final part of the article, it is going to take you to the next level of analyzing word documents stored in Windows folders, managed by File Table, and consumed by Semantic Search.

Additionally, the readers are going to gain more understanding of Semantic Search and how to make it work with MS Word documents for analysis.

This article provides a name-based analysis of the documents with equal attention to both theory and practice.


This article assumes the following:

  1. The readers are familiar with the following concepts:
  2. File Table(s).
  3. Semantic Search.
  4. Full-Text Search.
  5. All the prerequisites and **FILESTREAM Enabled EmployeesFilestreamSample **database have been set up.
  6. **EmployeesDocumentStore File Table **has been set up
  7. The following documents have been added to the File Table:
  8. Asif Permanent Employee – Experienced Project Manager.
  9. Mike Permanent Employee – Fresh Programmer.
  10. Peter Permanent Employee – Fresh Project Manager.
  11. Sadaf Contract Employee – Experienced Business Analyst.
  12. The readers can perform some simple Semantic Search operations against the stored documents.

I suggest that you go through the following articles to fill any gaps in your understanding of the above prerequisites and to implement the walkthrough(s) in the final part of the article:

  1. Learn to Store and Analyse Documents on Windows File System with SQL Server Semantic Search Part 1.
  2. Learn to Store and Analyse Documents on Windows File System with SQL Server Semantic Search Part 2.

Part 1 Review

In the first part of the series we learned about the following things:

  1. Setting up a FILESTREAM database.
  2. Setting up a File Table.
  3. Creating MS Word Documents in Windows Folder.
  4. Saving MS Word Documents in a File Table.
  5. Viewing MS Word Document by running a T-SQL script.

Part 2 Review

In the second part of the article we implemented the following things:

  1. Enabling Semantic Search by defining a Full-Text index, a Catalog, and a unique index.
  2. Running Full-Text queries against the stored documents.
  3. Adding more MS Word Documents.
  4. Counting of Documents.
  5. Comparing Permanent vs Contract-based employees.
  6. Adding more details to the names of the documents.
  7. Finding fresh employees’ documents using the name (column) based Semantic Search.
  8. Finding experienced employees’ documents using the name (column) based Semantic Search.
  9. Finding all Project Manager’s documents using the name (column) based Semantic Search.

#sql server #full-text search #semantic search #sql server #sql server 2016