Creation Of FileTable in SQL Server 2012

This article describes how to create FileTable in SQL server 2012.
  • 2778
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:

output-in-sql.jpg

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.

FILESTREAM.jpg

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:

output-in-sql.jpg

Execute above command and check table is created under FiltTables node.

filetable.jpg

Step 4

Noe select the record of table.

USE FileTable

SELECT * FROM [dbo].[File_Table]

 

You can see there is no record in Filetable.

empty file table.jpg

Step 5

Now right click on File_Table and explore it.

explore file table.jpg

Step 6

Now add some files in share location.

add file in share location.jpg

Step 7

 And again select the table data.

USE FileTable

SELECT * FROM [dbo].[File_Table]

 

Output:

filetable with data.jpg


Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.