Is it possible to enable full text search on the filestream enable table ? how ?

Question

hi...

I am uploading some files with different extention such as .txt , .pdf like that and may be some time .mp3 or .mp4....size up to 2gb...

for that i am planing to use filestream enable table ....

I want to implement full text search ...on this table ...

Is it possible ? how ?

is file extention same as the time uploading while i retrive it to download ?

Answer 1

Hi .netaholic,

Yes, we can implement Full-Text Search on the table which contains filestream column. Since FILESTREAM stores objects as varbinary(max) binary large object (BLOB) data as files on the file system, and it not convenient to search the binary value, I suggest adding a column to store the object name, in this way, we can get the stored binary value according to the object name column value. For example:

create table dbo.RecordFileStream
(
	[id] [uniqueidentifier] rowguidcol not null unique,
	[FileName] varchar(50),
	[ObjectCol] varbinary(max) filestream null
) 

declare @img varbinary(max);
select @img=CAST(bulkcolumn as varbinary(max))
from openrowset
(
	bulk
	'D:\sqldata\test.jpg',single_blob
)as x
insert into dbo.RecordFileStream values (newid(),'test.jpg',@img);

-- Get the stored binary value
 select [ObjectCol] from dbo.RecordFileStream where FileName='test.jpg'

---

Allen Li
TechNet Community Support