FileStrems in SQL Server 2012 – Introduction

Hi All,

I would like to start this post with a small story. I believe (at least) at one point of time, we might have faced problem searching information from our CD/DVD collection. We used to write information on CDs but that was not enough to cover all the contents of that CD. And when we need something particular we used to get confused, whether that content is in CD 1 or CD 2. I faced this many a times during my college-days. At that time one of my friends wrote a software that allows us to create a catalog for CD/DVD and store into database table. And that table name used to be written on that CD/DVD. So whenever we need to search something, we use that software and it will return us the CD name. So by that we minimized the efforts of searching and leverage the search functionality of software. I know there are lots of constraints and dependencies involved for this system to work beautifully, but that was a good concept.

In SQL Server Denali now SQL Server 2012, you might have heard the feature of FileStream which it pretty much like my story. FileStream allows us to manage unstructured data like documents, images etc. on file system. So applications which needs to have access to these resources (files, images etc.) can leverage facility of rich streaming APIs and performance of file system but at the same time maintain transactional consistencies between unstructured data and their management systems which is SQL Server.

Applications which are using SQL Server database as well as have a need of storing/managing large contents like documents; images etc. can have best of both world by using FileStream feature. FileStream feature uses the NT system cache for caching the resource/data. So when these resources are queries, SQL Server buffer pool is not used for streaming these contents rather NT file system helps for caching it. As a result SQL Server’s memory is available for query processing and gives better performance even while accessing unstructured data.

You can check-out more details about this feature here:

Thanks, Khilit

Rate this post

Leave a Reply