SQL SERVER - FILETABLES

SQL SERVER - FILETABLES

For filetables, lets first enable and configure filestream:

Enable and configure FILESTREAM

1. Start Menu-> All Programs ->Microsoft SQL Server 2012-> Configuration Tools-> SQL Server Configuration Manager.
2. Double click on SQL Server Services.
3. Right click on SQL Server Instance (with which you are working, for example see below image) and click Properties.



4. In SQL Server Properties dialog go to FILESTREAM Tab.




5. I checked all three checkboxes there.
6. Click Apply.
7. Run the below query:

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

8. Restart the SQL Server service

Little bit about FILESTREAM

The FILESTREAM feature allows you to store unstructured data e.g. BLOBs (documents, images, videos etc.) in the file system instead of in the database.

You can enable filestream with 3 different levels of access to FILESTREAM data (see above image):

Checking "Enable FILESTREAM for Transact-SQL access" enables other two options.
In order to enable read and write FILESTREAM data from windows, check the second option namely "Enable FILESTREAM for file I/O streaming access". Also specify the name of the Windows file share.
Remote clients can have access to FILESTREAM data stored in the Windows share by enabling "Allow remote clients to have streaming access to FILESTREAM data". [Give couple of good links related to FILESTREAM]

In point 7 above, we are configuring access level of filestream.
0 is for Disabling FILESTREAM support for this instance.
1 is for Enabling FILESTREAM support for Transact-SQL access.
2 is for enabling full access.

Now let’s run the below query:

Before running the below query I created the Lib folder under C drive.

CREATE DATABASE Library  
ON PRIMARY (NAME = Library_data, FILENAME = 'C:\Lib\Library.mdf'),
  FILEGROUP LibraryFSGroup CONTAINS FILESTREAM (NAME = Library_FS, FILENAME = 
'C:\Lib\LibraryFileStream')
  LOG ON (NAME = 'Library_log', FILENAME = 'C:\Lib\Library_log.ldf');        
GO

After Create Database command, you will find these three below files under C:\Lib



Below directory(LibraryDataFS) will store the files used with the filetable. We can do modifications via Windows applications without running any transactions, so we can enable non-transactional access as below:

ALTER DATABASE Library SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'LibraryDataFS')

Lets create table LibraryBooks:

Use Library Go CREATE TABLE LibraryBooks AS FileTable GO



So below you can see both the directory and the table you created above:



Now that a table is created, let’s use it i.e. try couple of queries with it:



The table is empty yet.

Add two text files in the LibraryBooks folder created above and then run the SELECT query again. It will show you two rows as below (here due to space constraints only two columns are shown)



Added one empty folder also. Run the below query. Instead of querying all the columns we are specifically accessing "name" and "is_directory" column. Now at this moment there are two files and one folder in the table.



Lets change some of the file properties, for example ‘Archive’. As seen below (from properties dialog and query). At present 'Archive' is On (checked).



Select name, is_archive from LibraryBooks


Lets update the archive property for Book2.

update dbo.LibraryBooks set is_archive = 0 where name = 'Book2.txt'



As you can see below the property is updated.



These filetables allow you to store files and other documents (including other folders) in tables in SQL SERVER and you can access them from Windows application as if they are stored in the file system. Filetables gives access to lots of information about the files as well as the contents of the file. We can create full text indexes with this, for example.