CODEWITHSUNDEEP
sql-serverfiletablesqlfilestream
Eelco
Eelco

Reputation: 379

FileStream vs FileTable

I want to store images in a sql database. The size of the image is between 50kb to 1mb. I was reading about a FileStream and a FileTable but I don't know which to choose. Each row will have 2 images and some other fields.

The images will never be updated/deleted and about 3000 rows will be inserted a day.

Which is recommend in this situation?

Upvotes: 14

Views: 12591

Answers (3)

Aiyoub A.
Aiyoub A.

Reputation: 5391

In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage.

If the following conditions are true, you should consider using FILESTREAM:

  • Objects that are being stored are, on average, larger than 1 MB.
  • Fast read access is important.
  • You are developing applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

Benefits of the FILETABLE:

  • Windows API compatibility for file data stored within a SQL Server database. Windows API compatibility includes the following:

    • Non-transactional streaming access and in-place updates to FILESTREAM data.
    • A hierarchical namespace of directories and files.
    • Storage of file attributes, such as created date and modified date.
    • Support for Windows file and directory management APIs.

  • Compatibility with other SQL Server features including management tools, services, and relational query capabilities over FILESTREAM and file attribute data.

Upvotes: 3

DdW
DdW

Reputation: 918

Originally it was always a bad idea to store files (= binary data) in a database. The usual workaround is to store the filepath in the database and ensure that a file actually exists at that path. It wás possible to store files in the database though, with the varbinary(MAX) data type.

was introduced in and handles the varbinary column by not storing the data in the database files (only a pointer), but in a different file on the filesystem, dramatically improving the performance.

was introduced with and is an enhancement over filestream, because it provides metadata directly to SQL and it allows access to the files outside of SQL (you can browse to the files).

Advice: Definitely leverage FileStream, and it might not be a bad idea to use FileTable as well.

More reading (short): http://www.databasejournal.com/features/mssql/filestream-and-filetable-in-sql-server-2012.html

Upvotes: 18

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

It depends. I personally will preffer link to the image inside the table. It is more simple and the files from the directory can be backed up separately.

You have to take into account several things:

  1. How you will process images. Having only link allows you easily incorporates imges inside web pages (with propper config of the Web server).

  2. How much are the images - if they are stored in the DB and they are a lot - this will increase the size of the DB and backups.

  3. Are the images change oftenly - in that case it may be better to have them inside DB to have actual state of the backup inside DB.

Upvotes: -2

Related Questions