When it comes to storing images in a database, there are two common methods that have been used traditionally. However, with the introduction of SQL Server 2008, a new method called FILESTREAM storage has emerged. In this article, we will explore the traditional methods and discuss the advantages and disadvantages of each, as well as delve into the benefits of using FILESTREAM storage.

Method 1: Storing Images in the Filesystem and Storing Pointers in the Database

This method, although quite old, is still widely implemented even though SQL Server 2005 was released over 5 years ago. The advantage of this method is that there is no limit on the size of the image files, and it is the fastest way to retrieve images from the system. However, the drawback is that application and database security act separately, and the application or operating system is responsible for backing up the images.

Method 2: Storing Images in the Database

Introduced in SQL Server 2005, this method allows images (binary data) to be stored directly in the database. The advantage of this method is the same as the disadvantage of Method 1, which is security and permissions. However, the disadvantage is the same as the advantage of Method 1, which is speed, and the maximum size is limited to 2 GB.

Upon observing both Method 1 and Method 2, it becomes clear that they complement each other, but each has its own limitations and strengths. This is where FILESTREAM storage comes into play.

FILESTREAM Storage

FILESTREAM storage is a system that allows the storage of Binary Large Object (BLOB) or image files larger than 2 GB directly in the database itself. It can be backed up just like a regular database backup, and the security of the BLOB is maintained along with the database application. Additionally, it offers the same speed as the filesystem.

This is what makes FILESTREAM storage superior to any other methods described earlier. It allows us to overcome the traditional limitations of SQL Server storage for BLOB, varbinary, or images.

Summary

In conclusion, when it comes to storing images in SQL Server, there are traditional methods such as storing images in the filesystem and storing pointers in the database, as well as storing images directly in the database. However, these methods have their own limitations and strengths. FILESTREAM storage provides a solution that overcomes these limitations by allowing the storage of large image files directly in the database, while maintaining security and offering the same speed as the filesystem.