100 FileTableScript

 

----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