Video: SQL Server 2008 - Reading FileStream Data

Presenter:Mike Taulty

SQL Server 2008 has a new extension to the varbinary(max) datatype which allows you to store arbitrary large blobs "in the database" from the perspective of data access and backup but the actual data is stored in the filesystem.

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video


Minute 0

Oh and welcomed the shop that he once exhibited as an eight minus m told him with Microsoft in the UK in this video looking at a theatrical final stream essential a vastly inflated it is an extension to far binary max what it allows you to do is store data of arbitrary size in the database so beyond the two gig limit you can store whatever you want it looks like a blob from the point of view of tea sequel and it looks like a blob ad from the bite of your things like backing up and recovering databases and so on but it later is that he stored outside of sequel server in the final system so single server makes it look like relational data when exactly stored in the file system you can access this is relational data using traditional tea sequel methods but you can also walk up to the super server and asked to access the data using a streaming method and grab the data as a files dream we can identify quite little things like I m streaming video is something from sequel server in there at the data looks

Minute 1

On the inside sequel but actually it in the file system you can access it and through the filesystem whilst maintaining transactional integrity and back up and all that kind of stuff so did you visit your shortwood is a reading data from a file stream in this particular video will come back and talk about writing data into a classroom and are subsequently okay I m here is a conservative estimate of the November 60 km open of the script at the first thing he did it in order to use files we must need to switch onto the server at the reserve stock typical SP files drink figure it has this practical than able level it is the and 93 as for Vanity Kingussie zero means off one means that you want to use equal access to files drink too means he want his equal and local machine file access the file stream and 391T sequel local file and remote file access to files so I think Iran list of suitable runners anyway and we consider I m already configured to use files

Minute 2

Trips and is Ltd greater database so I down here is not a create database colour photo deeply I give it a masturbator file and then more importantly I give it a file group called photo deeply affairs which contains a file string in a 20 need to do this you want to store files dreams for a database and essentially conceived as an association here with a folder at folder is where were going to put the files streamed data for this particular data that s so as go ahead and create database using that file group containing a file string orchestra with great ourselves a database and in that database we have a file group that contains a file stream and is going to store the data on de data photos were just wander over to D Day to voters was the already catastrophic that voters folder there is some infrastructure stuff started to appear in their reserves

Minute 3

It was having databases using that storage profiles through to an examiner discredit able that is just move ourselves into a database and will create a table by table near a school photos and two things to notice about this table the one is we have a row identifier which is a row to it you have to have one of these in order to use files strengths and that s not there just saw for Lochore anything like that it s there because you have to have one of those things the other thing to note is that we have this column called photo which is of binary max but it s vile stream and what that means is the data of this will be accessible to tea sequel means they will actually be stored in the file system and it will also be accessible because of the way we configured to serve its use files dreams and through filesystem API is that we can stream the data in and out of this thing that access 20 ways want a relational access and want through filesystem style access which was in a second circle had a

Minute 4

Break that table up out I want to get in data into this table and that a little client which took over the vines in the second which will allow me to serve photographs into the stables under sleaze after a second switch that client as important photograph I am this was good and we ve now got that in that table that is what the client does for associate and the client is holding a meal to see the figure scroll down in the script here we can see that we got something in the table and the photo aspect is just a law but if we go over to the photo storage you can see that things have started to appear in the file system were not really meant to deal with these directly but they started to appear in the filesystem we can see that date has been stored was over here at the foot of functions that we can use when dealing with one of these am files streamed data types clearly we can use a thing as a lobster with that lot here we can use our regular

Minute 5

Sequel to use equal access methods of dealing with blobs but we can also come at the data from a file system perspective so what are things you can do with a file stream datatype you can call this function call pathname and would select that and you see that what we get that looks like as to the Windows files share and you can see that what we get is this some long path at this path we conceded were particular API which will see the second which will allow us to deal with this data as though it was a file on the file system while still preserving transactional abilities of the database in the form of functionally to know about here because this is a need for a way to get the database is transactional capabilities and make sure that we don t find in a workaround was a breakdown to the point where accessing the state as a file stream and the way we can do that it is another function if we did begin a transaction per second and we select this function call get file stream transaction context

Minute 6

Just into what I gives us it is am a little array of binary about the small byte array and we can take that transaction context and we can use it when we call the API Witchell letters open the file stream data as though it was just a file on the file system and it s this that brings together what work you are doing from at a sequel perspective with what will you do from a fasting perspective or a summer to look at how we can build a kind to access our photographs are just inserted into the database of pop over to an visual studio and less likely project in visual studio undiscovered go ahead and make a new project we will just go and put things indeed temp will have a Windows forms application locate that and on my Windows form in the first instance Ltd had a picture box so stay down

Minute 7

Was formed if you like the popular is that a lot of the pitch which is currently living in the database without reading it is a long look at the code behind this at the time of recording a video the only access you get an API that deals with this file streams is through interop to managed code is ongoing to bring into my amp programme here and unmanaged function which is bringing thea deal import the function is called open sequel file stream it takes one of those faster impacts it takes at what kind of access and opening options it needs one of those transaction concepts which saw and the length of that thing and so on I m also bringing in close handle

Minute 8

Really doing this the right way I m using in point I should really be using safe handles by the time it gets net two this is cheap and cheerful if you like an intelligent drop is bringing these two enumerators I wrote as well different face a new profile in him as a class was in constant sin in article constants and them was simply threatened it here with them the access road to know read write read write absent flags were opening the file and day thing that gives me a connection strength to a spot that there was code is with that advice lets get ourselves a connection to the sequel server so that school and make a connection which is bringing that the interest using a connection strength but overnight connection at close of a Madonna

Minute 9

As a command and in Aberaman want to want to put some man to text is second given the connection but is pasted command in here only say what I m doing among the simplicity selecting the top pathname and the top transaction contest with photos supper should begin a transaction around here as well as just go ahead as they can t begin transaction at roughly the transaction but cast out of the point where we are creating command here and at the end of all this is just to try and to commit just to say thank that will do that than is a literate and Kodak term excuses man gets his reader hand them brings back some data from a is just so suitable data reader reader equals man execute reader

Minute 10

As a reader close at the end of dialysate is read readers only have one record then I need to go to get the data back from the staying justifies very of all psychopaths which get out the column 0 I ve got this transaction context of my throat gets that the column 1 and pulling the lengths from that transaction contacts and then what that allows me to do is call my interop function so I can say handle equals open sequel file stream after my path and on about it I want read I m not passing any particular flags transaction context is there to the lens is there and it only took us a rest to this Alex size but it s an elegant handle to that file stream of runs over a spokeswoman done without which the calling close handle on it in the meal meantime we can get a file stream from that solitude

Minute 11

net file stream has great one of those and what has it to the handle out of passive file access read now we have a donna files can let if we can do a simple thing and say image eyes because image from file stream are sorry from stream audio files in the week and there was a picture box 1 image equals I as if we can compile code looks like we can see if we can run code and the scaling mode has been broken on the pitch box model observers to sixes scaling on the pitch box is not really relevant to the demo but it seems wrong not to run not to make it look nice status size mode of an storage image and that s just also make sure that this thing is docked to the sensible

Minute 12

And specified again to lead a political from the database so what we re doing here when we look at code would be similar to the Lillico quite quickly there thought were doing you only look at code is that we are getting that hath names so were not bringing this out as a bull were bringing out a something we can open as a file handle began transaction context we go when we use the interop function to get access to that file and then from here on in the users files stream in order to pool and again at Sotheby s in a subsequent video to keep this one short look at how we can write this data back into the fast and but it s very much the same kind of approach is to see you so will and will have a look of and a second video of figure flavours are how we can access this data and using a mixture of relational and then file streaming can have access to get their cages to summarise that was seen here if you want to use files in any need to switch it on at the server level has a store

Minute 13

To defer that you need to be aware of it as you create or modify your database in that you need a file group which specifies file streams storage where that s posted on the file system and then you need to know about a function called open sequel files dream it s an unmanaged function there is no wrap of the unaware of the spider net in the sequel native client like this that sequel ends the alive 10 dll in order to cull function you will need a pass name for the file you get that by calling the pathname function on the Var binary max files dream datatype in sequel and you saw that we called that you will also need a transaction context you get that by calling get fasting transaction contexts 1 to got those two pieces you will be ought to call API will give you back a file handle and from there on your into reading and writing files at which you can do from net or any other API you might like to am to use clearly because this function

Minute 14

A manager can bring much court from anywhere you want to wear such a video of how we write they to use transactions against this but are now at seven to struck up with that if you want more information on developing single server and as a developer Centre in MSDN its MSN Microsoft com sequel is a more general purpose data access centre at data and if you want more information on this video feel free to mail me at my e mail address is just on screen other than that thanks love listening given it useful and hope to catch up with you I do virtually or in person at a UK event in the future

Click here to go to original video page