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.

Introduction to Filestream in SQL Server 2012

Introduction to Filestream in SQL Server 2012

Filestream feature is not new in SQL Server 2012, it is introduced in SQL Server 2008. Last week I visited one company where they are storing some document in physical disk on server and stores its location in database with appropriate records. This is one of the very old approach and nothing wrong in it but problem starts when you decide to change location of folder because of any reason. You have to update all pointers of location stored in database. Apart from this problem, you have to take manual backup of document folder as it won’t come along with SQL Server database backup.

You can have another approach too, you can store documents / images etc. in VARBINARY fields in SQL Server It creates an issue of performance though It saves you from taking manual backup of document because when you take backup of database, you will have all your document inside the backup in form of VARBINARY field but still performance is a big issue in this case. Apart from that there is one more limitation that we can’t store file / document which is bigger then 2GB.

I have written articles about how to use BLOB (Varbinary) fields.

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C# (Click Here to read more)

Insert and Update image field in SQL Server 2008/2005 (Click Here to read more)

Filestream solve your problem regarding performance and backup. Filestream can store document / image / video / music etc. in NTFT file system though it is accessible right from from the SQL Server as well as from the WIN32 API and .NET class. You will gain performance if you access your filestream data from .NET class or from WIN32 API, by doing this, you can reduce overhead from SQL Server. When you will take full backup of database, you can have all your filestream files in backup at the same time you are eliminating 2GB limitation of VARBINARY field.

There are some disadvantages hovering around filestream too like mirroring doesn’t support filestream data but you can ignore this because mirroring itself deprecated from SQL Server 2012 version. Apart from that there is one more major disadvantage which is you can’t apply Transparent Data Encryption (TDE) on filestream data. These are two major disadvantage but I can ignore it as I will have less overhead on my SQL Server due to filestream, my data backup issue get solved and I wouldn’t have limitation of 2GB. These are the main advantage which force me to choose this feature.

You can enable filestream while installing SQL Server instance but if you have not enabled it at the time of installation, let us see how can we enable it in our existing SQL Server instance.

1.) Open “SQL Server Configuration Manager” from “Start | All Programs | SQL Server 2012 | Configuration Tools “. Once you have “SQL Server Configuration Manager” open, move to the SQL Server instance, right click on it and click on “Property”.

1SQLServerConfigurationManager

2.) Once you click on “Property”, you will get instance’s property. Move to the “FILESTREAM” tab and follow the setting which marked in red rectangle in following screen capture.

2FilestreamProperty

Once you setup property, open SQL Server Management Studio (SSMS) and execute following script to allow access to TSQL as well as file I/O for filestream.

[sourcecode language=”sql”]
EXEC sp_configure Filestream_access_level,2
GO
RECONFIGURE
GO
[/sourcecode]

Once you setup permission for filestream at instance level, let us create one database with filestream enable filegroup.

[sourcecode language=”sql”]

CREATE DATABASE ExtremeAdviceFileStream ON PRIMARY

(

NAME = ExtremeAdviceFileStream_data,

FILENAME = N’D:\TestDB\ExtremeAdviceFileStream_data.mdf’

),

FILEGROUP ExtremeAdviceFileStream_FS CONTAINS FILESTREAM

(

NAME =ExtremeAdviceFileStream_FILESTREAM,

FILENAME = N’D:\TestDB\ExtremeAdviceFileStream_FS’

)

LOG ON

(

NAME = ExtremeAdviceFileStream_LOG,

FILENAME = N’D:\TestDB\ExtremeAdviceFileStream_log.ldf’

);

GO

[/sourcecode]

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.