Maintenance Of Files In The Database
In SqlServer, you may use the following options (some of them are applicable to other CBSs).
Option 1
The D.O.D. keeps the headline of the file (e.g. the way to the file plus possibly some sort of a set of attributes):
create table [TableName] (... FilePath nvarchar(4000) not NULL... )
a Data is stored separately in the file system. The OBD size is smaller than if the OBD data are also stored. But we need to keep an eye on the files, there's no headline or headlines, no file. In my view, if the files are a logical part of the OBD data (not kash, not some temporary data), it would be better to look at other options.
Option 2
The OBD content of the file is also stored (in the varbinary(max) column).
create table [TableName] (... FileData varbinary(max) FILESTREAM not NULL, or -FileData varbinary(max) not NULL... )
There are two options, FILESTREAM and no.
Without FILESTREAM:
- Data stored in OBD (in LOB pages)
- one element limited to 2Gb
With FILESTREAM:
- stored file system (namely files)
- no restriction on 2Gb
- FILESTREAM does not participate in the max limit calculation of the OBD size (which is sensitive by Express Edition)
- data can be accessed through hundreds. API by file system
- (SqlServer 2014 and beyond) requested data are not removed from the buffer pool, leaving more memory to process requests
And with FILESTREAM and without trespassing. With FILESTREAM in access through Transact-SQL, there are restrictions on access through the file system.
Option 3
create table [FileTableName] as filetable
Their operation is based on the use of FILESTREAM. The table presents the hierarchy of stored files/directors, their data and attributes. In option 2, to create/write a file, it is necessary to create/write the list in the table. This option can be done directly through the file system. For example, to enter the relevant directory (SqlServer creates an appropriate UNC share) to do so, to create a file/director, remove/amend, then make a request select * from FileTableName and to see relevant changes. And vice versa, when the entry into the table via SQL, the directory will have a file or director.
