Storage and Management of unstructured data using File Table and File Stream

Introduction: SQL Server provides a special table of files, also referred to as a File Table, which lets an application to integrate its storage and data management components to allow non-transactional access. A File Table is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes. Each row in file table represents a file or directory.  It supports full-text search and semantic search over files and documents. FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. FILESTREAM integrates the SQL Server Database Engine with an NTFS file system.

Steps:

Enable Filestream:

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2012, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
8. If remote clients must access the FILESTREAM data that is stored on this share, select  Allow remote clients to have streaming access to FILESTREAM data.
9. Click Apply.

In Sql server Management studio,click new query to display the query editor and enter the following sql statement:

Create File Stream Enabled Database:

CREATE DATABASE Demo

 ON

 PRIMARY (NAME = Book, FILENAME = 'C:\demo\Book.mdf'),

  FILEGROUP BookGroup CONTAINS FILESTREAM (NAME = BookFS, FILENAME = 'C:\demo\BookFS')

  LOG ON (NAME = 'BookLog', FILENAME = 'C:\demo\BookLog.ldf');       

GO

After execution of the statements, you’ll see the folder as seen in below pic.

Enable Non-Transactional Access at database level

ALTER DATABASE Demo

SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'BookFS')

Create a File Table:

Use Demo

CREATE TABLE BookDocument AS FileTable

GO

I created two text files – collegeText.txt and KidsBook.txt. The text files contain Title,Author,Publish Date and Price. Now, right click on “BookDocument” in the object explorer and select “Explore File Table Directory”, a new window will open. Drop the collegeText.txt and KidsBook.txt files here.

Run the select statement:

SELECT *

FROM dbo.bookdocument

Enable full text search

--Create Full-Text Catalog

Create FullText Catalog demoFulltextSearch as Default

GO

--Create FullText Index

Create FullText Index on dbo.bookDocument

(name Language 1033, File_stream type column file_type Language 1033)

key Index PK__BookDocu__5A5B77D5A5D5A685

on demoFullTextSearch

with Change_Tracking Auto, StopList=system

Go

Execute the following sql:

SELECT *

FROM dbo.bookdocument

WHERE CONTAINS (file_stream, 'c#');

This returns the record of collegeText.txt.