SQL with Manoj

In my previous post sometime back I talked about FILETABLES, [link]. This is a new feature introduced in SQL Server 2012, which is built on top of FILESTREAM.

I also talked about how we can use them to store files and retrieve the information from them.

Here, in this post we will see how we can search in the files stored in FileTables using an existing feature i.e. “FullText Search”.

So, let’s check this by an example. I’ll be using the same code from my previous post to create a new Database and Filetables.

–> You will need to create a directory to allocate a location in any of your drive, lets’s create it in D: Drive, D:\SQL_Server2012\FileTables
Now execute the code below, which will create a new DataBase and a FileTable to store documents:

USE [master]
GO

-- Create a new Database with Filestream enabled:
CREATE DATABASE [newFileStreamDB]
 CONTAINMENT = NONE
 ON  PRIMARY ( 
	NAME = N'newFileStreamDB', 
	FILENAME = N'D:\SQL_Server2012\DATA\newFileStreamDB.mdf' , 
	SIZE = 5120KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024KB 
	),
 FILEGROUP newFileStreamGroup CONTAINS FILESTREAM DEFAULT (
	NAME = newFileStreamGroupFiles,
	FILENAME= 'D:\SQL_Server2012\FileTables\Files', 
	MAXSIZE = UNLIMITED 
	)
 LOG ON ( 
	NAME = N'newFileStreamDB_log', 
	FILENAME = N'D:\SQL_Server2012\DATA\newFileStreamDB_log.ldf' , 
	SIZE = 1024KB , 
	MAXSIZE = 2048GB , 
	FILEGROWTH = 10% 
	)
 WITH FILESTREAM (
	NON_TRANSACTED_ACCESS = FULL,
	DIRECTORY_NAME = N'FileTables' 
	)
GO

-- Use the new Database:
USE [newFileStreamDB]
GO

-- Creating a new FileTable
CREATE TABLE [dbo].[firstFileTable] 
	AS FILETABLE ON [PRIMARY] 
	FILESTREAM_ON [newFileStreamGroup]
WITH (
	FILETABLE_DIRECTORY = N'myfirstFileTable', 
	FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AI
)
GO

To check the FileTables link with Drive:
– Goto database in Object Explorer, Right click on the FileTable and click on “Explore FileTable Directory” option. This will open up a shared folder which is on the Virtual Network Name of you computer.
– Copy-Paste some documents/files in the folder above where FileTable is configured.

Check details of files maintained in the FileTable:

SELECT * FROM [firstFileTable]

–> Now we will see how we can search inside those files stored in Folder and in the FileTables:
We will be using the FullText feature of SQL Server to configure it.

-- Creating FullText Catalog:
CREATE FULLTEXT CATALOG CATG_firstFileTable AS DEFAULT;

-- Creating FullText Index on the table and column:
CREATE FULLTEXT INDEX ON dbo.firstFileTable
    (name LANGUAGE 2057,
    file_stream TYPE COLUMN file_type LANGUAGE 2057)
    KEY INDEX PK__firstFil__5A5B77D5C6F38A8E				-- REPLACE this with new PK created
    ON CATG_firstFileTable
    WITH
        CHANGE_TRACKING AUTO,
        STOPLIST = SYSTEM;

Now let’s search inside those files by using FullText Search Functions:

-- Using FREETEXT() function:
SELECT *
FROM dbo.firstFileTable
WHERE
FREETEXT (file_stream, '<Search_String>') -- Put the search string here, which is in your document/file.

-- Using CONTAINS() function:
SELECT *
FROM dbo.firstFileTable
WHERE
CONTAINS (file_stream, '<Search_String>') -- Put the search string here, which is in your document/file.

-- Final Cleanup
USE [master]
GO

DROP DATABASE [newFileStreamDB]
GO

NOTE: You also might need to check what all Document-Filters are currently supported in your SQL Server.
If not, then you can also download some filters from following [link].

-- Check existing Document-Filters:
select * from sys.fulltext_document_types

-- Load Services after installing missing Document-Filters
EXEC sp_fulltext_service @action='load_os_resources', @value=1;
EXEC sp_fulltext_service 'restart_all_fdhosts';

For more on FullText Search check following MSDN link: http://msdn.microsoft.com/en-us/library/ms142571.aspx