Filestream and Full-Text: A Full Solution for Document Indexing in SQL Server

First published on MSDN on Oct 23, 2017

Updated: Oct 19, 2021

Recently someone was asking if there is a simple SQL Server solution where you would generate text documents and simply pass them to SQL Server to catalog without writing an application to do this. Also, would like to be able to index and search the context of these documents.

The solution in SQL Server would involve Filestream and Full-Text technologies. There are two alternatives:

1. Use FileTable
2. Use a regular table

 

1. Using a FileTable

1. Enable Filestream: Go to SQL Server Configuration Manager, properties on the SQL Server service and under FILESTREAM enable FILESTREAM for T-SQL and file I/O.

 

 

2. Configure FILESTREAM in SQL Server

 

use master;
exec sp_configure 'filestream access level', 2

--AT THIS POINT, YOU NEED TO RESTART SQL SERVER

 

 

 

3. Restart SQL Server service 

 

4. Create a database with a FILESTREAM filegroup

 

DROP DATABASE FileTableDB_forTextDocs
go
CREATE DATABASE FileTableDB_forTextDocs
ON PRIMARY (
 NAME = N'FileTableDB',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\FileTableFileTableDB.mdf' ),
 FILEGROUP FilestreamFG CONTAINS FILESTREAM (
 NAME = FileStreamGroup1,
 FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\FileTableData' )
LOG ON (
 NAME = N'FileTableDB_Log',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\FileTableFileTableDB_log.ldf' )
 WITH FILESTREAM (
 NON_TRANSACTED_ACCESS = FULL,
 DIRECTORY_NAME = N'TextesFileTable')
go

 

 

5. Validate the database with directory name FILESTREAM options

 

SELECT DB_NAME ( database_id ), 
 directory_name, non_transacted_access, 
 non_transacted_access_desc
FROM sys.database_filestream_options
WHERE directory_name is NOT NULL
GO

 

 

6. Create a FileTable in the database

 

use FileTableDB_forTextDocs
GO
CREATE TABLE dbo.TextDocuments AS FileTable
 WITH (
  FileTable_Directory = 'TextesFileTable',
  FileTable_Collate_Filename = database_default);
GO

 

 

7. Validate the table is created successfully and check its contents (no rows)

 

SELECT * FROM sys.filetables;
GO
SELECT * FROM sys.tables WHERE is_filetable = 1;
GO
--check if there is something in there - NOTHING YET
SELECT * FROM dbo.TextDocuments
GO

 

 

8. Find the location of the filetable root path (DIRECTORY_NAME).

It will look like this: \\SERVERNAME\WINDOWS_SHARE\TextesFileTable\TextesFileTable

 

 

SELECT FileTableRootPath('dbo.TextDocuments');

 

 

9. Using the share you discovered with FileTableRootPath as a destination path, create several files.

  9.1 Run these from Command Prompt

 

 

echo 1. this is a some text1 > \\SERVERNAME\SQL2017\TextesFileTable\TextesFileTable\TextFile1.txt
echo 2. this is a some text2 > \\SERVERNAME\SQL2017\TextesFileTable\TextesFileTable\TextFile2.txt
echo 3. this is a some text3 > \\SERVERNAME\SQL2017\TextesFileTable\TextesFileTable\TextFile3.txt

 

 

  9.2 Let's add a file using PowerShell

 

 Add-Content -Value "This is some text 4" -Path "\\SERVERNAME\SQL2017\TextesFileTable\TextesFileTable\TextFile4.txt"

 

 

  9.3 Let's create a file in the \Documents folder of your user profile directory and then copy it to the FileTable directory

 

echo this is some text5 > %userprofile%\Documents\TextFile5.txt

copy %userprofile%\Documents\TextFile5.txt \\SERVERNAME\SQL2017\TextesFileTable\TextesFileTable\

dir \\SERVERNAME\SQL2017\TextesFileTable\TextesFileTable\

 

 

10. IMPORTANT:  Make Sure You Close The Share Because Keeping It Open In Windows Explorer Can Cause Blocking

 

11. Now check the FileTable to see if the files are tracked - should see 5 records

 

SELECT * FROM dbo.TextDocuments

 

 

12. Now to create a Full-Text index on this table, first create the catalog

 

CREATE FULLTEXT CATALOG [FTCat1] WITH ACCENT_SENSITIVITY = ON

 

 

13. Get the name of the primary key (PK) from the table. The PK names will look something like PK__TextDocu__5A5B77D5760F9CB8. 

 

exec sp_helpindex TextDocuments

 

 

14. Create the FT index on the file_stream column

 

CREATE FULLTEXT INDEX ON [dbo].[TextDocuments] (file_stream TYPE COLUMN [name]) 
KEY INDEX [PK__TextDocum__5A5B77D58E492DCE] -- name from previous step
 ON ([FTCat1]) WITH (CHANGE_TRACKING AUTO)

 

 

15. Now search using Full-Text Contains() clause

 

SELECT * FROM dbo.TextDocuments
WHERE CONTAINS(file_stream, 'test')

 

 

2. Using a regular table

16. Create a simple text document in a local folder.  Go to Command Prompt and run a command like this:

 

echo The sea and sunshine are beautiful today > c:\temp\NewTextDocument.txt

 

 

17. Using the same database created earlier, let's create a regular table with FILESTREAM

 

IF OBJECT_ID('TextDocumentsRegular') IS NOT NULL
  DROP TABLE TextDocumentsRegular
GO
CREATE TABLE TextDocumentsRegular(
Guid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
documentID INT NOT NULL ,
documentType VARCHAR(10) NOT NULL,
TextContent VARBINARY(MAX) FILESTREAM NOT NULL ,
dateinserted DATETIME)

 

 

18. Insert a row into the table using the newly-created document

 

INSERT INTO TextDocumentsRegular (documentID, documentType, TextContent, dateinserted)
SELECT 101, '.txt', *, GETDATE() FROM OPENROWSET(BULK N'C:\temp\NewTextDocument.txt', SINGLE_BLOB) rs

 

 

19. Check if the row got inserted, pointing to the document

 

SELECT * FROM TextDocumentsRegular

 

 

20. Get the primary key or unique key name from the table

 

 

exec sp_helpindex TextDocumentsRegular

 

 

21. Create a full-text index using the unique key discovered with above command

 

CREATE FULLTEXT INDEX ON [dbo].TextDocumentsRegular
(Textcontent type column documentType) 
KEY INDEX UQ__TextDocu__A2B5777D1D53476D -- name from prior step
ON ([FTCat1]) 
WITH (CHANGE_TRACKING AUTO)

 

 

22. Search using Full-Text CONTAINS() clause

 

 

SELECT * FROM TextDocumentsRegular
WHERE CONTAINS (TextContent, 'sunshine')

 

 

 

Namaste,

Joseph