In SQL Server, the FILESTREAM attribute allows you to store large binary data, such as images or documents, directly in the file system while maintaining transactional consistency with your SQL Server database. This is particularly useful for handling large files that can exceed the limits of regular varbinary(max) storage.
Here’s a step-by-step guide to using the FILESTREAM attribute in SQL Server:
Setup
1. Enable FILESTREAM on the SQL Server Instance:
First, you need to enable the FILESTREAM feature on your SQL Server instance. You can do this by using SQL Server Configuration Manager or by running SQL Server Management Studio:
1. Open SQL Server Configuration Manager.
2. Locate your SQL Server instance, right-click it, and select Properties.
3. Go to the FILESTREAM tab and enable the FILESTREAM for Transact-SQL access and optionally for file I/O streaming access.
4. Set the appropriate file system directory for the FILESTREAM data.
2. Create a New Database with FILESTREAM:
You need to specify a FILESTREAM filegroup when creating a new database. Here’s a SQL command to create a database with a FILESTREAM filegroup:
CREATE DATABASE MyDatabase
ON PRIMARY (NAME = MyDatabase_Data, FILENAME = 'C:\Path\To\MyDatabase_Data.mdf')
FILEGROUP MyFileStreamGroup CONTAINS FILESTREAM
(NAME = MyFileStreamFileGroup, FILENAME = 'C:\Path\To\MyFileStream')
LOG ON (NAME = MyDatabase_Log, FILENAME = 'C:\Path\To\MyDatabase_Log.ldf')
3. Create a Table with a FILESTREAM Column:
Define a table that includes a column of type VARBINARY(MAX) with the FILESTREAM attribute.
CREATE TABLE MyDocuments (
DocumentId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
DocumentName NVARCHAR(255) NOT NULL,
DocumentData VARBINARY(MAX) FILESTREAM NULL,
UploadedDate DATETIME NOT NULL DEFAULT GETDATE()
);
Using the Table
4. Inserting Data into the Table:
You can insert binary data into the table as follows:
DECLARE @DocumentId UNIQUEIDENTIFIER = NEWID();
INSERT INTO MyDocuments (DocumentId, DocumentName, DocumentData)
VALUES (@DocumentId, N'SampleDocument.pdf',
(SELECT * FROM OPENROWSET(BULK N'C:\Path\To\Your\File\SampleDocument.pdf',
SINGLE_BLOB) AS Document));
5. Retrieving Data from the Table:
To retrieve the binary data stored in a FILESTREAM column, you can use a standard SELECT statement:
SELECT DocumentId, DocumentName, UploadedDate
FROM MyDocuments
WHERE DocumentId = @DocumentId;
If you want to retrieve the file for download or further processing, you can again use OPENROWSET or other file handling methods.
6. Deleting Data from the Table:
To delete a record, you can simply run a DELETE statement:
DELETE FROM MyDocuments WHERE DocumentId = @DocumentId;
Considerations
- Limitations: There are some limitations with FILESTREAM, such as the inability to use certain features in conjunction with FILESTREAM data. For example, text-based operations directly on FILESTREAM columns are not supported.
- Backup and Restore: Backing up a database with FILESTREAM data leverages the standard SQL Server backup processes. However, you should ensure that your file system is also backed up since the data is stored outside the database.
Using FILESTREAM can significantly enhance your ability to work with large binary files while utilizing SQL Server's transactional integrity and management capabilities.