{"id":2266,"date":"2020-02-18T04:28:13","date_gmt":"2020-02-18T01:28:13","guid":{"rendered":"http:\/\/kusuaks7\/?p=1871"},"modified":"2024-01-08T20:22:00","modified_gmt":"2024-01-08T20:22:00","slug":"how-to-use-sql-server-filestream-to-store-unstructured-data","status":"publish","type":"post","link":"https:\/\/www.experfy.com\/blog\/bigdata-cloud\/how-to-use-sql-server-filestream-to-store-unstructured-data\/","title":{"rendered":"How to Use SQL Server FILESTREAM to Store Unstructured Data"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tIn this post, I decided to explain to you how to use SQL Server FILESTREAM to store unstructured data.\u00a0Here you can also read what are the positive and negative sides of FILESTREAM. Here are some other SQL queries to help you use it.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tIn earlier versions of SQL Server, storage of unstructured data posed many challenges in terms of maintaining consistency between structured and unstructured data, managing backup\/restore procedure, performance issues, scalability, etc.\u2026\u00a0In earlier versions of MSSQL before SQL Server 2008, there were various mechanisms for storing unstructured data.\u00a0This information is usually stored in the form of files in a shared folder for which certain users are granted access rights.\u00a0The UNC path of these files is usually stored in the database as a column in the table (varchar (n)) so that application logic can access the specific files. The problem was the security of the files, administering access to them and maintaining them.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe later concept of Binary Large Objects (BLOB) helped to store unstructured data to some degree.\u00a0The main advantage of this concept is the integrated management and transactional consistency within the database. Security issues (the previous file solution) were resolved in this case.\u00a0There were problems though, which is a 2Gb limit and log file overload.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nTo address these issues, SQL Server 2008 introduced for the first time an enhancement called FILESTREAM.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tIt is not a data type such as VARBINARY (MAX) but an attribute\/property set via the VARBINARY column to indicate that the data should be stored directly on the file system.\u00a0Such data remains an integral part of the system and maintains transactional consistency.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nSome of the features of FILESTREAM are:\n
    \n \t
  • Binary data is stored as individual files outside the database,<\/li>\n \t
  • These individual files can be accessed through the WIN32 API for file operations,<\/li>\n \t
  • T-SQL statements are applicable,<\/li>\n \t
  • The 2GB file size limit for VARBINARY (MAX) columns has been removed for objects stored in the file system through FILESTREAM,<\/li>\n \t
  • FILESTREAM can also be used on compressed folders, disks, volumes.<\/li>\n<\/ul>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t

    In order to use FILESTREAM, it must be enabled.\u00a0FILESTREAM is enabled on two levels.\u00a0The first level is the SQL Server level.\u00a0This level was enabled when installing SQL Server.\u00a0If this is not done then enable can be done through SQL Server Configuration Manager by checking the checkboxes on the SQL Server FILESTREAM tab of Properties (Figure 1).<\/p>\nThe second level of enabling FILESTREAM is at the SQL Server Instance level. This enable is done by executing the T-SQL script provided in script 1.\n

    \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t
    EXEC sp_configure filestream_access_level,<\/span> 2<\/span><\/span><\/pre>\n
    RECONFIGURE<\/pre>\n<\/div>\nScript 1. Enable FILESTREAM at Server Instance level<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t\nTable 1 lists the possible values for filestream_access_level.\n\n\n\n\n\n\n
    Value<\/td>\nDescription<\/td>\n<\/tr>\n
    0<\/td>\nDisables FILESTREAM support for this instance.<\/td>\n<\/tr>\n
    1<\/td>\nEnables FILESTREAM for Transact-SQL access.<\/td>\n<\/tr>\n
    \u00a02<\/td>\nEnables FILESTREAM for Transact-SQL and Win32 streaming access.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Table 1. Possible values for filestream_access_level<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tThe next step is to create a database where it is possible to create tables in which FILESTREAM will be used. An example of a T-SQL script for creating a database where FILESTREAM can be used is given in script 2.\n
    \n
    CREATE DATABASE Test_FILESTREAM<\/span><\/pre>\n
    ON<\/span><\/div>\n
    PRIMARY(<\/span><\/div>\n
    NAME=Test_FILESTREAM_Prmary,<\/span><\/div>\n
    FILENAME=’d:sqlbaza2019mssqldataFILESTREAMTest_FILESTREAM.mdf’),<\/span><\/div>\n
    FILEGROUP FileStreamGroup CONTAINS FILESTREAM(<\/span><\/div>\n
    NAME=Test_FILESTREAM_FM,<\/span><\/div>\n
    FILENAME=’d:sqlbaza2019mssqldataFILESTREAMTest_FILESTREAM_FM’)<\/span><\/div>\n
    LOG ON (<\/span><\/div>\n
    NAME=Test_FILESTREAM_Log,<\/span><\/div>\n
    FILENAME=’d:sqlbaza2019mssqldataFILESTREAMTest_FILESTREAM.log’)<\/span><\/div>\n<\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tScript 2. An example of creating a database where it will be possible to use FILESTREAM<\/strong>\n\nExecuting script 2 on the file system will create a $ FSLOG folder and file filestream.hdr. The filestream.hdr file is a header file for the FILESTREAM container.\n\nThe next step is to create a table with column VARBINARY (MAX) with the FILESTREAM attribute (script 3).\n
    \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t
    \n
    USE Test_FILESTREAM<\/span><\/pre>\n
    <\/div>\n
    CREATE TABLE [dbo].[Fajlovi]<\/div>\n
    (<\/div>\n
    ID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL PRIMARY KEY,<\/div>\n
    Fajl VARBINARY(MAX) FILESTREAM DEFAULT NULL<\/div>\n
    )<\/div>\n<\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tScript 3. Creating a table with a VARBINARY (MAX) FILESTREAM column<\/strong>\n\nIt remains to insert the record into the newly created table (script 4).\n
    \n
    INSERT INTO Fajlovi<\/span><\/pre>\n
    VALUES (newid(), cast(‘My test FIESTREAM-a’ as varbinary(max)))<\/div>\n<\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tScript 4. Inserting a record into a table with a VARBINARY (MAX) FILESTREAM column<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tInserting a record with script 4 will also create a new folder on the file system.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tRecords from the table with the VARBINARY (MAX) FILESTREAM column can be obtained by executing script 5.\n
    \n
    select<\/span><\/span> *<\/span><\/span><\/pre>\n
    from [Test_FILESTREAM].[dbo].[Fajlovi]<\/pre>\n<\/div>\nScript 5. Viewing records from a table with a VARBINARY (MAX) FILESTREAM column<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tThe result of executing script 5 is given in Figure 2.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tEach cell in the FILESTREAM column is a file path on the file system associated with it. To read the path, it is necessary to use the PathName property of the varbinary (max) column in the T-SQL statement. Script 6 provides an example of how to read the varbinary (max) column file path.\n
    \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t
    DECLARE @filePath varchar(<\/span>max)<\/span><\/span><\/pre>\n
    <\/div>\n
    SELECT @filePath = Fajl.PathName()<\/span><\/div>\n
    FROM dbo.Fajlovi<\/span><\/div>\n
    WHERE ID = ‘F9A149D0-F5F0-4FC5-9F59-1D27E4D10C1D’<\/span><\/div>\n
    <\/div>\n
    PRINT @filepath<\/span><\/div>\n<\/div>\n \n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tScript 6. Example of reading a FILESTREAM file path on a file system<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tT-SQL can be used to work with FILESTREAM data, but it\u2019s a more natural MS Visual Studio environment.\u00a0The use of FILESTREAM functionality, in application logic, is made possible by implementing the System.Data.SqlTypes.SqlFileStream class.\u00a0To maintain consistency of data, each SQL FILESTREAM operation must be part of a transaction.\u00a0MARS (Multiple Active Result Sets) connections have special rules for batch transactions, which the T-SQL BEGIN TRANSACTION statement violates.\u00a0To avoid this problem, the application client should use the appropriate transaction management API to mean the class System.Data.SqlClient.SqlTransaction.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tTo allow transactional access to a FILESTREAM data file system, it is necessary to use the T-SQL function GET_FILESTREAM_TRASACTION_CONTEXT () to provide a token that represents the current transaction within a particular session (C # code 1).\n
    \n
    SqlConnection sqlConnection =<\/span> new<\/span> SqlConnection(<\/span><\/span><\/pre>\n
    <\/div>\n
    “Integrated Security=true;server=(local)”);<\/span><\/div>\n
    SqlCommand sqlCommand = new SqlCommand();<\/span><\/div>\n
    sqlCommand.Connection = sqlConnection;<\/span><\/div>\n
    <\/div>\n
    SqlTransaction transaction = sqlConnection.BeginTransaction(“mainTranaction”);<\/span><\/div>\n
    sqlCommand.Transaction = transaction;<\/span><\/div>\n
    <\/div>\n
    sqlCommand.CommandText =<\/span><\/div>\n
    “SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()”;<\/span><\/div>\n
    <\/div>\n
    Object obj = sqlCommand.ExecuteScalar();<\/span><\/div>\n
    <\/div>\n
    byte[] txContext = (byte[])obj;<\/span><\/div>\n<\/div>\n \n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tC # code 1. Using the GET_FILESTREAM_TRASACTION_CONTEXT () function<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tThis way the transaction is initiated but not yet committed or rollbacked. Multiple operations with FILESTREAM data can be performed in one transaction. An example of a data entry code using the Win32 API is given in C # code 2 in which a sqlFileStream object is initially created based on the System.Data.SqlTypes.SqlFileStream class.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t
    \n
    SqlFileStream sqlFileStream =<\/span> new<\/span> SqlFileStream(<\/span>filePath,<\/span> txContext,<\/span> FileAccess.<\/span>ReadWrite);<\/span><\/span><\/pre>\n
    <\/div>\n
    byte[] buffer = new byte[512];<\/span><\/div>\n
    <\/div>\n
    int numBytes = 0;<\/span><\/div>\n
    <\/div>\n
    \/\/Write string, “FILESTREAM test data” in FILESTREAM.<\/span><\/span><\/div>\n
    string someData = “FILESTREAM test data”;<\/span><\/div>\n
    Encoding unicode = Encoding.GetEncoding(0);<\/span><\/div>\n
    <\/div>\n
    sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()),0,someData.Length);<\/span><\/div>\n<\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tC # code 2. Example code for entering data in FILESTREAM<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tAn example code to read FILESTREAM data, using the Win32 API, is given in C # code 3. The same sqlFileStream object that was created at the beginning of the C # code 1 example is used.\n
    \n
    sqlFileStream.Seek(<\/span>0L,<\/span> SeekOrigin.<\/span>Begin);<\/span><\/span><\/pre>\n
    numBytes = sqlFileStream.Read(buffer, 0, buffer.Length);<\/span><\/div>\n
    string readData = unicode.GetString(buffer);<\/span><\/div>\n
    <\/div>\n
    if (numBytes != 0)<\/span><\/div>\n
    Console.WriteLine(readData);<\/span><\/div>\n<\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tC # code 3. Example code to read FILESTREAM data<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tUpon completion of FILESTREAM enrollment transactions (C # code 1) and reading FILESTREAM data (C # code 2), the sqlFileStream object (created at the beginning of the C # code 1 example based on the System.Data.SqlTypes.SqlFileStream class) must be closed and commit transaction (C # code 4). The sqlCommand object was created at the beginning of example C # code 1.\n
    \n
    sqlFileStream.Close();<\/span><\/span><\/pre>\n
    sqlCommand.Transaction.Commit();<\/span><\/div>\n<\/div>\n \t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tC # code 4. Close the sqlFileStream object and commit the transaction<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tA graph of comparative values of data loading performance, when VARBINARY (MAX) and FILESTREAM are used to store unstructured data and T-SQL or Win32 is given in Figure 3 to access the data.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tIt should also be noted that FILESTREAM is also used in other SQL Server technologies, such as FileTable and In-Memory OLTP.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\tIn the end:<\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t\nLike any other technology, FILESTREAM has its advantages and disadvantages.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t\nAdvantages:<\/strong>\n