SQL Server Mentalist

Hi Friends,

As every body is spreading there knowledge regarding New SQL Server Denali CTP 3 Now its my turn to give at least little bit about same.

So here we go lets have an series of blog on Denali Features !!

Todays topic is easy and interesting.

Introduction to FileTables

File Tables Feature is nothing but Storing Data into SQL Server as if we are storing it in Windows file system and having all operation similar to that !!

Is not sounds great if I have all my files in server and can be access as anywhere like I am accessing it from My PC only

The FileTable feature have full 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.

The FileTable feature builds on top of SQL Server FILESTREAM technology. FileTable is meant for storing and managing unstructured data in SQL Server,also we can access via the file system.

A FileTable provides the following main Function

• 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.
• 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.

FileTable stores information like

• 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.

What I have to learn if I want to known everything about file table

Just read the following topic in very cool way

1. Enable the Prerequisites for FileTable – Describes how to enable the prerequisites for creating and using FileTables.
2. Create, Alter, and Drop FileTables – Describes how to create a new FileTable, or alter or drop an existing FileTable.
3. Load Files into FileTables – Describes how to load or migrate files into FileTables.
4. Work with Directories and Paths in FileTables – Describes the directory structure in which the files are stored in FileTables.
5. Access FileTables with Transact-SQL – Describes how Transact-SQL data manipulation language (DML) commands work with FileTables.
6. Access FileTables with File I\O APIs – Describes how file system I/O works on a FileTable.
7. Manage FileTables – Describes common administrative tasks for managing FileTables.
8. FileTable Schema – Describes the pre-defined and fixed schema of a FileTable.
9. FileTable Compatibility with Other SQL Server Features – Describes how FileTables work with other features of SQL Server.
10. .FileTable DDL, Functions, Stored Procedures, and Views – Lists the Transact-SQL statements and the SQL Server database objects that have been added or changed to support the FileTable feature.

Some more walk through FileTables

• 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.

  We will surely have some practical or direct demoing kind of thing on same !!

Hope this helps !!

Thanks for reading my blog !!

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 50 : SQL Server Denali Feature # 1- FileTables # 2 – Demo , Scripting

· BISQL # 51 : SQL Server Denali Feature # 2 – Sequence number

· BISQL # 51 : SQL Server Denali Feature # 2 – Sequence number –All Explanation !!

· BISQL # 52 : How To Check Database log file Details and Who Access last

· BISQL # 53 : SQL Server Denali Feature # 3 – AlwaysOn

Hope you will like this post on Denali feature Introduction, Theory.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

        For More information related to BI World visit my Mentalist Blog

                    Link Resource Blog >> Daily Interesting links

                    SQL Server Mentalist >> SQL Learning Blog

                    Business Intelligence Mentalist >> BI World

                                   Connect With me on

         | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                  Copyright © 2011 – 2012 Vishal Pawar