Two in Two Days

It’s a busy week for me. I’ve got quite a few articles to review, feedback to write for Stairway Series authors, review and changes of some PowerPoint decks for later this month, and two User Group presentations.

This is on top of a busy first week of school in the household. I feel like I’m playing catch up all week.


I’ll be at the Boulder SQL Server User Group tonight. My presentation will be on Unstructured Data in SQL Server, looking at Filestream and Filetable and how they can be setup and used.

Fortunately I’ve done this before, and a little practice this week was enough to get me ready.

Hopefully I’ll see a few of you there as I haven’t been to Boulder in over a year.

Denver SQL

The Denver SQL Server User Group usually asks me do a presentation or two each year and this time I have a new one. They get to be my guinea pigs for the first delivery of this talk.

Get Testing with tsqlt, a preview of a talk I’ll be doing at SQL in the City, is on the agenda. I’ve been going over this one a few times this week, so hopefully it goes smoothly.

Managing Data in a FileTable with T-SQL

I wrote a post about creating a Filetable, which just covered the basics of how to build one. How do you work with the data in this table? In this post I’ll look at a few things you can do from the T-SQL side.

From the last post, I had my author drafts Filetable. I can see this in the Object Explorer.


I can use the same “select data” feature from Object Explorer on a Filetable, just like any other table.


I get the results, and as you see, I have a few rows in the table.


These are actually the files I see in the share.


Inserting Data

One of the advantages of Filetable is that you can use Explorer (and any tools that use the same Explorer APIs) to move data in and out of a table. However that doesn’t preclude you from using T-SQL.

I can use a script to insert data into the table, just as I might with Filestream.

INSERT INTO AuthorDrafts(name, file_stream) Values ( 'circle.jpg' , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9 ); go


As you can see, this command works fine:


If I then look at the share, I see my file:


Retrieving Data

As shown above, I can use SELECT queries to return data from a Filetable in T-SQL. However, I have a share as well, and I can cut, copy, paste, and open files from the share just as I would any other file in the file system.



If I open the file in Paint, I see my image:



Working with files in a file table is easy, and while many people will use Explorer functions, you can use T-SQL as well to insert, or retrieve the data as you choose.

Adding Data to Filestream with T-SQL

I’ve written on how to enable Filestream and how to add a filegroup, but I haven’t touched the Filestream impact on your tables. This post will look at the table side of Filestream and how you get your binary data into the database.

Let’s assume you have a filestream enabled database. If you look at my File and Filegroups post, you’ll see how to do this. Given that, we now need to create a table and add data to it.

Creating a Table with Filestream

Filestream doesn’t have a separate entity like Filetables. Instead, the Filestream attribute is added to a column in a table, much like the identity property.

I’ll create a simple table that holds Filestream data.

  , name VARCHAR(20)  
  , jpg varbinary(MAX) FILESTREAM

Here we are creating a basic table that has an ID and name, which are essentially meta data for our filestream data. You could have any amount of information in this table, but the Filestream data is stored in the jpg field, where we have a varbinary(max) datatype, and the FILESTREAM attribute on the table.

I could have a more complex schema, such as the Production.Document table in Adventureworks2008 (shown below)

CREATE TABLE [Production].[Document](
    [DocumentNode] [hierarchyid] NOT NULL,
    [DocumentLevel]  AS ([DocumentNode].[GetLevel]()),
    [Title] [nvarchar](50) NOT NULL,
    [Owner] [int] NOT NULL,
    [FolderFlag] [bit] NOT NULL,
    [FileName] [nvarchar](400) NOT NULL,
    [FileExtension] [nvarchar](8) NOT NULL,
    [Revision] [nchar](5) NOT NULL,
    [ChangeNumber] [int] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [DocumentSummary] [nvarchar](max) NULL,
    [Document] [varbinary](max) FILESTREAM  NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL


Adding Data

To add data to this table, we have a variety of choices using T-SQL. It’s possible to use an application, and pass in parameters that are streamed to the table, but for the simplicity of this example, let’s show two ways in T-SQL.

First, let’s look at a direct insert. I can CAST my data to varbinary, and then insert it into the table. I have a small image that looks like this:


It’s a simple circle, very small. The actual data in this image is this:

0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4 D002A000000080004011A0005000000010000003E011B00050000000100000046 01280003000000010002000001310002000000120000004E00000000000000600 000000100000060000000015061696E742E4E45542076332E352E313000FFDB00 43000201010201010202020202020202030503030303030604040305070607070 706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C 0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0 C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C 0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F000001050 1010101010100000000000000000102030405060708090A0BFFC400B510000201 0303020403050504040000017D010203000411051221314106135161072271143 28191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435 363738393A434445464748494A535455565758595A636465666768696A7374757 67778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3 B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E 8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F01000301010101010101010100000000 00000102030405060708090A0BFFC400B51100020102040403040705040400010 277000102031104052131061241510761711322328108144291A1B1C109233352 F0156272D10A162434E125F11718191A262728292A35363738393A43444546474 8494A535455565758595A636465666768696A737475767778797A828384858687 88898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C 4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9 FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03 FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C1 8940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C 59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87 BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007 FFFD9

I can insert this data directly into my table:

INSERT INTO FSDemo(ID, name, jpg) 
Values (NEWID()
      , 'circle.jpg'
      , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9

The other method I can show is a direct insert, streaming from the OPENROWSET command. Here I’ll pic an image on my system and insert it.

INSERT INTO FSDemo(ID, name, jpg) 
Values (NEWID()
      , 'uma.jpg'
      , (SELECT * FROM OPENROWSET(BULK N'C:\Users\Steve\Documents\SampleData\Uma.jpg', SINGLE_BLOB) AS CategoryImage)

Now if I query the table

  id, name, jpg
 from FSDemo;

I see my data.


When I look at this in Management Studio, I see the actual hex data, which isn’t easy to understand. I need another way to extract this data and render it. However I can show one more quick demo here. Let me take an XML document and insert it:

declare @list XML
select @list = '
  <Groceries Store=''Safeway''>
  <Drinks Store=''Tipsys''>
    <Drink>Fat Tire</Drink>
    <Drink>Klinker Brick Cabernet</Drink>
    <Item>electric fence ribbon</Item>
INSERT INTO FSDemo(ID, name, jpg) 
Values (NEWID()
      , 'sample.xml'
      , CAST( @list AS VARBINARY(MAX))

Once this is done, the data is in binary, as I see from the selection from the table.


However if I cast this result back, I get the data as expected.


Hopefully this helps you to understand how to insert data into a Filestream table. In another post, I’ll look at how you can extract this data for rendering.

Filestream and Filetable in SQL Server

This talk is an update of my Unstructured Data in SQL Server talk that looks at the Filestream and Filetable features of SQL Server.

This session begins by presenting a case why we will manage more binary (unstructured data) in the future. The growth of this data is amazing. From there we delve into the basics of the three main types of data:

  • structured
  • semi-structured
  • unstructured

The session examines the basics of Filestream and how you get enable this feature, administer it from a DBA perspective, and move data in and out of Filestream columns.

Filetable is introduced along with demos on how to enable this data, as well as a brief search demo to extract information from documents.

Level: 200

Length: 60-75 minutes

Demo Code:


Related Blogs:

Presentation Schedule

This session has been given at these places.

Apr 9, 2013 – SQL Intersection Spring 2013

T-SQL Tuesday #40– File and Filegroups

tsqltuesdayIt’s the second Tuesday of the month and time for T-SQL Tuesday again. This is a monthly blog party, where the participants write on a particular theme. This month Jen McCown, of Midnight DBA fame, invites us to talk about files and filegroups in SQL Server.

If you’re like to participate, write a post and drop a comment (or pingback) on Jen’s blog. Watch the #tsql2sday hashtag on twitter for next month’s invitation.

Filestream and Filegroups

I have a couple talks that deal with Filestream related topics, so I decided on a quick introductory lesson on how this works.

Filestream was built into the AdventureWorks 2008 sample database. Requiring administrators to turn on Filestream caused some confusion and complaints, despite the fact that it’s easy to do.

What does Filestream have to do with filegroups? In a database that is enabled for Filestream data, you need to add a filegroup specifically for the Filestream data. This is actually a folder on your file system, which you can access through T-SQL, or through the Win32 API. If you are using SQL Server 2012 or later, you can also access this data with a Filetable, which is built on Filestream.

Let’s create a database, and add a filegroup for Filestream. We start with the “New Database” dialog in SSMS.


With the normal defaults, we see a data file (FS_Test) and a log file (FS_test_log). For Filestream data, we need a new place to store it. Let’s add a file:



Once I add the file, I mark it as storing Filestream data. The other options are rows (data files) or log files. However this presents a problem. When I scroll right, I see that there is no filegroup for Filestream data. I can’t put this in an existing filegroup.


Let’s add one of those. Here’s the default filegroup dialog.


I can click add, and put in a filestream filegroup. The name doesn’t matter, it’s just for administrative purposes. Once I do that, I can go back to the files dialog, and if I select the dropdown, my new filegroup appears.


Now I need a location. Outside of SQL Server, I created a folder in my data directory. This can be anywhere, but I did it in the default location. It’s called FilestreamDataTest.


I then select this in the files dialog, using the ellipsis to the right of the Path column.


I see my folder in the file picker and choose it.


Once I’ve selected it, I don’t click OK. I click “Script” and get the script below:

( NAME = N'FS_test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FS_test.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
( NAME = N'fs_test_fsdata', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FilestreamDataTest\fs_test_fsdata' , MAXSIZE = UNLIMITED)
( NAME = N'FS_test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FS_test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

I can run this, and once I do, if I go into the folder that contains my Filestream file, I see this:


As I create tables that hold Filestream data, including FileTable data, I’ll see entries in here for each column (or Filetable) that holds this data. There is a folder that holds logging information for this data, which I do not manage.

Hopefully this is a quick, short piece that helps you understand Filegroups and Filestream.

Create a Filestream Filegroup for Filetables – SQL Server 2012

Once you’ve enabled filestream, the next step is to add a filegroup to your database to hold the filestream data. This is pretty easy to do, and I’ll show you the SSMS and code versions.

If you want to know more about these Filestream containers, you can read BOL. Let’s create a simple database:

-- create a new database
create database UnstructuredData

This is a simple database with my instance defaults in place. It has a single mdf, a single ldf, and the default Primary filegroup. Let’s not add a new filegroup:

-- add a filestream FG
ALTER DATABASE [UnstructuredData]

Here I am adding the filegroup (empty) and specifying this as a filestream container. You cannot mix Filestream data and non-Filestream data in the same filegroup in SQL Server 2012.

To add a file, we can use the ALTER DATABASE command:

-- Add a file to the Filestream FG
ALTER DATABASE [UnstructuredData] 
  ADD FILE ( NAME = N'UnstructuredFS', 
             FILENAME = N'c:\fs\UnstructuredFS' ) 

Here I am adding a file, which is actually a folder in this case. According to the documentation, the path up to the last folder (c:\fs in this case) must exist, but the last folder (UnstructuredFS) must not.

You could do all of this in one statement, as shown here:

CREATE DATABASE [UnstructuredData]
( NAME = N'UnstructuredData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UnstructuredData.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
( NAME = N'UnstructuredFS', FILENAME = N'c:\fs\UnstructuredFS' , MAXSIZE = UNLIMITED)
( NAME = N'UnstructuredData_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UnstructuredData_log.ldf' , SIZE = 784KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

This gets you a space for holding your Filestream data. In 2012, you can now have more than one file for Filestream data, so you can separate out your filegroup across different physical locations if you have a need to do so for performance or scalability.

In the next post, I’ll build a FileTable and store some documents in it.

Unstructured Data in SQL Server


More and more of our data does not fit neatly into a structured, relational model of rows and columns of data. In this session, you will learn about how SQL Server stores unstructured data, with a special emphasis on how to use Filestream, which integrates SQL Server with the NTFS file system by storing varbinary(max) binary large object data as files stored within the file system. You will also learn about the new SQL Server 2012 filetable feature, which builds on Filestream and provides the ability to read, write, and update Filestream objects directly through the file system. This session is designed for DBAs and developers who need to learn how to manage large quantities of unstructured data.

This covers SQL Server 2008, R2, and 2012. The basic Agenda:

  • What is unstructured data
  • Filestream
  • FileTable

There are demos that look at how Filestream works and how FileTable can be used in SQL Server 2012.

Level: 200

Length: 60 Minutes

Demo code:

Slides: UnstructuredData.ppt

Related Posts:


You can view my speaking schedule here:

Enabling Filestream in SQL Server 2012

Filestream is a cool feature, albeit one that’s cumbersome to use in SQL Server 2008 and R2. However the FileTable feature in SQL Server 2012 builds on Filestream and you must enable this feature for FileTable to work.

There is a good document in BOL about this. It basically has you doing a few different things. The first step is to enable the filestream access from outside SQL Server using the SQL Server Configuration Manager. When you start the manager, right click the database service and select properties.


The database engine has to allow for the access to the file system, so this allows that integration. Typically a Windows administrator is required to dot his.

Once that is complete, you will see the account properties for the service. What we want to do is change to the FILESTREAM tab, shown on the bottom row to the right.


On this tab, we can enable Filestream only for SQL Server, for I/O access as well, and specify a share. For Filetable we need to enable both levels of access and create a share name. I chose “SQLFS” for my share name.


Once this is done, you need to switch to Management Studio and then right clicking the instance and selecting properties.


This will bring up a series of selections. If you choose the “Advanced” item on the left, you will get a list of properties for the instance. Near the top, there is the FILESTREAM section. Below I have dropped down the choices. By default, this is disabled, and for FILESTREAM you can select either of the other options, but FileTable needs the full access.


Once this is done, you need to restart the instance to enable the Filestream for the SQL Server. This doesn’t set up FILESTREAM in any of your databases; this merely enables it for the instance. You need to still create the FILESTREAM filegroups in any database that will use FILESTREAM data.