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:
- Use FileTable
- 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