FILESTREAM Support
The FILESTREAM feature, which was introduced in SQL Server 2008, provides a way to store and access large binary values, either through SQL Server or by direct access to the Windows file system. A large binary value is a value larger than 2 gigabytes (GB). For more information about enhanced FILESTREAM support, see FILESTREAM Overview.
When a database connection is opened, @@TEXTSIZE will be set to -1 ("unlimited"), by default.
It is also possible to access and update FILESTREAM columns using Windows file system APIs. For more information, see FILESTREAM Overview.
For more information, see the following topics:
For information on sample applications that demonstrate this feature, see Considerations for Installing SQL Server Samples and Sample Databases.
Querying for FILESTREAM Columns
Schema rowsets in OLE DB will not report whether a column is a FILESTREAM column. ITableDefinition in OLE DB cannot be used to create a FILESTREAM column.
Catalog functions such as SQLColumns in ODBC will not report whether a column is a FILESTREAM column.
To create FILESTREAM columns or to detect which existing columns are FILESTREAM columns, you can use the is_filestream column of the sys.columns catalog view.
The following is an example:
-- Create a table with a FILESTREAM column.
CREATE TABLE Bob_01 (GuidCol1 uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID(), IntCol2 int, varbinaryCol3 varbinary(max) FILESTREAM)
-- Find FILESTREAM columns.
SELECT name FROM Sys.columns where is_filestream=1
-- Determine whether a column is a FILESTREAM column.
SELECT is_filestream FROM Sys.columns where name = 'varbinaryCol3' and object_id IN (SELECT object_id from Sys.tables where name='Bob_01')
Down-Level Compatibility
If your client was compiled using the version of SQL Server Native Client that was included with SQL Server 2005, and the application connects to a later version of SQL Server, varbinary(max) behavior will be compatible with SQL Server 2005. That is, the maximum size of returned data will be limited to 2 GB. For result values larger that 2 GB, truncation will occur and a "string data right truncation" warning will be returned.
When data-type compatibility is set to 80, client behavior will be consistent with down-level client behavior.
For clients that use SQLOLEDB or other providers that were released before the SQL Server 2005 version of SQL Server Native Client, varbinary(max) will be mapped to image.