Binary Large Object (Blob) Data in the SQL Server (ANO13)

 #Blobs in the SQL Server, #FILESTREAM, #FileTables , #Blob Data types

In the previous article(ANO12), I explained how to store large a column with a lob datatype inside a table. In this article, I will introduce Filestrean and FileTable to store large objects.

FILESTREAM Storage:

The Database Engine supports the storage of LOBs using the VARBINARY(max) data type. The property of this data type is that binary large objects (BLOBs) are stored inside the database. This solution can cause performance problems if the stored files are very large, as in the case of video or audio files. In that case, it is common to store such files outside the database, in external files.

 The FILESTREAM storage option supports the management of LOBs, which are stored in the NTFS file system. The main advantage of this type of storage is that the Database Engine is able to manage FILESTREAM data even though it is stored outside the database.

Therefore, this storage type has the following properties:

• You use the CREATE TABLE statement to store FILESTREAM data and use the DML statements (SELECT, INSERT, UPDATE, and DELETE) to query and update such data.
• The Database Engine assures the same level of security for FILESTREAM data as for relational data stored inside the database.

Before We can store FILESTREAM data, We have to enable the system for this task. The following subsection explains how to enable the operating system and the instance of our database system.

Enabling FILESTREAM Storage

FILESTREAM storage has to be enabled at two levels:

• For the Windows operating system
• For the particular database server instance

NOTE You need to be Windows Administrator on a local system and have administrator (sysadmin) rights to enable FILESTREAM storage. You also need to restart the instance for the changes to take effect.

You use SQL Server Configuration Manager to enable FILESTREAM storage at the OS level. To open SQL Server Configuration Manager, type SQLServerManager15.msc in the Search field for SQL Server 2019 or type SQLServerManager14.msc for SQL Server 2017(You can also execute this command "SQLServerManager15.msc" in the Run). In the list of services, right-click SQL Server Services, and then click Open. In the SQL Server Configuration Manager snap-in, choose your instance of the Database Engine, right-click the instance, and then click Properties. In the SQL Server Properties dialog box, click the FILESTREAM tab. Check the Enable FILESTREAM for Transact-SQL Access check box. If you want to read and write FILESTREAM data from Windows, check the Enable FILESTREAM for File I/O Access check box. Enter the name of the Windows share in the Windows Share Name box and click Apply to apply the changes. SQL Server Configuration Manager creates a new share with the specified name for the instance.

The next step is to enable FILESTREAM storage for a particular database server instance. SQL Server Management Studio will be used to show this task. Right-click the instance in Object Explorer, click Properties, select Advanced in the left pane, and set Filestream Access Level to one of the following levels:

• Disabled FILESTREAM storage is not allowed.
• Transact-SQL Access Enabled FILESTREAM data can be accessed using T-SQL statements.
• Full Access Enabled FILESTREAM data can be accessed using T-SQL as well as from the OS.

The alternative way is to use the sp_configure system procedure with the filestream access level option:

/*
-- 
0:Disable 
1:Transact SQL Access  
2:Full Acess Enabled
*/
SP_CONFIGURE 'filestream access level',2  
RECONFIGURE
GO        

After you enable FILESTREAM storage for your instance, you can use the Create Database statement to create a filegroup for FILESTREAM data and then add a file to that filegroup.

Note: if you are not familiar with Filegroup, see my previous articles(ANO8-11)

In this script, I added multiple filegroups including ordinary data, indexes, large objects, and Filestream.

USE master
GO
IF DB_ID('FGDB')>0
BEGIN
	ALTER DATABASE FGDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE FGDB
END
GO


CREATE DATABASE FGDB 
	ON  PRIMARY
	(
		NAME=FGDB,FILENAME='d:\DataBase\FGDB.mdf'
	),
	FILEGROUP FG1
	(
		NAME=FGDB_Data1,FILENAME='d:\DataBase\FGDB_Data1.ndf',SIZE=1MB,FILEGROWTH=1MB
	),
	(
		NAME=FGDB_Data2,FILENAME='d:\DataBase\FGDB_Data2.ndf',SIZE=1MB,FILEGROWTH=1MB
	),
	FILEGROUP FG_Index
	(
		NAME=Data_IDX,FILENAME='d:\DataBase\FGDB_IDX.ndf'
	),
	FILEGROUP FG_LOB
	(
		NAME=Data_LOB,FILENAME='d:\DataBase\FGDB_LOB.ndf'
	),
	FILEGROUP FG_FileStream CONTAINS FILESTREAM
	(
		NAME = Data_FSG,FILENAME ='d:\DataBase\FGDB_FS'
	)
	LOG ON
	(
		NAME=FGDB_log,FILENAME='d:\DataBase\FGDB_log.ldf'
	)
GO
USE FGDB
GO        

The CONTAINS FILESTREAM option tells the system that this filegroup will contain only FILESTREAM data.

Of course, you can also use the Alter Database statement to add a filegroup to an existing database.

USE FGDB
GO
ALTER DATABASE FGDB 
	ADD FILEGROUP FG_FileStream2 CONTAINS FILESTREAM
GO
ALTER DATABASE FGDB 
	ADD File 
	(
		NAME = Data_FSG2,FILENAME ='d:\DataBase\FGDB_FS2'
	) TO FILEGROUP FG_FileStream2
go
SP_HELPFILEGROUP        

Now We can create a table with one or more FILESTREAM columns:

CREATE TABLE TestTable
(
	ID  UNIQUEIDENTIFIER  NOT NULL ROWGUIDCOL UNIQUE DEFAULT(NEWSEQUENTIALID()),
	Content	VARBINARY(MAX) FILESTREAM NULL
)    
ON [PRIMARY] FILESTREAM_ON FG_FileStream
GO
The TestTable        

Note: All tables that store FILESTREAM data require the existence of a UNIQUE ROWGUILDCOL. For this reason, the TestTable has the id column, defined using these two attributes.

You can also add a filestream column to an existing table:

DROP TABLE IF EXISTS TestTable2
GO
CREATE TABLE TestTable2
(
	Title	NVARCHAR(255) NOT NULL
)    
GO
ALTER TABLE TestTable2 SET(FILESTREAM_ON ='FG_FileStream')
GO
ALTER TABLE TestTable2 ADD
	ID  UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT(NEWID()),
	Content	VARBINARY(MAX) FILESTREAM NULL
GO	
        

Insert data into the table:

INSERT INTO TestTable(Content)
	VALUES (CAST(REPLICATE('FileStream',10) AS VARBINARY(MAX)))
GO

SELECT * FROM TestTable
SELECT *,CAST(Content AS VARCHAR(MAX)) FROM TestTable
GO        

We can also insert an image (or other multimedia) inside this table.

INSERT INTO TestTable(Content)
	SELECT 
		BulkColumn 
	FROM OPENROWSET
		(
			BULK N'D:\DataBase\Dump\img.jpg', 
			SINGLE_BLOB, Single_Blob
		) AS tmp
GO
SELECT *,CAST(Content AS VARCHAR(MAX)) FROM TestTable
GO
--Note we cannot cast image 
--see the content path 
SELECT ID,Content.PathName() FROM TestTable
GO        

You can also use Delete and update statements:

DELETE FROM TestTable WHERE ID=?
GO
UPDATE TestTable SET
	Content= 0x
WHERE ID=?
GO         

I hope this article is useful for you, in the next article I'll explain FileTable.

Don't forget to like and share 🙏