With the new SQL server 2012 , Microsoft has packed a whole new bunch of features ! One among them is the ‘FileTable’ . The name it self suggest it all ! As per MSDN :
SQL Server provides a special table of files, also referred to as a FileTable, for applications that require file and directory storage in the database, with Windows API compatibility and non-transactional access. A FileTable 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.
But there are some PER-REQUISITES for using this FileTable :
- FILESTREAM should be enabled at the Instance level
- FILESTREAM FILEGROUP should be provided at the database level
- Enable Nontransaction access at the database level
- Specify directory for FileTable at the database level
Below mentioned steps would enable you to start using FILETABLE:
Now, enable FILESTREAM on the instance level. Execute the following command and see if the FILESTREAM feature is already enabled.
select value,value_in_use from sys.configurations where name like 'filestream access level'
If the value is 0, then FILESTREAM support is disabled for that instance. If the value is 1 then FILESTREAM is available for TSQL access. If the value is 2, then FILESTREAM access is enabled both for Transact SQL access and Win32 streaming access.
Create folder E:\MyFiles.
Now we need to enable the next three per-requisites, its easy to do it in a script instead of creating a database and altering the properties. So, let us create the database with FILESTREAM File group and enable it in Non Transaction level. Also we could provide the folder location for the FILESTREAM folder.
/****** Object: Database [MyDB] Script Date: 5/23/2012 4:25:56 PM ******/ CREATE DATABASE [MyDB] CONTAINMENT = NONE ON PRIMARY ( NAME = N'MyDB_Data', FILENAME = N'E:\Data\MyDB_Data.mdf' , SIZE = 2112KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [MyFilestreamFG] CONTAINS FILESTREAM DEFAULT ( NAME = N'MyFileStreamData', FILENAME = N'e:\myfiles\Data' , MAXSIZE = UNLIMITED) LOG ON ( NAME = N'MyDB_Log', FILENAME = N'E:\Data\MyDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 110 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [MyDB].[dbo].[sp_fulltext_database] @action = 'enable' end GO
Now, we shall create the table as Filetable, by using the following Transact SQL statement.
Use MyDB go CREATE TABLE MyDataFiles AS FileTable WITH ( FileTable_Directory = 'MyDataFiles', FileTable_Collate_Filename = database_default ); GO
This will cretae a folder MyDataFIles under the default filestream location :
You can query the filestream options defined for every database using the following TSQL query.
select db_name(Database_id) as DBName,* from sys.database_filestream_options
These are the things you could experiment with initially :
Now let us try to query the table that we created as File Table, using the following query.
Use MyDB go select * from MyDataFiles
Let’s copy some files to the MyDataFiles folder under the FILESTREAM share. (* the path i had specified above)
Now let’s try to query the table that we created as File Table using the following query.
select stream_id, name,file_type, last_access_time from MyDataFiles
This query will list all the files that you have copied to the folder. If you want to update these physical files, you could do even that. For instance renaming of a file could be done like this :
UPDATE MyDataFiles SET name = 'RenameTextFile.txt' WHERE stream_id = '4F464AB6-18A5-E111-BDE0-00505694001D'
If you delete a row from the table, it will delete the actual physical file as well. Execute the following TSQL command and check it out:
delete MyDataFiles where name='data.csv'
Thus an entire new concept / feature has been introduced in SQL Server 2012 to deal with files and directories.
You could explore more from MSDN articles here. Happy exploring !