----001---------- EXEC sp_configure filestream_access_level, 2 RECONFIGURE
----002---------- CREATE DATABASE MPPOC
ON
PRIMARY ( NAME = ArchiveMDF,
FILENAME = 'C:\MPPOCData\MPPOC.mdf'), -- C:\MyData路径必须存在
FILEGROUP FileStreamGroupFirst CONTAINS FILESTREAM( NAME = ArchiveFILESTREAM,
FILENAME = 'D:\MPPOCData\MPOCFileStream') -- D:\MyData路径下MyFileStream文件夹必须不存在
LOG ON ( NAME = ArchiveLDF,
FILENAME = 'C:\MPPOCData\MPPOC.ldf')
--SELECT FileTableRootPath('ProfileStores') --- \\PRCSGI10413D\MSSQLSERVER\MPOCFileStream\ProfileStores
----003---------- ALTER DATABASE MPPOC
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MPOCFileStream' ) -- 指定数据库访问级别和指定目录名字
----004------ USE MPPOC GO
CREATE TABLE ProfileStores AS FileTable WITH ( FileTable_Directory = 'ProfileStores', FileTable_Collate_Filename = database_default ); GO
---005------
CREATE UNIQUE INDEX UQ_MPPOCStreamId ON [ProfileStores](stream_id);
CREATE FULLTEXT CATALOG ProfileStoresFT AS DEFAULT CREATE FULLTEXT INDEX ON [ProfileStores] (file_stream TYPE COLUMN file_type) KEY INDEX UQ_MPPOCStreamId;
----006-- select * from sys.fulltext_document_types
EXEC sp_fulltext_service 'update_languages'; EXEC sp_fulltext_service 'load_os_resources', 1; EXEC sp_fulltext_service 'restart_all_fdhosts';
---006----[dbo].[ProfileStores]
-- use the CONTAINS keyword to search for a single word SELECT top 100 * FROM ProfileStores WHERE CONTAINS(file_stream,'Hello')
--
-- proximity search SELECT top 10 * FROM ProfileStores WHERE CONTAINS(file_stream, 'NEAR((name,male), 3, TRUE)')
--
-- search for all forms of the word SELECT top 10 * FROM ProfileStores WHERE CONTAINS(file_stream , ' FORMSOF (INFLECTIONAL, sky) ');
--
-- using freetext SELECT top 10 * FROM ProfileStores WHERE FREETEXT (file_stream, 'stone' );
----- mannul insert get GUID
---Support concurrency control DECLARE @image2 VARBINARY(MAX) SELECT @image2 = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK 'D:\SKY\2003.doc', SINGLE_BLOB) AS x
DECLARE @Tbl table(id uniqueidentifier);
INSERT INTO [dbo].[ProfileStores]([file_stream], [name]) OUTPUT INSERTED.[stream_id] into @Tbl VALUES (@image2, 'test00114111144122' );
select * from @Tbl
select * from ProfileStores
-- create some temporayr tables - we will create actual tables but use tempdb to do so -- (and we will clean up afterwards)
use tempdb go
if object_id('ee_test_tables_1','u') is not null drop table ee_test_tables_1 if object_id('ee_test_tables_2','u') is not null drop table ee_test_tables_2 go
create table ee_test_tables_1 (stream_id uniqueidentifier,file_stream varchar(100), name varchar(100)) create table ee_test_tables_2 (stream_id uniqueidentifier default newid(),file_stream varchar(100), name varchar(100)) go
-- example 1 create a newid() then use that
declare @newid uniqueidentifier set @newid = newid() insert ee_test_tables_1 values (@newid, 'stream 1','name 1')
select * from ee_test_tables_1 select @newid go
-- example 2 retrieving a "default" derived newid()
declare @newid uniqueidentifier declare @output table (stream_id uniqueidentifier)
insert ee_test_tables_2 (file_stream,name) output inserted.stream_id into @output values ('stream 2','name 2')
select @newid = stream_id from @output
select * from ee_test_tables_2 select * from @output select @newid
go
-- example 3 - using a char
-- uniqueidentifier max length is 36 characters of the format -- xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
declare @charid char(36) set @charid = newid() insert ee_test_tables_2 (stream_id, file_stream, name) values (@charid, 'stream 3', 'name 3')
select * from ee_test_tables_2 select @charid
go
-- now do some tidy up before we forget
if object_id('ee_test_tables_1','u') is not null drop table ee_test_tables_1 if object_id('ee_test_tables_2','u') is not null drop table ee_test_tables_2 go