SQL Server Denali FileTables Feature - The fruits of WinFS? (Think "A FileStream enabled table that you can access as a network file share and other standard File IO API's" or "SQL Server File System in a Table") - Greg's Cool [Insert Clever Name] of the Day

Tim Anderson’s ITWriting - SQL Server 2011 Denali publishes tables as Windows network folders

I’ve been testing the new Community Tech Preview of SQL Server 2011, codenamed “Denali”.

Here is an intriguing feature. You can now create a new kind of table called a FileTable. A FileTable is mapped to a folder on the filesystem, though you are not meant to access it directly once it is managed by SQL Server. However, you can access the folder in Windows Explorer, or over the network, as a network share. When you do this, a SQL Server component intercepts the Windows API calls and updates the FileTable. FileTables build on the existing FILESTREAM feature in SQL Server 2008, and the documents in the folder are stored as FILESTREAM data.

The illustration shows a folder in Windows Explorer that is also a SQL Server FileTable.

..."

SQL Server "Denali" - FileTables

The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services - including full-text search and semantic search - over unstructured data and metadata.

In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.

...

Benefits of the FileTable Feature

The goals of the FileTable feature include the following:

Windows API compatibility for file data stored within a SQL Server database. Windows API compatibility includes the following:

Non-transactional streaming access and in-place updates to FILESTREAM data.

A hierarchical namespace of directories and files.

Storage of file attributes, such as created date and modified date.

Support for Windows file and directory management APIs.

Compatibility with other SQL Server features including management tools, services, and relational query capabilities over FILESTREAM and file attribute data.

Thus FileTables remove a significant barrier to the use of SQL Server for the storage and management of unstructured data that is currently residing as files on file servers. Enterprises can move this data from file servers into FileTables to take advantage of integrated administration and services provided by SQL Server. At the same time, they can maintain Windows application compatibility for their existing Windows applications that see this data as files in the file system.

What Is a FileTable?

SQL Server provides a special table of files, also referred to as a FileTable, for applications that require file and directory storage in the database, with Windows API compatibility and non-transactional access. A FileTable is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes.

A FileTable provides the following functionality:

A FileTable represents a hierarchy of directories and files. It stores data related to all the nodes in that hierarchy, for both directories and the files they contain. This hierarchy starts from a root directory that you specify when you create the FileTable.

Every row in a FileTable represents a file or a directory.

Every row contains the following items. For more information about the schema of a FileTable, see FileTable Schema.

A FILESTREAM column for stream data and a file_id (GUID) identifier. (The FILESTREAM column is NULL for a directory.)

Both path_locator and parent_path_locator columns for representing and maintaining the file and directory hierarchy.

10 file attributes such as created date and modified date that are useful with file I/O APIs.

A type column that supports full-text search and semantic search over files and documents.

A FileTable enforces certain system-defined constraints and triggers to maintain file namespace semantics.

When the database is configured for non-transactional access, the file and directory hierarchy represented in the FileTable is exposed under the FILESTREAM share configured for the SQL Server instance. This provides file system access for Windows applications.

Additional Characteristics of FileTables

Some additional characteristics of FileTables include the following:

The file and directory data stored in a FileTable is exposed through a Windows share for non-transactional file access for Windows API based applications. For a Windows application, this looks like a normal share with its files and directories. Applications can use a rich set of Windows APIs to manage the files and directories under this share.

The directory hierarchy surfaced through the share is a purely logical directory structure that is maintained within the FileTable.

Calls to create or change a file or directory through the Windows share are intercepted by a SQL Server component and reflected in the corresponding relational data in the FileTable.

Windows API operations are non-transactional in nature, and are not associated with user transactions. However, transactional access to FILESTREAM data stored in a FileTable is fully supported, as is the case for any FILESTREAM column in a regular table.

FileTables can also be queried and updated through normal Transact-SQL access. They are also integrated with SQL Server management tools, and features such as backup.

..."

In a word, wow. This could be awesome, or a nightmare (or more likely both). It reminds me of the M drive feature of a past version of Exchange, where you could access Exchange via a drive mapping too. Feature which if used caused many people many headaches, and got pulled in later releases.

Anyway...

As someone how has to write apps to handle terabytes of unstructured data/files, this feature gave me goose bumps. I've been eyeing FileStream for a while now, but FileTables, which builds on FileStream, might be the tipping point for me (and might just be the feature that helps me sell Denali internally). I mean leveraging the power and storage of SQL Server without giving up file access API's... awesome.

I wonder that given the folders are logical and not physical, this mean there's no MAX_PATH issues?

This is another Denali feature I'll be keeping a very close eye on now.