This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
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!
