Advertisements
Home > SQL Server 2012 > SQL Server 2012 (a.k.a Denali) – New feature | FileTables

SQL Server 2012 (a.k.a Denali) – New feature | FileTables


In my [previous posts] I discussed about new features of Denali, now SQL Server 2012.

Here, in this post I’ll talk about a new feature that I explored recently, when I was working with creating tables in SQL Server 2012. I observed a new item in object explorer that took my attention and created eagerness to explore it, and that new thing is FileTables.

As per MS BOL, The new FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.

In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications. The FileTable feature builds on top of SQL Server FILESTREAM technology.
 

–> Let’s see how can we use this new feature by a simple example below:

USE [master]
GO

-- Create a new Database with Filestream enabled:
CREATE DATABASE [newFileStreamDB]
 CONTAINMENT = NONE
 ON  PRIMARY ( 
	NAME = N'newFileStreamDB', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER12RC0\MSSQL\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'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER12RC0\MSSQL\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

 

So, what all objects it creates under this new FileTable:

1. The table is created with following predefined Columns:

2. with Indexes:

3. with Constraints:

4. It also creates a FK reference key:

 

–> Now, when checking under Object Explorer under Tables you won’t see any table (even after refreshing it). But there is a new folder named FileTables, expand it and you can see this table there, shown below:


 

–> Right click on it and select ‘Explore FileTable Directory’, it will open the folder as shown below:

Here, I’ve manually created 3 files, 1 notepad, 1 powerpoint and 1 word doc. You can also copy files from other locations and paste/drop them here. As you paste/drop files here, SQL Server internally updates the [firstFileTable] file table.
 

–> We can check the table by simply issuing SELECT statement on the table:

SELECT * FROM dbo.firstFileTable

-- Final Cleanup
DROP TABLE dbo.firstFileTable
GO

 

I liked this new feature very much as this will ease the work and reduce the overhead of maintaining files with databases.
 

In my [next post] you can check how you to use Full Text Search with files stored in FileTables.


Advertisements
  1. March 23, 2015 at 7:29 pm

    @Sleva, thanks for your comments.

    The NON_TRANSACTED_ACCESS option allows you to show/hide the option to Explore File Location from SSMS by right clicking on the FileTable.

    Sorry, for responding so late, I just check your comment today only!

  2. August 24, 2013 at 3:05 pm

    Hi Manoj

    Can you please elaborate what is NON_TRANSACTED_ACCESS=Full option.

    Thanks

    Selva

  1. January 22, 2016 at 6:39 pm
  2. October 23, 2015 at 5:22 pm
  3. October 23, 2015 at 3:05 pm
  4. December 24, 2013 at 12:18 pm
  5. December 21, 2012 at 4:44 pm

Leave a 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: