When to use FileTable, and DB design questions

I have a kind of interesting scenario- lets say I have a list of people I wish to store in the database, so I create a table 'Person'.  Now lets say each person has a 2000 large image files (say high res photos or something).  Each image is > 5M.  What I need to do at the end of the day is retrieve a person, and upon selection display a selected photo (or photos) in my C# WPF app.

I was looking at FileTable to store the data on the disk, as this would be preferable to storing all these images as blobs.  But I wasn't sure how to associate the Person with their images through the FileTable.

Can I add additional columns to a file table (like an fk to Person)?  Not sure if that is the right approach.  Maybe FileStream is better?  Is it better in a case like this to just store a path in the database?  Which leads to the next question...

I also need to store other kinds of data, like DeepZoom data- this is interesting stuff, as its about 600M of data, and exists in a single directory structure with multiple levels of data (and images).  Apps able to read this need extremely fast access to the data to render it quickly.  I think in this case I would need to just store the location of the DZ data on disk.  But that seems fragile.  Is there a standard accepted way to store file locations in the DB?

Thoughts and advice appreciated!

October 22nd, 2012 4:01pm

Maybe FileStream is better? 

Yes, FileStream will be the better approach. FileTables have a fix table design, you can't change it e.g. to add a FK column.
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2012 4:32pm

Hi File stream is the good option.

The FileTable feature builds on top of SQL Server FILESTREAM technology

FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

YOu can use the Filetable when server version is greater then 2000

For more about Filetables please refer the below link

http://technet.microsoft.com/en-us/library/ff929144.aspx

October 25th, 2012 5:29am

Okay, filestream it is.

What about storing file locations?   Also, if I write a 10M file to a filestream- do I write this to the DB as if it were a blob?  Is it slower to write to the DB than directly to the filesystem using filestream?

I read the whitepaper on filestream and hunted around but couldn't seem to find this info...

Free Windows Admin Tool Kit Click here and download it now
October 25th, 2012 3:29pm

Okay, filestream it is.

What about storing file locations?   Also, if I write a 10M file to a filestream- do I write this to the DB as if it were a blob?  Is it slower to write to the DB than directly to the filesystem using filestream?

I read the whitepaper on filestream and hunted around but couldn't seem to find this info...

Since a FileTable appears as a folder in the Windows file system, you can easily load files into a new FileTable by using any of the available methods for moving or copying files. These methods include Windows Explorer, command line options including xcopy and robocopy, and custom scripts or applications.

Please read the below URL. It clearly explains answer for your question.In the below url  script example also given

http://technet.microsoft.com/en-us/library/gg492083.aspx#HowToMigrateFiles

Loading or Migrating Files into a FileTable
October 26th, 2012 5:29am

Well now you are confusing me ganeshkuppuswamy :)

We just agreed that Filestream was the way to go, yet you are describing a Filetable.

To reiterate: Is there a performance difference writing a 10M file to a Filestream (via C# for example) vs writing directly to the filesystem?  And for the sql gurus, how to store file location in a non fragile way? 

Free Windows Admin Tool Kit Click here and download it now
October 26th, 2012 3:45pm

Definitely FileTable!

For mapping (like Person) just create a  junction (?) table and FOREIGN KEY to the FileTable.

Table design example with junction table:

http://www.sqlusa.com/bestpractices2005/bankdatabase/

FileTable is like a Ferrari ready to cruise, filestream is like an empty car frame.

October 26th, 2012 7:45pm

I looked at the link you sent- after the glare of rainbow html wore off I saw the code :)

One thing I don't get- I need to tie the 'Person' table to the 'PersonPhotos' FileTable by something unique.  And there may be a lot of photos, organized in folders by date.  So how does this work?  My Person table just uses a PersonId (int) as a primary key, but the FileTable has no primary key of type int, its a hierarchyid.

The end goal for me is to be able to store the Person's photos on disk, but do a select to get all photos from that Person, and filter them.

Not sure how to accomplish this with FileTable.

Free Windows Admin Tool Kit Click here and download it now
October 28th, 2012 3:51am

Here are the tables:

CREATE TABLE Person (
PersonID INT IDENTITY(1,1) PRIMARY KEY,
-- DepartmentID INT FOREIGN KEY REFERENCES Department(DepartmentID),
LastName nvarchar(50) not null,
FirstName nvarchar(40) not null,
MiddleName nvarchar(40),
CONSTRAINT uqName UNIQUE(LastName, FirstName, MiddleName),
ModifiedDate date default(CURRENT_TIMESTAMP));
GO

CREATE TABLE xrefPerson_PersonPhotos (
PersonID INT FOREIGN KEY REFERENCES Person(PersonID),
PhotoID hierarchyid FOREIGN KEY REFERENCES PersonPhotos(path_locator),
CONSTRAINT pkPersonJunctionPhoto PRIMARY KEY(PersonID, PhotoID),
Description nvarchar(256),
ModifiedDate date default(CURRENT_TIMESTAMP));
GO



October 28th, 2012 4:31am

Ah!  Thank you very much!
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2012 6:59am

I want to be one of three tables
I want to filetable I add a column firstname
Is it possible to add a column to be filetable
September 7th, 2014 6:53am

Do you have any reason why to use path_Locator as a pk instead of stream_Id?
Free Windows Admin Tool Kit Click here and download it now
June 19th, 2015 1:31am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics