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

相关文章