Create table with Filestream column in SQL Server 2012

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.

[sourcecode language=”sql”]
USE ExtremeAdviceFileStream
GO

CREATE TABLE Customers
(
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
Name varchar(25),
CustPhoto VARBINARY(MAX) FILESTREAM
)
GO
[/sourcecode]

Now let us insert one record in above created table with following script

[sourcecode language=”sql”]
–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
[/sourcecode]

Now, if I execute simple SELECT statement in Customers table, it would looks like this screen capture.

1SELECTQuery

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.

2FilestreamFolder

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.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Create table with Filestream column in SQL Server 2012”

Comments are closed.