When it comes to storing images in database there are two common methods. I had previously blogged about the same subject on my visit to Toronto. With SQL Server 2008, we have a new method of FILESTREAM storage. However, the answer on when to use FILESTREAM and when to use other methods is still vague in community.
Let us look into two traditional methods first along with their advantage and disadvantages.
Method 1) Store image in filesystem and store pointer in database
This is quite an old method and you can find this implemented in many places, even though SQL Server 2005 has been released 5 years ago.
The advantage of this method is that there is no limit on the size of images file and it is the fastest way to retrieve images from system.
The drawback of this method is that application and database security acts separately and application/OS has to take backup for images.
Method 2) Store images in a database
This was introduced in SQL Server 2005 where images (binary data) can be stored in database.
The advantage of this method is same as the disadvantages of method 1, i.e. security and permissions.
The disadvantage of this method is same as the advantages of method 1, i.e. speed and maximum size is limited to 2 GB.
On observing Method 1 and Method 2, it is very clear that they are complementing each other but both are very powerful methods on their own.
Although both the methods are very good they have limitations, which together only they can provide. This is where FILESTREAM Storage comes into action.
FILESTREAM Storage system can store BLOB (Binary Large Object OBject) or images file, which is larger than 2 GB, and it can store them in database itself. It can be backed up just like regular database backup, and the security of the BLOB is maintained along with database application. After all this, it is as fast as filesystem. Now, this is what makes this superior to any other methods described earlier.
Summary: We can use FILESTREAM Storage to overcome traditional limitations of SQL Server storage of BLOB, varbinary or images.
Reference : Pinal Dave (https://blog.sqlauthority.com)
