SQL Server: Defining a column that implements FILESTREAM storage

FILESTREAM storage is implemented to store large binary objects, such as image or video files, as files on the file system and be able manage to them using Transact-SQL. FILESTREAM data can also be accessed using Win32 APIs. FILESTREAM storage is tightly integrated with most database functionality, including backup and recovery. When DBA/Developer take a database backup, FILESTREAM storage is also backed up unless DBA/Developer override this functionality by performing a partial backup. To create a table that can store FILESTREAM data, DBA/Developer create a table that contains a column of the varbinary(max) data type and include the FILESTREAM attribute. For example, DBA/Developer could use the following Transact-SQL statement to create a table with a Blueprint column that stores FILESTREAM data:

CREATE TABLE MyTable(
ID uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE, Blueprint varbinary(max)
FILESTREAM NULL);

DBA/Developer should also note that to use FILESTREAM data, DBA/Developer must first use SQL Server Configuration Manager or the sp_configure system stored procedure to enable FILESTREAM, because it is not enabled by default. DBA/Developer should not create a CLR user defined type. A CLR user-defined type is a user-defined type that is created using a .NET Framework language.CLR user-defined types are used to implement more complex data types that cannot be implemented using traditional methods.DBA/Developer should not define a hierarchyid data type column. A hierarchyid data type is a special variable-length, CLRsupplied data type that can be used to represent hierarchical data. When DBA/Developer define a hierarchyid data type column, DBA/Developer can use various system-supplied methods, such as GetRoot, GetDescendant, GetLevel, Read, and Write, which allow DBA/Developer to perform tasks on the hierarchy. Each value stored in the table is an internal binary value that represents the row's position in the hierarchy. Using a hierarchyid data type simplifies management of data that is represented in a tree-like structure. DBA/Developer should not define an image data type column and create a trigger to backup the files when a backup is taken. An image column's contents are stored within the database, rather than external to the database on the file system.