Working with Files through MS-SQL 2012 onwards

Hi Folks,
The primary purpose of this article is to push developers to use modern techniques of working with files. Usually in our applications whenever we work with files, we follow the orthodox way. Say there is requirement where user wants to upload a document what we do is Create a table in our SQL database, something like below:

fileId userId filePath fileCreatedOn
14 45 D:\root\myApp\Files\user001.jpg 2017-02-28 11:39:45

Off course we can add more columns as per the requirements but lets focus on filePath and fileCreatedOn (which is nothing but getdate() at the time of insert). So these are the only two columns which give us information about our file. what if we need more than that ?

The Microsoft SQL 2012 has introduced File Table feature where SQL Server internally uses Windows API to store the file at the physical location as well as it stores all the details (like attributes) of the files in tabular format for our T-SQL querying. Behind the scenes SQL Engine uses the famous File Stream classes to store and access the information. The plus point is we get lot of extra attributes associated with the files which can be later used for extension of any functionality. So lets get started to work with File Tables in MS SQL Server. Here are the steps to follow:

First we will have to enable File Stream and complete some pre-requisites:

  1. Enable File Stream for the current instance.
  2. Assign a File Stream file group to database.
  3. Provide Non-Transnational access to the database.
  4. Specify directory for File Tables.

For above four steps, we will have to execute the following steps:

  1. Go to SQL Server configuration manager. Select SQL Server Services right click on the Instance Name and select Properties Follow the screenshot below:SQLServerProps.PNG
    Restart the SQL Service once you set these properties.


  2. Create a new database and go to its properties. In case if Database is already created then directly select the database and go to its properties and go to Files.Add new Database File. I named my file as FileTableDB_file. In adjacent column select FileType as FILESTREAM Data

  3. Go to Filegroups Add a new Filegroup under FILESTREAM group box. I named it as FilePrimary

  4. Now we will have to map this filegroup to our newly created Filestream Data file. So lets quickly go back to Files tab and go to FileTableDB_file and select the Filegroup from the dropdown as FilePrimary. Refer to the Screenshot below:DBFile.PNG

  5. Next step before we Save and Exit is go to Options and punch in the Directory Name and Non-Transacted Access Refer to the Screenshot below:DBOptions.PNG

We are done configuring the system for File Stream. Now let’s explore the functionality.


Creating a new File Table:

CREATE TABLE MyFileTable AS FileTable
WITH
(FileTable_Directory = 'MyFileTable_Dir');
GO
Now we go ahead and run a SELECT query on this table then we shall see the list of columns which will hold all the information regarding the document. The table is going to be empty for now. So lets fill it up.There are two methods from which we can insert the data.
  1. Move the file to the specified directory. The movement can be done either programmatically or simple copy-paste.
  2. Bulk copy files using T-SQL statements. (INSERT SQL query).

Let’s explore the option 1.

Right click on the table name to click on Explore FileTable Directory. This will browse to a localhost network location where  MyFileTable_Dir is created. Currently its empty. So I will copy two files in this location. Screenshot below:

DbFileTableAndDirectory.png
One is the MS word file and other is PPT file.
Note: MS SQL Server saves file references located on a specified path .It doesn’t actually store the files.
Now Lets go back SQL Server and fire a SELECT query on same table. You will see the data !

Now let’s explore the option 2.

Assume that you have files kept it in some other location which now you would want to import it into SQL tables.So I am going to import file from my F:\testFiles\testfile1.txt

INSERT INTO [dbo].[MyFileTable] ([name],[file_stream])
SELECT
'NewFileFromSQL1.txt', * FROM OPENROWSET(BULK N'C:\testFiles\NewFileFromSQL2.txt', SINGLE_BLOB) AS FileData
GO
If you will see that data is now inserted in the table like below. Also it will create a new file named ‘NewFileFromSQL1.txt’ on the physical location.
insfile


Note:
Before we conclude this article, one more quick topic I would like to cover. The core purpose of File Tables are to work with existing files but in certain scenarios you would also want to create files on the go. The MS SQL Server covers this requirement as well. One can always create a file using INSERT QUERY.

INSERT INTO [dbo].[MyFileTable] ([name],[file_stream])
SELECT
'NewFile.txt', CAST('THIS IS TEST FILE.' AS varbinary(20))
GO
This will create a new txt file named “NewFile.txt” at the physical location. We may use this feature to write simple Exception logs, etc.

Conclusion

This feature of MS SQL Server is useful in applications which required functionalities like Full Text Search, Maintaining files and it’s version controls,  document repositories etc.

There is lot about File Tables than we have covered but I hope I atleast got you interested in the topic.  Thank you very much for reading, All the best and Happy Coding!


– GauranG

2 thoughts on “Working with Files through MS-SQL 2012 onwards

Leave a Reply to Gaurang Naik Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s