Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Managing Files with SQL Server 2008's FILESTREAM Feature : Page 2

SQL Server's new FILESTREAM feature simplifies the process of keeping file-based data, such as images, in sync with relational data.


advertisement

Enabling FILESTREAM

By default, the FILESTREAM feature is disabled, so before you can use it, you must configure both the server and database instances using the following steps:

 
Figure 1. Enabling FILESTREAM: You must enable FILESTREAM access for the desired SQL Server Instance before configuring the file stream access level for the database instance.
  1. To enable FILESTREAM for the server instance, open up the SQL Server Configuration Manager and right-click on "SQL Server Services" from the list of services, then click Open. You'll see a list of servers; right-click the SQL Server instance on which you want to enable FILESTREAM and select "Properties" from the context menu. Navigate to the "FILESTREAM" tab and check the "Enable FILESTREAM for Transact-SQL access" option (see Figure 1). You can also enable FILESTREAM for file I/O streaming access from this tab.
  2. To enable FILESTREAM for the database instance, execute the system stored procedure sp_configure and set the filestream_access_level parameter to 2, as shown below:

EXEC sp_configure filestream_access_level, 2 GO RECONFIGURE GO

The valid values for the filestream_access_level parameter are:

  • 0—Disables FILESTREAM support for this instance. This is the default value.
  • 1—Enables FILESTREAM for Transact-SQL access.
  • 2—Enables FILESTREAM for Transact-SQL and Win32 streaming access.
After completing the server and database instance configurations, the next step is to create the actual database in which the data will reside. Because FILESTREAMs are specifically created for storing binary data in file systems, you use the CREATE DATABASE statement to create a special FILEGROUP and mark it as a stream:

CREATE DATABASE FILESTREAMExample ON PRIMARY ( NAME = FILESTREAMExample_Primary, FILENAME = 'c:\Projects\DevX\Data\FILESTREAMExample.mdf'), FILEGROUP FILESTREAMGroup CONTAINS FILESTREAM ( NAME = FILESTREAMExample_FileGroup, FILENAME = 'c:\Projects\DevX\Data\FILESTREAMExample') LOG ON ( NAME = FILESTREAMExample_Log, FILENAME = 'c:\Projects\DevX\Data\FILESTREAMExample.ldf') GO

Next, create a table, designating one of its columns as type VARBINARY(MAX) FILESTREAM:



CREATE TABLE Product ( ProductID INT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Picture VARBINARY(MAX) FILESTREAM NULL, RowGuid UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID() ) GO

The preceding table definition assigns the Picture column as the varbinary(max) column, with the FILESTREAM attribute enabled. Note that the table with a FILESTREAM column must have at least one non-null unique ROWGUID column.

Any binary data you store inside the Picture column is not accessible from the file system; the only way to work with this binary data is through the standard CRUD (INSERT, UPDATE, and DELETE) SQL statements. The following example inserts a single row into the Product table.

INSERT INTO Product VALUES(1, 'Bicycle', 0x00, default)

GO

That inserts a new row where the ProductID is 1, the Name contains Bicycle, the Picture column value is NULL, and the RowGuid column contains some default GUID value. Now you can retrieve this row from a .NET application, and read, overwrite, or append to its contents.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap