Friday 29 November 2013

What is SQL Server?

SQL Server is a relational database management system developed by Microsoft. That can respond to queries from other applications OR client machines.



Wednesday 27 November 2013

SQL Server: FileTables

Today, after attending a Session from Aasim Abdullah I have learned about FileTable, a new SQL Server 2012 feature based on FileStream. With FileTable, we can manage documents in a more professional way.


Filetable is to Store, Manage and Represents a Hierarchy of Files and Directories with their General Meta data (10 file attributes) in special tables of SQL server.

Filetable NON_TRANSACTED_ACCESS, Windows API Compatibility allow files access from windows explorer

Filetable is Fixed structured table and allow only two options with table creation
                1. Directory
                 2. Collation

SQL Server automatically creates fixed number of triggers, indexes and constraints on table with the creation of 'Filetable'
No.
Column Name
Data type
1
path_locator
rowguidcol
2
stream_id
varbinary(max)
3
file_stream
filestream
4
file_type
nvarchar(255)
5
Name
nvarchar(255)
6
parent_path_locator
hierarchyid
7
cached_file_size
bigint
8
creation_time
datetime2(4)
9
last_write_time
datetime2(4)
10
last_access_time
datetime2(4)
11
is_directory
bit
12
is_offline
bit
13
is_hidden
bit
14
is_readonly
bit
15
is_archive
bit
16
is_system
bit
17
is_temporary
bit

Following are basic important figures of Filetable

Prerequisite for Filetable are
       Instance Level
       FILESTREAM enabled
       Database Level
       FILESTREAM Filegroup defined
       Non-transnational ( None, Read Only, Read\Write) option enabled
       Valid directory (NTF Data Container) specified

Methods to load files in Filetable are
                  1.  Copy-Paste in Windows Explorer
                  2.  T-SQL  (Bulk Copy Operation)

Operations that we can perform on Filetable are
       Insert
       Update
       Delete
       User defined indexes, triggers and constraints
Security:
       Filetable supports only SQL Server T-SQL Level Security

File Table DMVs are
       sys.database_filestream_options
       sys.filetables
       sys.filetable_system_defined_objects  (System objects related to file table)
       sys.dm_tran_locks

Kill File Handles for urgent tasks are also allowed
EXEC sp_kill_filestream_non_transacted_handles @table_name = 'filetable_name';

Limitations
       Memory-mapped files (like Notepad and Paint) are not supported with NON_TRANSACTED_ACCESS, only way to get access to those files is remote access.
       Partition is not allowed on Filetable
       ‘Select Into’ statement can’t be used for Filetable creation  
       Up-to 15 levels Sub-Directory allowed
       Filetable ‘s File name limit doesn't match with Windows Explorer limit

Reference Links
I have found following links more use full

and a video of Technet is more easy to understand File Tables.
http://channel9.msdn.com/Events/TechDays/Techdays-2012-the-Netherlands/2270