Creation Of FileTable in SQL Server 2012
This article describes how to create FileTable in SQL server 2012.
Creating a FileTable
Step 1
Enable FileStream on SQL Server instance. In my previous article I explained how to enable FileStream feature on SQL server instance, you can visit from here- How To Enable FileStream Feature in SQL Server 2012.
Step 2
Now create database when FileStream is enabled.
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'FileTable')
DROP DATABASE FileTable
GO
CREATE DATABASE FileTable
ON PRIMARY
( NAME = FileTable_Primary,
FILENAME =N'D:\FileTable\FileTable_Data.mdf',
MAXSIZE = 50MB),
FILEGROUP FSDataGroup CONTAINS FILESTREAM
( NAME = FileStream,
FILENAME =N'D:\FileTable\FSData')
LOG ON
(Name = FileTable_Log,
FILENAME = 'D:\FileTable\FileTable_Log.ldf',
MAXSIZE = 25MB
)
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTable')
GO
Note: Path should exists in a given directory.
Output:
After execution of above statement, following folder structure will be created in given drive as shown below. Here ".mdf" and ".ldf" and "LearnFileTable" will hold the files created for FileStream data.
Step 3
Now create a FileTable by executing following statement in SQL Server management studio.
USE [FileTable]
GO
CREATE TABLE File_Table AS FileTable
WITH
(
FileTable_Directory = 'MyFirstFileTable',
FileTable_Collate_Filename = database_default
);
GO
Output:
Execute above command and check table is created under FiltTables node.
Step 4
Noe select the record of table.
USE FileTable
SELECT * FROM [dbo].[File_Table]
You can see there is no record in Filetable.
Step 5
Now right click on File_Table and explore it.
Step 6
Now add some files in share location.
Step 7
And again select the table data.
USE FileTable
SELECT * FROM [dbo].[File_Table]
Output: