The Internet age data is exploding, and we often need to store structured and unstructured data (such as documents, presentations, videos, audio, images) together. There are usually several scenarios:
1. Storing structured data in a database, storing unstructured data in a file system and then having a field in the database to record the path of the file system, though cost-effective, introduces additional complexity because you need to manually ensure the integrity of the transactions across relationships and systems that are not relational.
2. Storing both structured and unstructured data in a database has for many years supported the storage of relational data, such as binary objects, or the Blob,sql server called varbinary data types, which benefits the full use of database features such as transactional support, Backup and recovery support, integrated security support, Full-text search support, etc., but cost more, requires more disk space, because it is stored in the same row of data, storage and retrieval time is longer, the overall performance of the application will also have a negative impact. (If you write the query with SELECT * from XXX is very slow)
3. The FILESTREAM data type introduced in SQL Server 2008 stores unstructured data, such as documents, presentations, videos, audio, images, and a pointer to a file system stored in the database. In SQL Server 2008, the new FileStream (file flow) feature is implemented on the existing varbinary (max) data type, and you can store real data on the server's filesystem, but can be managed and accessed within the context of the database. The integrity of the transaction is guaranteed automatically.
4. The filetable of SQL Server 2012 is further enhanced, enabling applications to consolidate their storage and data management components by introducing filetable, allowing non-transactional access, and providing integrated full-text and semantic searches for unstructured data and metadata.
The two new features are discussed in detail below.
Learn about FileStream in SQL Server 2008
The FILESTREAM data type is implemented as a varbinary (max) column, and the data is stored in the NTFS file system, with pointers stored in the database. In this case, the store is no longer a 2GB size limit for a blob, but is constrained by the file size of the NTFS file system. FileStream is prohibited by default, so you need to specify the FileStream attribute for the varbinary (max) column. This way SQL Server does not save the BLOB to the SQL Server database, but to the NTFS file system.
Storing BLOB data on an NTFS file system offers some benefits:
The same as the performance of the data flow that directly operates on the NTFS file system
FILESTREAM data does not use the SQL Server buffer pool, so the query processing of the SQL Server buffer pool is not affected by the FILESTREAM data.
There is no longer a 2G size limit for blobs.
The consistency of the transaction.
SQL Server integrated security model.
FileStream BLOB data is included when backing up and restoring.
Supports Full-text search.
With the Select,insert,update and DELETE statements, you manipulate the table with FILESTREAM data, and the performance does not slow down.
To use this new feature, we first need to enable it at the instance level (during Setup, or by modifying the properties of the SQL Server instance, or by using sp_configure to change the instance properties), and then creating or modifying the database, and there is a filegroup with the FileStream attribute. You then create a table with the varbinary (max) data type column and specify the FileStream property.
Learn about SQL Server 2012 's filetable
Filetable is further enhanced using FileStream, which allows direct, transactional, and access to FILESTREAM columns of large data. The Filetable table can also be configured to allow non-transactional access to files without prior SQL Server authorization.
Filetable is a special type of table whose structure is fixed, unlike a normal user table that can define its own fields as if it were a file system in a folder. A filetable contains FileStream with several file-level attributes (file_id, name, path, creation date, modified date, last access time, etc.), file and directory hierarchy data. This means that each row of data in the filetable represents a file or directory on a file system.
Filetable in the file system is a indows shared directory with file and directory data that you can access through Non-transactional files (Windows API based applications can access files without SQL Server access). For Windows applications, this looks like a normal network share location for files and directories. Applications can use the Windows API to manage files and directories for this network share.
Windows API operations are non-transactional and are not related to the database. However, Filetable is implemented based on FileStream, so the SQL transaction is supported. Filetable can also be queried and updated through normal Transact-SQL commands. They also integrate SQL Server management tools and features, such as backup and recovery.
We need to configure filetable and FileStream separately. This means that we can continue to use only FileStream functionality without having to enable non-transactional access or create filetable.
Start using SQL Server 2012 's filetable
Enable FileStream
Copy Code code as follows:
Use master Go
EXEC sp_configure ' FileStream access level ', 2
Go
Reconfigure go
--you can use the statement to
--config value and running value
EXEC sp_configure filestream_access_level;
Go
Press CTRL + C to copy the code to create the Learnfiletable database
Copy Code code as follows:
Use master
Go
IF EXISTS (SELECT name from sys.databases WHERE name = N ' learnfiletable ')
DROP DATABASE learnfiletable
Go
CREATE DATABASE learnfiletable
--details of primary file group
On PRIMARY
(NAME = Learnfiletable_primary,
FILENAME =n ' D:\FileTable\LearnFileTable_Data.mdf ',
SIZE = 10MB,
MAXSIZE = 50MB,
FileGrowth = 5MB),
--details of additional filegroup to is used to store data
FILEGROUP Datagroup
(NAME = Learnfiletable_data,
FILENAME =n ' D:\FileTable\LearnFileTable_Data.ndf ',
SIZE = 10MB,
MAXSIZE = 50MB,
FileGrowth = 5MB),
--details of special filegroup to is used to store FILESTREAM data
FILEGROUP Fsdatagroup CONTAINS FILESTREAM
(NAME = FileStream,
--filename refers to the path and the actual file name. It
--creates a folder which contains a FILESTREAM.HDR file and
--also a folder $FSLOG folder as depicted in image below
FILENAME =n ' D:\FileTable\FSData ')
--details of log file
LOG on
(Name = Learnfiletable_log,
FILENAME = ' D:\FileTable\LearnFileTable_Log.ldf ',
SIZE = 5MB,
MAXSIZE = 25MB,
FileGrowth = 5MB
)
With FILESTREAM (non_transacted_access = full, Directory_name = N ' learnfiletable ')
--other option for non_transacted_access are read_only or off
Go
Press CTRL + C to copy the code check filestream/filetable
Copy Code code as follows:
--Check The filestream/filetable Options
SELECT db_name (database_id), non_transacted_access, Non_transacted_access_desc, directory_name from Sys.database_ Filestream_options
WHERE db_name (database_id) = ' learnfiletable '
Create Filetable
Copy Code code as follows:
Use learnfiletable
Go
CREATE TABLE myfirstfiletable as Filetable
With
(
filetable_directory = ' myfirstfiletable ',
Filetable_collate_filename = Database_default
);
Go
Press CTRL + C to copy the code if we do not enable FileStream before creating the Filetable, the error will be
MSG 1969, level, State 1, line 1 Default FILESTREAM filegroup isn't available in database ' <database_name> ' created, I Check it out, there's no record.
Use learnfiletable SELECT * FROM [dbo]. [Myfirstfiletable]
In Enterprise Manager Select Filetable, right-click "Explorer filetable Directory" link as shown below:
You will see a network of shared directories. To manually add several files to the directory, we return to SQL Server Enterprise Manager to see what's going on:
Run the query once more
Use learnfiletable SELECT * FROM [dbo]. [Myfirstfiletable]
Summary
In this article, I talked about using SQL Server's FileStream and filetable features to store unstructured data. The FileStream feature stores unstructured data in a file system and saves pointers to files in the database, and filetable further expands this capability to allow non-transactional access (access to files without prior authorization, shared locations). In other words, with this capability, we can manage unstructured data through the file system, rather than in SQL Server management, but can still access these files in SQL Server transactions.
