How to use FILESTREAM, query the metadata , access filestream data (using TIFF files) in SQL Server

[Guest Joseph Pilov]

1. Create database with a FILESTREAM filegroup

 

 

 

 

 

 

 

USE [master]

GO

 

--create database with Filestream

CREATE DATABASE [FileStreamTest]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'FileStreamTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\FileStreamTest.mdf' , SIZE = 204800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),

FILEGROUP [FileStreamFG] CONTAINS FILESTREAM DEFAULT

( NAME = N'FileStreamTestFStream', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\FileStreamTestFStream' , MAXSIZE = UNLIMITED)

LOG ON

( NAME = N'FileStreamTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\FileStreamTest_log.ldf' , SIZE = 270336KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

WITH CATALOG_COLLATION = DATABASE_DEFAULT

GO

 

 

 

 

 

 

 

 

2. Create a table that will store the binary documents

 

 

 

 

 

 

 

USE [FileStreamTest]

GO

CREATE TABLE FSTiffs(

Guid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),

documentID INT NOT NULL ,

documentType VARCHAR(10) NOT NULL,

FileContent VARBINARY(MAX) FILESTREAM NOT NULL ,

dateinserted DATETIME)

 

 

 

 

 

 

 

 

3. Discover which database and files contain FILESTREAM

 

 

 

 

 

--which database and files use filestream

SELECT db_name(database_id) dbname, name as file_name, physical_name, type_desc, *

FROM sys.master_files

WHERE type_desc = 'FILESTREAM'

 

 

 

 

 

 

 

 

4. Switch to the database and discover which table(s) contain FILESTREAM data

 

 

 

 

 

 

 

--which tables in the database have filestream enabled

USE [FileStreamTest]

GO

SELECT * FROM sys.tables

WHERE filestream_data_space_id IS NOT NULL

 

 

 

 

 

 

 

5. Using your preferred picture creator, create two .TIFF files: Sample1.tiff and Sample2.tiff. I used Paint in Windows

 

 

 

6. Insert the first file into the FILESTREAM-based table

 

 

 

 

 

 

 

--insert a TIFF file

INSERT INTO FSTiffs (documentID, documentType, FileContent , dateinserted)

SELECT 101, '.tiff', *, GETDATE() FROM OPENROWSET(BULK N'C:\temp\Sample1.tiff', SINGLE_BLOB) rs

 

 

 

 

 

 

 

 

7. Now update the newly-inserted row by replacing it with a different TIFF file

 

 

 

 

 

 

 

--update a document

UPDATE FSTiffs SET FileContent = ( SELECT * FROM OPENROWSET(BULK N'C:\temp\Sample2.tiff', SINGLE_BLOB) AS rs)

WHERE documentID = 101

 

 

 

 

 

 

 

8. Return data from the table

 

 

 

 

 

--select data from filestream table

SELECT * FROM FSTiffs

 

 

 

 

 

 

 

 

9. Delete the row

 

 

 

 

 

 

 

--delete a document

DELETE FSTiffs

WHERE documentID = 101

 

 

 

 

 

 

 

 

 

Enjoy!

 

Continue reading...