Is there a SqlFileStream like class that works with Sql Server 2005?

For the background to this question, see “How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?” that now has a large bounty on it.

SqlFileStream gives you an IoStream that sits on top of a blob (varbinary) value that is stored in the database. However due to limitations on SqlFileStream implementation it will only work with Sql Server 2008.

Logically I can’t see any reason why the some API cannot be provided on Sql Server 2000 and Sql Server 2005.

• Does anyone know if this has been done?
• Is there an open source implementation of this anywhere?
• What am I missing in think开发者_如何学运维ing it should be possible?

I don't know of a SqlFileStream class for anything other than SQL Server 2008. I suspect this is entirely down the fact that the SqlFileStream class is specifically designed to "sit on top of" a column of type FILESTREAM within SQL Server 2008.

The FILESTREAM column/datatype is only available in SQL Server 2008 and essentially allows large volumes of data to be stored in this column. A FILESTREAM column is actually a varbinary(max) column with a FILESTREAM modifier. This essentially causes the SQL Server to effectively bypass the RDBMS storage sub-system and store the binary data directly on the underlying NTFS file system, whist maintaining transactional integrity for this data from the RDBMS perspective.

The MSDN article states this thusly:

Specifying the FILESTREAM attribute on a varbinary(max) column causes SQL Server to store the data in the local NTFS file system instead of in the database file. Transact-SQL statements provide data manipulation capabilities within the server, and Win32 file system interfaces provide streaming access to the data.

The FILESTREAM Data in SQL Server 2008 MSDN article also states that:

The SqlFileStream API works in the context of a SQL Server transaction.

But also that:

SqlFileStream inherits from the System.IO.Stream class, which provides methods for reading and writing to streams of data. Reading from a stream transfers data from the stream into a data structure, such as an array of bytes. Writing transfers the data from the data structure into a stream.

Suggesting that, internally, the SqlFileStream operates in the same manner as a "bog-standard" FileStream class

However, perhaps the most telling details as to why this functionality is not available in prior versions of SQL Server (that do not implement any type of FILESTREAM or "direct-to-underlying-storage" column type) is from the FILESTREAM Overview MSDN article that states:

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

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.

This essentially says that using a SqlFileStream object will act almost identically to a "standard" FileStream object, except that SQL Server (for transactional integrity purposes) will act as a very lightweight "gatekeeper" (or "wrapper) for your reading and writing of binary data to an underlying NTFS file system file. Since no previous version of SQL Server has offered such functionality and direct integration with the underlying NTFS file system, I'd say that it would not be possible to implement a SqlFileStream against any version of SQL Server prior to SQL Server 2008. (At least not one that has the same "write to a file, but maintain RDBMS transactional integrity" features).

The FILESTREAM datatype was introduced in SQL Server 2008 and there is no equivalent in SQL Server 2005. This datatype is handled differently than a 'normal' blob type. FILESTREAM data are kept separately to the database as files, while blobs (text or varchar(max) and alike) are kept directly within data pages. So, you can stream contents of files with SqlFileStream in SQL 2008, but to read blobs, db engine has to load them into server memory and return to client as a whole. Because of that it is not possible to have anything like SqlFileStream attached directly to a blob column in SQL 2005.

see How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?