Demo Transcript: Accessing Documents with SQL Server 2012 FileTable

SQL11UPD03-TSCRIPT-06

This wiki is a transcript of a previously recorded video.

Accessing Documents with SQL Server 2012 FileTable

Hi folks, I’m Greg Low. I’m a SQL Server MVP and part of the Microsoft Regional Director Program. In this demo I just want to quickly show you how to use the FileTable that was added in SQL Server 2012.

First thing I’m doing in this piece of demo code is I’m just creating a folder that’s just going to be used to hold my FILESTREAM data, which is also used for the FileTable. So I’ll execute that. I’m just making sure my database doesn’t already exist. Then the important thing here is I’m saying create a database called ExternalAccess, and I’ve said with FILESTREAM Non_Transacted_Access is full, so I want full access to this via the different mechanisms. Then I’ve suggested that the folder name that will appear logically is called ExternalAccess.

Now this part is exactly the same as if I was dealing with FILESTREAM in the previous versions. Here I can say I want to then add a FileGroup to the database that’s going to be used to hold the FILESTREAM data. In this case I’ve said Alter Database, add a FileGroup called Externalaccess_FG and this contains FILESTREAM. Finally we need somewhere to store the data before that FileGroup so we do that via add file. So I’m using Add File to the FileGroup and saying it’s going to be this folder here that’s sitting underneath that folder external access that we created out in the operating system.

For example, if we dive out here and take a look at this I’ll now see on the C: drive we now have a folder called ExternalAccess. If we look at what’s inside this, this is where the FILESTREAM system has gone off and created its files. Noticed there’s a log file here and a header indicating it’s a FILESTREAM. Further if I drill in we could see inside if there was anything in the log file. This is not how you’re meant to get to the data at all. So what we can do if I then go on use that ExternalAccess database, what we then create the simplest way of now using FILESTREAM is to use a FileTable. If I, say, create a table called ExternalFiles and in this case as FileTable, and I’ve said, look, the folder which will become a logical name is called SharedFiles, this is going to be a file out in the operating system. For that we also need to know what collation to work with. I’ve said FILETABLE_COLLATE_FILENAME, set that to the database_default.

Once I’ve done that, notice that I have a new table called ExternalFiles. At the moment if I do a select we’ll see that there’s nothing in there right now. Let’s put something in there. From the start if I open my machine, which is called KIWI, then notice there are a number of shares exposed out of the machine. In our case the one that’s of interest is the mssqlserver share. This is exactly the same share again used for FILESTREAM. If I pop back here into Object Explorer and looked at the properties of my server then on the advanced settings notice that there’s FILESTREAM and in here I’ve got the access level as full, and the share name is MSSQLSERVER. That’s where that came from. Regardless, let’s go in here and open up the Kiwi folder and we’ll go into that mssqlserver share. Notice the ExternalAccess name that we mentioned is there and the shared file’s name that we used for that folder for that FileTable.

Now once we’re inside here, notice we’re accessing this via our share, let’s create a file. If I say new bitmap image and we’ll give it a name. SmileyFace will do us, and let’s edit that. If I just say edit, let’s do our very best smiley face and you can tell I’m not an artist, but if we do that and save it then this is a standard file in the operating system. Notice if we come back inside SQL Server and I now quiz or query this ExternalFiles table that has now become a file, there was no need for me to go in and start creating entries inside FILESTREAM in a table in SQL Server and then start going off and start streaming data in it. The mere presence of dropping that file into that folder has created the data. We have the StreamID, we have the FILESTREAM which is the actual data itself followed by things like the name, the path, the file type and so on.

You might wonder about these columns. These are a fixed set of columns. If I look in Object Explorer and drill into the external access database under tables, now there’s a new node called FileTables. Let’s open that up and we can see the ExternalFiles table. Let’s drill further into that, open up the columns and you can see that this is a fixed set of columns that has been created and is the same for all FileTables even though we didn’t create that.

What’s particularly powerful about this is it gives us access to a lot of information about the files including the contents of the file. Over the top of this we could then start proceeding on and building things like Full Text indexes or even the new statistical semantics search over the top of what’s there in the FileTable.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)