Paul Randal just published a list of tips and tricks that will help you get the most out of using FileStream, based on a longer whitepaper he wrote for Microsoft. Here are some posts that will help:
Read this before using FILESTREAM!
August 25th, 20084
By: Aaron Bertrand
I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.
I haven't used FILESTREAM except for a few basic tests, so don't really have any guidance for you. Have you visited this topic in Books Online? It may have some information that will help you decide one way the other.
http://msdn.microsoft.com/en-us/library/bb895334.aspx
Hi. Everywhere it's written that FILESTREAM should only be used when you are working with files bigger than 1MB. I got Files from 1KB up to 50MB. My question is now: Should I use FILESTREAM in my case, or not? Hope you have a good advise for me 🙂
Thank You!
Arthur, I guess it depends on what you are doing with the data from the FTP file. If you are bulk inserting the data into a true table format (several columns, several rows), then FileStream doesn't make much sense. AFAIK FileStream is meant for handling whole files, not splitting them up.
This is more of a question than a comment. We currently subscribe to Bloomberg for data concerning stocks. At the moment we receive an FTP file nightly and import that data into SQL Server. At some point soon, however, we are going to switch to a live feed. Would a FileStream be appropriate for this? One of our guys is working on a c# application to deal with the stream, but that's because we're still using SQL 2005. If a FileStream object is a better approach, it might provide the motivation to upgrade.
Thanks.