Create table with Filestream column in SQL Server 2012
Earlier I have written what Filestream is and how it is useful to us. I have enabled filestream in SQL Server instance and created one database with filestream filegroup. I am going to use same “ExtremeAdviceFileStream” to demonstrate this article. If you don’t have database with filestream filegroup, have a look at my earlier article and get that very short script.
Here is the table which will create one table with column having Filestream attribute.
USE ExtremeAdviceFileStream GO CREATE TABLE Customers ( ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, Name varchar(25), CustPhoto VARBINARY(MAX) FILESTREAM ) GO
Now let us insert one record in above created table with following script
--I have already kept “Ritesh-Teerth.JPG” file in my D drive, --you have to provide your own path and file name here. INSERT INTO Customers VALUES (NEWID(),'Ritesh Shah',(select * FROM OPENROWSET(BULK 'D:\Ritesh-Teerth.JPG', SINGLE_BLOB) AS img)) GO
Now, if I execute simple SELECT statement in Customers table, it would looks like this screen capture.
Please note that you may get different value in ID as well as in CustPhoto fields.
Since we have created our database “ExtremeAdviceFileStream” in D drive, let us see what we can find in Filestream folder.
I have one file in my Filestream folder. I have inserted “Ritesh-Teerth.JPG” file in as a customer image in “Customers” table. I you want to confirm, you can open this file in Paintbrush but this is find just confirm this in development or test environment. This type of manual editing of Filestream datafile may resulted in severe damage in production database so I would like you not to open any file manually in your production server.
Well, this is how you can enable filestream. I will cover few exercises for filestream in coming articles very soon.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.