Alapvetően nem egy új funkció, csak a Filestream továbbfejlesztése. Nekem pl. ez a funkció segít rendbetenni a dokumentumaimat- nesze nekem BigData. Lényegében a FileTable egy adott séma alapján – windows API-k segítségével – visszaadja egy “mappa” tartalmát, a fastruktúra megtartásával.
A FileTable oszlopai:
- stream_id: ez egy egyedi azonosító a mappára vagy file-ra. Típusa mi más lenne mint uniqueidentifier :-)
- file_stream: ez maga az adat, mappa, file. Itt érdemes megjegyezni egy nagyon fontos dolgot: a FileStream és a FileTable esetében a 2 GB limit nem érvényes, mint a varbinary(max) típusra. Lásd BOL: http://technet.microsoft.com/en-us/library/bb933993(SQL.105).aspx
- name: a file vagy a mappa neve.
- path_locator: ez egy hierarchyid típusú mező, ami a mappák fastruktúrájában azonosítja egy elem helyét.
- parent_path_locator: ez mutatja meg, hogy melyik elem áll az adott elem felett a hierarchiában.
- file_type: fájl típusa, mappa esetén NULL értéket vesz fel. a Full-Text Search esetében használható a TYPE COLUMN megadásakor.
- cached_file_size: a fájl méretét adja meg. Ez egy számított oszlop.
- creation_time: a file létrehozásának dátuma, melyet a Windows API ad vissza.
- last_access_time: a fájl utolsó hozzáférésének/megnyitásának időpontja.
- is_directory: ha az értéke 1, akkor mappa az adott elem.
- is_offline: Ha az értéke 1, akkor offline fájl.
- is_hidden: rejtett fájl, ha az értéke 1.
- is_readonly: csak olvasható, ha az értéke 1.
- is_archive: archív, ha az értéke 1.
- is_system: rendszer file, ha az értéke 1.
- is_temporary: ideiglenes file, ha az értéke 1.
Hogyan hozható létre FileTable?
A FileTable létrehozása előtt enegdélyezni kell pár dolgot az SQL Server-ben insatnce, majd adatbázis szinten. Az alábbi lépések segítségével felépítek egy egyszerű adatbázist, ahol mindenféle dokumentumokat fogok tárolni, majd keresni is fogok bennük.
Instance szintű beállítások
FILESTREAM engedélyezése
Adatbázis beállítások
A példa egyszerűsítése érdekében létrehozok egy teljesen új adatbázist. A példa adatbázis beállításait nem feltétlenül ajánlom éles környezetben :-)
Adatbázis létrehozása
Az alábbi script segítségével hozzuk létre a minta adatbázist:
USE [master];
GO
IF DATABASEPROPERTYEX('FileTableDB', 'Version') > 0
ALTER DATABASE [FileTableDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [FileTableDB];
GO
CREATE DATABASE [FileTableDB]
ON PRIMARY (NAME = N'FileTableDB', FILENAME = N'C:\Temp\FileTableDB.mdf'),
FILEGROUP [FILESTREAM_FG] CONTAINS FILESTREAM DEFAULT (NAME = N'FILESTREAM_FG',
FILENAME = N'C:\temp\FILESTREAM_STORAGE' , MAXSIZE = UNLIMITED)
LOG ON (NAME = N'FileTableDB_log', FILENAME = N'C:\temp\FileTableDB_log.ldf')
GO
Figyeljük meg, hogy van egy FILESTREAM FileGroup is, illetve győződjünk meg arról, hogy létezik a c:\temp mappa és az SQL Server service account-nak van joga a mappához.
Adatbázis szintű beállítások
Az alábbi beállítások szükségesek ahhoz, hogy az adatbázisunk tudja kezelni a FileTable táblákat.
ALTER DATABASE [FileTableDB] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME=N'FileTableDB_FileTablesDirectory') WITH NO_WAIT;
GO
A kódban két fontos opció is felfedezhető a FILESTREAM-el kapcsolatban:
- NON_TRANSACTED_ACCESS: három állapota van ennek a beállításnak: OFF, READ_ONLY és FULL. Én most a FULL-t állítom be.
- DIRECTORY_NAME: az a mappa neve, amiben az adatbázishoz tartozó FileTable adatok lesznek letárolva. Nagyon fontos, hogy ennek a mappának a neve egyedi kell legyen egy instance-en belül. Én azt a névkonvenciót szoktam alkalmazni, hogy <adatbázisnév>_<mappanév>. Így van esély arra, hogy egyedi lesz.
Ezekkel a beállításokkal már neki lehet állni létrehozni a FileTable-t.
A FileTable
Az alábbi kód segítségével létre tudjuk hozni a FileTable-t.
CREATE TABLE [dbo].[FileTableSample] AS FILETABLE
ON [PRIMARY] FILESTREAM_ON [FileTableDBFileStreamFG]
WITH
(
FILETABLE_DIRECTORY = N'FileTableSample',
FILETABLE_COLLATE_FILENAME = database_default
)
Két fontos dolgot kell megfigyelni itt is:
- a FILETABLE_DIRECTORY paraméter megadja, hogy a FILESTREAM mappában, mi legyen annak a mappának a neve, amiben ehhez FileTable-höz tartozó file és mappák legyenek.
- FILETABLE_COLLATE_FILENAME: ez adja meg a collation-jét a FileTable name oszlopának.
Ha kész is van a táblánk, akkor azt láthatjuk az SSMS-ben:
Adatbetöltés
Két módon lehet adatot tenni a FileTable-be:
- Transact-SQL segítségével és
- File másolással.
Transact-SQL adatbetöltés
Itt azért ne várjunk csodákat 
A file másolás a legjobb megoldás, az alábbi kód csak szemléltetés:
INSERT INTO [dbo].[FileTableSample] ([file_stream], [name])
VALUES (CAST('test text in the file' AS varbinary(max)), 'test.txt')
Hagyományos adatbetöltés
Itt a hagyományos másolás, xcopy, robocopy mehet. Alapvetően egy-egy FileTable-nek van saját elérési útvonala, ami lekérdezhető az alábbiak szerint:
SELECT FILETABLEROOTPATH('[dbo].[FileTableSample]')
Ez visszaadja az elérési utat, ami az adott FileTable-nek be lett állítva:
\\<Computername>\<FileStreamShareName>\<FileStreamDefaultFolder>\<YourFileTableFolder>\
Esetemben ez így nézett ki:
\\ROADRUNNER\SQL2012\FileTableDB_FileTablesDirectory\FileTableSample
Miután ebbe a mappába bemásoltam pár dokumentumot, a FileTable így nézett ki:
SELECT * FROM [FileTableSample]
Vigyázat, a file_stream oszlop visszaadja a file varbinary formátumát! Ez nem igazán jó ötlet, ha lekérdezéseket csinálunk az SSMS-ben, nem igazán szerette nekem sem a 8GB-os file-t