/*********** Storing Files ***************/
--> store filepath
--> store files in varbinary(MAX)
--> FileStream (sql-server-2008): Store pointer in the row and the files in seperate file => dramatically improve performance
--> FileTable (sql-server-2012): allows access to the files outside of SQL using API
use master
GO
EXEC sp_configure filestream_access_level
GO
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO
CREATE DATABASE PlayWithFiles
ON PRIMARY(
NAME = PlayWithFiles_Primary,
FILENAME =N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\PlayWithFiles_Data.mdf'
),
FILEGROUP PlayWithFiles_Group CONTAINS FILESTREAM(
NAME = PlayWithFiles_Group,
FILENAME =N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\PlayWithFiles_File'
)
LOG ON(
Name = FILESTREAM_Log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\PlayWithFiles_Log.ldf'
)
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'PlayWithFiles')
GO
SELECT DB_NAME(database_id) as DBName, non_transacted_access, non_transacted_access_desc
FROM sys.database_filestream_options
where DB_NAME(database_id) = 'PlayWithFiles'
GO
use PlayWithFiles
GO
------------------------------------------- CREATE FILESTREAM TABLE
CREATE TABLE dbo.StoringFileInFileStream
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[SerialNumber] INTEGER UNIQUE,
[MyFiles] VARBINARY(MAX) FILESTREAM NULL
)
GO
select * from dbo.StoringFileInFileStream
GO
DECLARE @File AS VARBINARY(MAX)
SELECT @File = CONVERT(VARBINARY(MAX), BulkColumn)
FROM OPENROWSET(BULK 'C:\temp\Ronen Ariely Test2 File.docx', SINGLE_BLOB ) AS x
INSERT INTO dbo.StoringFileInFileStream(id,SerialNumber, MyFiles) SELECT newid(),1, @File
GO
select * from dbo.StoringFileInFileStream
GO
------------------------------------------- CREATE FILETABLE TABLE
CREATE TABLE StoringFile AS FILETABLE
WITH (FileTable_Directory = 'StoringFile');
GO
--> SSMS Object Explorer -> Databases -> PlayWithFiles -> tables -> File Tables -> dbo.StoringFile -> right click: Explor File Table Directory
--> \\machine name\central file stream name\database name\table name
DECLARE @File AS VARBINARY(MAX)
SELECT @File = CONVERT(VARBINARY(MAX), BulkColumn)
FROM OPENROWSET(BULK 'C:\temp\Ronen Ariely Test2 File.docx', SINGLE_BLOB ) AS x
INSERT INTO dbo.StoringFile(name, file_stream) SELECT 'Ronen Ariely Test2 File.docx', @File
GO
SELECT * FROM dbo.StoringFile
GO