Category: Filestream

Joey D’Antoni explains why you almost never want to use FILESTREAM:

Yesterday, I accidentally walked into a discussion on LinkedIn about the merits of Filestream in SQL Server. If you aren’t familar with Filestream, consider yourself lucky, it was a feature that was added to SQL Server in the short timeframe that people thought it was a good idea to use databases for file storage, and before the enlightened times when object storage became a thing. I first remember blobs being a thing in Oracle 8i, where at least you had the ability to store them in a tablespace with a larger block size than 8k, and had a dedicated area of the buffer pool with that larger block size that you could dedicate to that blob tablespace.

Joey has the right of things. There are rare exceptions where it could make sense to store files in databases. My best example involves storing ML models that we use in SQL Server ML Services, simply because of how difficult it is to read anything off of disk via ML Services. But that’s a real edge case.

Comments closed

Chad Callihan shares some thoughts:

A few weeks ago, I had a post about running into some issues due to a database using the FILESTREAM feature. To be fair, the problem wasn’t with FILESTREAM itself, just that I hadn’t accounted for it being in use (or previously in use) while configuring Availability Groups. FILESTREAM has been around for years but isn’t something I’ve seen in use too often. In case you’re not familiar with it, let’s take a brief look at what it is and some gotchas to watch out for.

Filestream is usually not worth it. I did like FileTable, the SQL Server 2012 extension to Filestream that smoothed out some of the rough edges. But even that’s Windows-only and very uncommon to find in environments.

Comments closed

Louis Davidson continues a discussion of SQL Server’s filetable feature:

In my article “Filetables, Great For Business, Great For Hobbyist”, I implemented the basis of a hobby database I use for my Twitter accounts (@disneypicaday and @dollywoodp), where I post daily pictures of theme parks.

As my inventory of prepped pictures has grown, one thing has become a major pain. Duplicates. I have tens of thousands of pictures, and I scan them occasionally to add to my directories. Perhaps not surprisingly, the same pictures often get chosen a second time. I use a naming standard that integrates the files into my database, as well as for the copies to go out for a given day. So the second (and third and fourth) time I choose the same picture, it looks different, though it has the exact same bits.

There are some nice benefits to maintaining file metadata in a queryable format, though do read to the end to see the performance impact.

Comments closed

Louis Davidson shows us a good scenario for SQL Server’s FileTable:

Now, my pictures are very manageable and searchable using SQL Server. The fact that the names are very standardized also helps me with searching the names in a file folder (I copy all of the files from the share into a Dropbox folder so I have access to the files everywhere I am as well.)

And additional cool thing is that even on my Express Edition local server, the filetable data is backed up with the database, so when I backup the database, it backs up my pictures in addition to the textual data. That extra backup, along with the copy of the files in Dropbox give me extra relief that I have a decent backup if something gets corrupted.

With just a little bit of configuration, and a little bit of code, I have a simple database of my images that I can search using T-SQL in a manner that is a lot more powerful that what I can reasonably do using File Explorer, along with the ability to keep history of how the pictures are used. This would be very useful for any photographer with a stock of pictures that they want to search and keep history of, even if it is just baby pictures…of the puppy.

Click through for the example. I think FileTable is one of the most underutilized features from SQL Server 2012.

Comments closed

Haroon Ashraf begins a series on semantic search over files:

The article examines storing unstructured data using native windows directory structure and managing it through the SQL Server database. Subsequently, we demonstrate how to perform comparative analysis with the help of Semantic Search in order to get valuable information out of the data. Additionally, you are going to get some hands-on experience of storing and linking documents on Windows File System and using Semantic Search.

It is of great importance for both database beginners and professionals to know how to apply file storage strategies such as File Table to searching technologies like Semantic Search for unstructured data analysis.

The first part of the series is all about setting up FileTable.

Comments closed