Contents

1 語 襾豪?
2 蠍磯蓋 れ
3 一危磯伎 燕蠍
4 企 燕蠍
5 FILESTREAM directory structure - where do the GUIDs come from?
6 谿瑚襭


誤碁 れ 覯 覓 覯企. 企 譟磯 覈詩 覓 覯 れ 螻 曙. 蠏碁殊 讌讀 危危蠍 覦.

1 語 襾豪? #

襷 れ螻 螳 一 .

  • 一危郁 1MB襯
  • 觜襯 所鍵襯 蟲
  • 覩碁ろ一 危襴貅伎 螳覦 蟲

燕 讌 蠏碁る 螻 願蠍磯ゼ 覦.

2 蠍磯蓋 れ #

れ螻 螳 蠍磯蓋 れ .

  1. SQL Server Configuration Manager ろ
  2. FileStream SQL Server 語ろ伎るゼ 谿場 襷一 るジ 企Ν ->
  3. FileStream 襷 豌危襯 螻 企Ν

file_stream01.jpg

れ朱 SSMS れ螻 螳 れ.
use master
go
exec sp_configure 'filestream access level', 2
go
reconfigure
go

襷讌襷 襷り覲 れ螻 螳 譬襯 覩碁 れ螻 螳.

ValueDescription
0FILESTREAM support for the instance is Disabled
1FILESTREAM for Transact-SQL Access is Enabled
2FILESTREAM for Transact-SQL and Win32 streaming access is Enabled

襾語 蠍壱 襷 谿瑚蠍 覦.

3 一危磯伎 燕蠍 #

れ 襴襯 一危磯伎 Script襯 ろ.

  • c:\filestream (覈麹襦語 mkdir c:\filestream)

use master
go
if  exists (select name from sys.databases where name = 'filestreamdb')
	drop database filestreamdb
go

use master
go

create database filestreamdb 
on primary
(
	name = data01
,	filename = 'c:\filestream\data01.mdf'
),
filegroup filestream_fg contains filestream 
(
	name = filestream_data01
,	filename = 'c:\filestream' --蠍磯ゼ 譯殊伎 危エ覺. c:\filestream\file  . 
)
log on
(
	name = log01
,	filename = 'c:\filestream\log01.ldf'	
)

/*
覃讌 5591, 譴 16,  1, 譴 1
FILESTREAM feature is disabled.

襷 企 覃讌襯 蟆 覃 伎蟾讌 螻殊 譴 觜襴 覿覿 る 詞 . 
*/

4 企 燕蠍 #

use filestreamdb
go
create table dbo.filestreamtable
(
  fs_id uniqueidentifier rowguidcol not null unique,
  fsdata varbinary(max) filestream
);

insert into filestreamtable
values(newid(), cast ('inserting data into filestreamtable........' as varbinary(max)))
go


select 
	fs_id
,	CONVERT(varchar(50), fsdata) fsdata 
from filestreamtable
/*
fs_id                                fsdata
------------------------------------ --------------------------------------------------
E1C9F831-7305-4C18-954A-5DFEFC5DD550 inserting data into filestreamtable........
*/

declare @txcontext varbinary(max)
begin transaction
select @txcontext = get_filestream_transaction_context()
print @txcontext
commit
--蟆郁骸: 0x247E3F6CCDE6524FB5F20CA80003177B
file_stream02.jpg

5 FILESTREAM directory structure - where do the GUIDs come from? #

SELECT o.name AS [Table],
    cp.name AS [Column],
    p.partition_number AS [Partition],
    r.rsguid AS [Rowset GUID],
    rs.colguid AS [Column GUID]
FROM sys.sysrowsets r
    CROSS APPLY sys.sysrscols rs
    JOIN sys.partitions p ON rs.rsid = p.partition_id
    JOIN sys.objects o ON o.object_id = p.object_id
    JOIN sys.syscolpars cp ON cp.colid = rs.rscolid
WHERE rs.colguid IS NOT NULL AND o.object_id = cp.id
AND r.rsguid IS NOT NULL AND r.rowsetid = rs.rsid;
GO 

FileStreamData.jpg
0x6DBC7BFD 1覦危語 讌る殊 0x6D BC 7B FD 企襦 fd7bbc6d-... 螻 螳 .

6 谿瑚襭 #