I would like to see + and - different types of storage, and when which one is better to use.

I did not work with file tables, but I assume that there is less headache with file operations, for example, the file cannot be blocked by the process, probably there are transactions (Ie you cannot kill the file, if suddenly when you add it to the table, the client will fall off ). Correct if I'm wrong.

UPD: Is Enttity Framework friendly with file tables?

  • And you here on SO in the search did not enter the "files in the database", these questions pass about once a month ... - Mike
  • And actually what is a "file table"? And most of the assumptions you wrote are wrong. 1. The process may block the file. 2. no transactions. 3. How to write a file and ignore if the client rolls off or not you decide. If the machine unexpectedly reboots or another fails, the unfinished file may remain on the disk - Mike

1 answer 1

In SqlServer you can use the following options (some of them are applicable to other DBMS).

Option 1

The "header" of the file is stored in the database (for example, the path to the file, plus possibly some set of attributes):

create table [TableName] ( ... FilePath nvarchar(4000) not NULL, ... )

and the data is stored separately in the file system. The size of the database is smaller than if the data is also stored in the database. But you need to follow the situations "there is a file, there is no title" or "there is a title, there is no file". In my opinion, if files are a logically important part of the database data (not a cache, not some temporary data), then it is better to look at other options.

Option 2

The database also stores the contents of the file (in a column of the varbinary(max) ).

 create table [TableName] ( ... FileData varbinary(max) FILESTREAM not NULL, --либо --FileData varbinary(max) not NULL, ... )

There are two options - with and without FILESTREAM .

Without FILESTREAM :

  • data is stored in the database (in the so-called LOB pages)
  • data size per item is limited to 2Gb

With FILESTREAM :

  • data is stored in the file system (exactly as files)
  • no 2Gb limit on the item
  • FILESTREAM data is not involved in calculating the limit for max. database size (what Express Edition is sensitive to)
  • data can be accessed via acc. File System API
  • (SqlServer 2014 and on) the requested data does not eat up from the buffer pool, leaving more memory for processing requests

Both with FILESTREAM and without transactions are supported. With FILESTREAM when accessing via Transact-SQL, the support is complete; there are restrictions when accessing through the file system (see here ).

Option 3

Using tables of special type FileTable .

 create table [FileTableName] as filetable

Their functionality is based on the use of FILESTREAM . The table represents the hierarchy of stored files / directories, their data and attributes. In option 2, to create / delete a file, you need to create / delete acc. entry in the table. In this embodiment, this can be done directly through the file system. For example, go to the appropriate directory (SqlServer creates the corresponding UNC share for this), create a file / directory, delete / modify, then make a select * from FileTableName and see the corresponding changes. And vice versa - when you insert a record into a table through SQL, the corresponding file or directory will appear in the directory.

Which option is best to use - I think, depends on the specific task. The documentation provides a more detailed description and comparison of options 2 and 3.