File Tables در Sql Server

FileTables در SQL Server

در SQL Server ابزارها و قابلیت‌های متعددی برای مدیریت داده‌ها وجود دارد.

یکی از امکانات جالب و کاربردی که از SQL Server 2012 به بعد معرفی شد، قابلیت FileTable است.

این ویژگی بر پایه‌ی FileStream ساخته شده و به ما اجازه می‌دهد فایل‌های غیرساخت‌یافته (Unstructured Data) مانند تصاویر، ویدئوها، اسناد Word یا PDF را در کنار داده‌های ساخت‌یافته‌ی پایگاه داده ذخیره و مدیریت کنیم.

به طور کلی SQL Server برای ذخیره‌ی داده‌های باینری دو روش اصلی دارد:

• استفاده از نوع داده‌ی VARBINARY(MAX)
• استفاده از FileStream برای ذخیره‌ی فایل‌ها در سیستم فایل ولی مدیریت آن‌ها توسط SQL Server

FileTable در واقع یک گسترش بر FileStream است که امکانات زیر را اضافه می‌کند:

• اجازه می‌دهد فایل‌ها را مستقیماً از طریق ویندوز اکسپلورر یا برنامه‌های دیگر در پوشه‌ای مخصوص کپی، حذف یا ویرایش کنیم.
• SQL Server به‌صورت خودکار تغییرات را در یک جدول خاص (FileTable) منعکس می‌کند.
• کاربران می‌توانند با استفاده از T-SQL به متادیتا و محتوای فایل‌ها دسترسی داشته باشند.

به بیان ساده:

FileTable یک جدول خاص است که نمایی از پوشه‌ها و فایل‌های ذخیره‌شده در سیستم فایل ایجاد می‌کند و این پوشه توسط SQL Server کنترل می‌شود.

 چرا از FileTable استفاده کنیم؟

• مدیریت فایل‌ها و داده‌ها در یک مکان

  هم داده‌های ساخت‌یافته (SQL) و هم فایل‌های غیرساخت‌یافته در یک پایگاه داده مدیریت می‌شوند.

• دسترسی همزمان

  برنامه‌ها می‌توانند فایل‌ها را مثل یک فولدر عادی مدیریت کنند، در حالی که SQL Server همچنان به آن‌ها دسترسی دارد.

• جستجو و Query

  می‌توان روی نام فایل‌ها، مسیر آن‌ها یا حتی محتوا (با کمک Full-Text Search) کوئری نوشت.

• سازگاری با برنامه‌های قدیمی

  نیازی نیست نرم‌افزارها تغییر کنند؛ آن‌ها همچنان فایل‌ها را در یک پوشه باز می‌کنند.

پیش‌نیازهای ایجاد FileTable

برای استفاده از FileTable باید چند مرحله مقدماتی انجام شود:

• فعال‌سازی قابلیت FileStream

• در SQL Server Configuration Manager قسمت FileStream را فعال کنید.

• اجازه دسترسی برای Transact-SQL و Win32 را فعال کنید.

تنظیم سطح پایگاه داده

در دیتابیس باید ویژگی‌ها فعال شوند:

• NON_TRANSACTED_ACCESS برای تعیین سطح دسترسی پوشه
• DIRECTORY_NAME برای تعیین نام پوشه‌ای که فایل‌ها در آن ذخیره می‌شوند

مثال:

ALTER DATABASE MyDB
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyDBFiles' );

ایجاد FileTable (CREATE TABLE … AS FILETABLE)

• سینتکس کلی

CREATE TABLE [schema_name].[table_name] AS FILETABLE
WITH (
    FILETABLE_DIRECTORY = 'DirectoryName',
    FILETABLE_COLLATE_FILENAME = database_default
);

FILETABLE_DIRECTORY: نام پوشه‌ای که فایل‌ها در آن ذخیره می‌شوند.

FILETABLE_COLLATE_FILENAME: کلاسیون مورد استفاده برای نام فایل‌ها.

• مثال ساده

CREATE TABLE dbo.Documents AS FILETABLE
WITH (
    FILETABLE_DIRECTORY = 'DocsFolder',
    FILETABLE_COLLATE_FILENAME = database_default
);

این دستور یک FileTable به نام Documents ایجاد می‌کند. همزمان یک پوشه به نام DocsFolder در مسیر FILESTREAM دیتابیس ساخته می‌شود.

هر فایلی که در این پوشه قرار بگیرد، به صورت رکورد در جدول Documents قابل مشاهده است.

 

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

 

 

مشاهده ساختار FileTable در Sql Server

وقتی یک FileTable ایجاد می‌کنید، به صورت خودکار ستون‌هایی خاص برای مدیریت فایل‌ها اضافه می‌شود.

نمونه ستون‌ها:

• stream_id

  شناسه فایل (UniqueIdentifier)

• file_stream

  محتوای فایل (VARBINARY(MAX) FILESTREAM)

• name

  نام فایل یا پوشه

• path_locator

  مسیر فایل در ساختار درختی

• creation_time, last_write_time, last_access_time

  تاریخچه تغییرات

 درج و مشاهده داده‌ها در Sql Server

اگر فایل را در ویندوز در پوشه مربوطه قرار دهید، رکورد آن در FileTable ظاهر می‌شود:

SELECT name, creation_time FROM dbo.Documents;

برعکس، اگر رکوردی در جدول درج کنید (با وارد کردن محتوای باینری)، فایل آن در پوشه ظاهر خواهد شد.

استفاده از FileTables در Sql Server

می‌توان روی ستون‌ها کوئری زد:

SELECT name, file_stream
FROM dbo.Documents
WHERE name LIKE '%.pdf';

می‌توان از Full-Text Search استفاده کرد تا در محتوای فایل‌ها (مثلاً Word یا PDF) جستجو انجام داد.

می‌توان از برنامه‌های عادی ویندوز (Explorer، Word، Photoshop) برای باز کردن و تغییر فایل‌ها استفاده کرد.

حذف FileTable (DROP TABLE)

• سینتکس

DROP TABLE [schema_name].[table_name];

• مثال

DROP TABLE dbo.Documents;

این دستور جدول FileTable را حذف می‌کند.

پس از حذف، فایل‌ها و پوشه‌های مربوط به آن نیز از سیستم فایل حذف می‌شوند (زیرا SQL Server آن‌ها را مدیریت می‌کند).

مدیریت FileTable در Sql Server

• برای مشاهده لیست FileTables:

SELECT * FROM sys.filetables;

• برای مشاهده مسیر پوشه:

SELECT DB_NAME(database_id) AS DBName, directory_name
FROM sys.database_filestream_options;

• برای تغییر تنظیمات دسترسی:

ALTER DATABASE MyDB
SET FILESTREAM ( NON_TRANSACTED_ACCESS = READ_ONLY );

نکات امنیتی

• دسترسی به پوشه FileTable باید محدود شود؛ هر کاربری که دسترسی به پوشه داشته باشد، می‌تواند فایل‌ها را تغییر دهد.
• برای کنترل دسترسی بهتر است مجوزهای NTFS و مجوزهای SQL Server هماهنگ شوند.
• حذف یک رکورد از FileTable معادل حذف فایل است. بنابراین باید سیاست‌های Backup و Audit در نظر گرفته شود.

محدودیت‌ها

• یک FileTable فقط می‌تواند در دیتابیس‌هایی ایجاد شود که FILESTREAM فعال باشد.
• FileTable نمی‌تواند کلید اولیه (Primary Key) اختصاصی داشته باشد (SQL Server خودش ستون‌ها را مدیریت می‌کند).
• فقط یک پوشه (Directory) برای هر FileTable وجود دارد.
• عملیات TRUNCATE TABLE پشتیبانی نمی‌شود.
• FileTable بیشتر برای ذخیره‌سازی فایل‌ها مناسب است، نه برای تراکنش‌های پیچیده.

کاربرد File Tables در Sql Server

• مدیریت اسناد سازمانی

  ذخیره PDFها، Wordها و اسکن‌ها در دیتابیس.

• سیستم‌های چندرسانه‌ای

  نگهداری عکس‌ها یا ویدئوها در کنار داده‌های کاربری.

• سیستم‌های قانونی یا پزشکی

  که نیاز به تاریخچه دقیق فایل‌ها و Audit Trail دارند.

• یکپارچگی داده‌ها

  امکان Join بین متادیتای فایل‌ها و جداول داخلی پایگاه داده.

اسکریپت کامل نمونه

-- ایجاد دیتابیس با پشتیبانی FileStream
CREATE DATABASE MyDB
ON PRIMARY
( NAME = N'MyDB_data', FILENAME = N'C:\SQLData\MyDB.mdf'),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM
( NAME = N'MyDB_FS', FILENAME = N'C:\SQLData\FS' )
LOG ON
( NAME = N'MyDB_log', FILENAME = N'C:\SQLData\MyDB.ldf');
GO

-- فعال‌سازی تنظیمات دیتابیس
ALTER DATABASE MyDB
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyDBFiles' );
GO

-- ایجاد FileTable
USE MyDB;
GO
CREATE TABLE dbo.Documents AS FILETABLE
WITH (
    FILETABLE_DIRECTORY = 'DocsFolder',
    FILETABLE_COLLATE_FILENAME = database_default
);
GO

-- درج و مشاهده فایل‌ها
SELECT name, creation_time, file_stream
FROM dbo.Documents;

-- حذف FileTable
DROP TABLE dbo.Documents;

جمع‌بندی

FileTable یکی از امکانات قدرتمند SQL Server است که مدیریت فایل‌های غیرساخت‌یافته را ساده می‌کند.

برای ایجاد آن از دستور CREATE TABLE … AS FILETABLE استفاده می‌شود.

قبل از ایجاد باید FILESTREAM در سطح سرور و دیتابیس فعال شود.

فایل‌ها از طریق ویندوز یا T-SQL قابل دسترسی هستند.

برای حذف آن از دستور DROP TABLE استفاده می‌شود.

مزیت اصلی FileTable ترکیب سادگی مدیریت فایل‌ها با توان SQL Server در کوئری‌نویسی و امنیت است.

این قابلیت در سازمان‌هایی که حجم بالایی از فایل‌ها دارند و نیازمند کنترل و جستجو هستند، می‌تواند بسیار مؤثر باشد.