CODEWITHSUNDEEP
sql-servert-sql
Karl Johan Vallner
Karl Johan Vallner

Reputation: 4310

SQL Server (Transact-SQL) difference between Filestream and BLOB

Could anyone explain in laymans terms, what is the difference between those two data types and moreso, which are the pros and cons of using either of those to store files in a database.

If context is needed then I am creating a web app, where users can upload a multitude of different data, like images, Excel files, .docx etc.

Upvotes: 2

Views: 2257

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

Blobs are stored in a varbinary(MAX) column with the value stored in data pages inside the database data file(s).

With FILESTREAM, values are stored as individual files separately on the filesystem, with an individual file for each row and value. These files are managed internally by SQL Server and can be stored and retrieved using T-SQL just like normal varbinary(MAX) values or with Win32 APIs.

There are also FileTables, which is a specialized table with a predefined schema on top of FILESTREAM. FileTables provide T-SQL access like blobs and FILESTREAM and can optionally be access via a SQL Server managed UNC path, similarly to a normal Windows share. Creating/deleing files via the share inserts/deletes rows from the file table and visa-versa.

Upvotes: 4

Related Questions