ستون‌های پراکنده در SQL Server

ستون‌های پراکنده در SQL Server

ستون‌های پراکنده (Sparse Columns) در SQL Server ویژگی‌ای هستند که به ذخیره‌سازی بهینه داده‌های دارای مقدار NULL کمک می‌کنند.

در جداولی که تعداد زیادی ستون با داده‌های پراکنده وجود دارد، استفاده از این نوع ستون باعث کاهش مصرف فضای ذخیره‌سازی می‌شود.

برخلاف ستون‌های معمولی که مقدار NULL هم فضایی اشغال می‌کند، در ستون‌های Sparse مقدار NULL هیچ فضایی مصرف نمی‌کند، در نتیجه برای جداول با مقادیر NULL زیاد، این روش کارایی بالاتری دارد.

با این حال، اگر مقدارهای NULL کمتر از ۲۰-۳۰٪ داده‌ها را تشکیل دهند، استفاده از ستون‌های پراکنده ممکن است باعث افزایش فضای ذخیره‌سازی شود، زیرا مقدارهای غیر NULL در این نوع ستون‌ها اندکی فضای بیشتری نسبت به ستون‌های عادی مصرف می‌کنند.

یکی از قابلیت‌های مهم مرتبط با ستون‌های پراکنده، ستون‌های مجموعه‌ای (Column Sets) است که امکان مدیریت همه Sparse Columns را در قالب یک ستون XML فراهم می‌کند.

این ویژگی به کاربران اجازه می‌دهد که بدون نیاز به تغییر طراحی جدول، ستون‌های جدیدی اضافه کرده یا حذف کنند.

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

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

ویژگی‌های اصلی ستون‌های پراکنده

• مصرف فضای کمتر برای مقادیر NULL در مقایسه با ستون‌های معمولی.

• مصرف فضای بیشتر برای مقادیر غیر NULL نسبت به ستون‌های معمولی.

• امکان ترکیب با Column Set برای مدیریت بهتر ستون‌های پویا.

• محدودیت‌هایی در استفاده از برخی انواع داده و شاخص‌گذاری.

ایجاد ستون‌های پراکنده در SQL Server

برای ایجاد یک ستون پراکنده، کافی است هنگام تعریف ستون، از کلمه کلیدی SPARSE استفاده کنیم.

مثال زیر یک جدول با ستون‌های معمولی و پراکنده را نشان می‌دهد:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Description NVARCHAR(500) SPARSE NULL,
    Price DECIMAL(10,2) NOT NULL,
    DiscountPercentage DECIMAL(5,2) SPARSE NULL
);

در این مثال، ستون‌های Description و DiscountPercentage به‌عنوان ستون‌های پراکنده تعریف شده‌اند. اگر مقدار NULL در این ستون‌ها ذخیره شود، هیچ فضایی اشغال نمی‌کند.

 

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

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

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

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

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

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

 

مزایای استفاده از ستون‌های پراکنده

•  کاهش مصرف فضای ذخیره‌سازی

  SPARSE باعث صرفه‌جویی در فضای دیسک برای ستون‌هایی با مقدار زیاد NULL می‌شود.
  معمولاً 40% تا 60% صرفه‌جویی در فضا دارد، ولی برای داده‌های BIGINT و NVARCHAR(MAX) این مقدار بیشتر است.

• افزایش کارایی و عملکرد جداول بزرگ

  کاهش فضای مصرفی به معنی بهبود سرعت خواندن و نوشتن داده‌ها در جداول بزرگ است.
  صفحات کمتری از داده در Memory و Disk I/O اشغال می‌شود.

•  بهینه‌سازی ایندکس‌ها با FILTERED INDEX

  استفاده از Filtered Index روی SPARSE باعث بهینه‌سازی کوئری‌ها می‌شود:

CREATE INDEX IX_ActiveUsers ON Users(IsActive) WHERE IsActive IS NOT NULL;

• افزایش انعطاف‌پذیری برای جداول با فیلدهای متغیر

  مناسب برای داده‌های نیمه‌ساختاریافته (مثل فرم‌های پویا و تنظیمات متغیر کاربران).
  امکان مدیریت آسان صدها ستون پراکنده با Column Set

• مدیریت آسان داده‌های NULL در XML و JSON

داده‌های SPARSE می‌توانند در قالب XML ذخیره و پردازش شوند:

SELECT SparseData FROM MyTable FOR XML AUTO;

 محدودیت‌های ستون‌های پراکنده

اگرچه استفاده از ستون‌های پراکنده می‌تواند بهینه باشد، اما محدودیت‌هایی نیز وجود دارد که باید مدنظر قرار گیرند:

• مصرف فضای بیشتر برای مقادیر غیر NULL نسبت به ستون‌های معمولی.

• عدم پشتیبانی از برخی انواع داده مانند TEXT, IMAGE, TIMESTAMP, GEOGRAPHY و GEOMETRY.

• عدم امکان تعریف کلید خارجی (Foreign Key) روی ستون‌های پراکنده.

• عدم امکان ایجاد نمایه خوشه‌ای (Clustered Index) مستقیماً روی ستون‌های پراکنده.

 بررسی Column Set و مدیریت ستون‌های پراکنده

Column Set یک ویژگی در SQL Server است که امکان مدیریت مجموعه‌ای از ستون‌های پراکنده را در یک ستون XML فراهم می‌کند.

با استفاده از این ویژگی، می‌توان داده‌های تمام ستون‌های پراکنده را در قالب یک فیلد XML مدیریت کرد.

مثال:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(255) SPARSE NULL,
    PhoneNumber NVARCHAR(15) SPARSE NULL,
    Address NVARCHAR(300) SPARSE NULL,
    AdditionalData XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);

در این مثال، ستون AdditionalData به‌عنوان Column Set تعریف شده است که تمامی ستون‌های پراکنده را در خود نگه می‌دارد.

بهینه‌سازی ستون‌های پراکنده در SQL Server

برای بهینه‌سازی ستون‌های پراکنده (Sparse Columns) در SQL Server، از روش‌های زیر استفاده کنید:

•  استفاده از SPARSE برای داده‌های پراکنده

  اگر بیش از 50-60% مقادیر یک ستون NULL هستند، آن را SPARSE تعریف کنید:

ALTER TABLE MyTable ADD MyColumn INT SPARSE NULL;

• ایجاد COLUMN SET برای مدیریت چندین ستون پراکنده

  اگر تعداد زیادی SPARSE دارید، از COLUMN SET استفاده کنید:

CREATE TABLE MyTable (
    ID INT PRIMARY KEY,
    Col1 INT SPARSE NULL,
    Col2 NVARCHAR(100) SPARSE NULL,
    SparseData XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);

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

• استفاده از FILTERED INDEX برای بهینه‌سازی جستجوها

  ایندکس‌های فیلترشده فقط مقادیر غیر NULL را نگه می‌دارند:

CREATE INDEX IX_MyColumn ON MyTable(MyColumn) WHERE MyColumn IS NOT NULL;

• بهینه‌سازی Storage با Data Compression

  برای کاهش فضای ذخیره‌سازی داده‌های غیر NULL در Sparse Columns:

ALTER TABLE MyTable REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);

• بررسی تأثیر با DMVها

  برای تحلیل تأثیر SPARSE در مصرف فضای NULLها:

SELECT name, is_sparse FROM sys.columns WHERE object_id = OBJECT_ID('MyTable');

مثال پروژه محور از  ستون‌های پراکنده در SQL Server

پروژه: طراحی پایگاه داده برای مدیریت فرم‌های داینامیک با استفاده از ستون‌های پراکنده (Sparse Columns)
 

سناریو پروژه

یک شرکت نیاز دارد که فرم‌های داینامیک را برای جمع‌آوری اطلاعات مختلف از کاربران ذخیره کند.

از آنجایی که هر کاربر ممکن است فیلدهای متفاوتی را پر کند، ذخیره‌سازی سنتی باعث اشغال فضای زیاد می‌شود.

برای بهینه‌سازی فضای ذخیره‌سازی، از ستون‌های پراکنده (Sparse Columns) در SQL Server استفاده خواهیم کرد.

• طراحی جدول با ستون‌های پراکنده

  جدول DynamicForms برای ذخیره‌سازی داده‌های ورودی کاربران طراحی شده است.
  در این جدول، برخی فیلدها مقدار NULL زیادی دارند، بنابراین از ستون‌های پراکنده برای بهینه‌سازی استفاده شده است.

CREATE TABLE DynamicForms (
    FormID INT PRIMARY KEY IDENTITY(1,1),
    UserID INT NOT NULL,
    FullName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(255) SPARSE NULL,
    PhoneNumber NVARCHAR(20) SPARSE NULL,
    Address NVARCHAR(500) SPARSE NULL,
    DateOfBirth DATE SPARSE NULL,
    AdditionalInfo XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
    CreatedAt DATETIME DEFAULT GETDATE()
);

ویژگی‌ها

• • ستون‌هایی مانند Email، PhoneNumber، Address و DateOfBirth به‌صورت SPARSE تعریف شده‌اند، چون در بسیاری از فرم‌ها مقدار NULL دارند.
  • ستون AdditionalInfo از نوع Column Set است که امکان دسترسی به تمامی ستون‌های پراکنده را در قالب XML فراهم می‌کند.
  • مقدار CreatedAt برای ذخیره زمان ایجاد فرم استفاده شده است.

• درج داده در جدول

  برای وارد کردن داده‌ها در جدول، به‌راحتی می‌توان داده‌های متفاوتی را برای هر کاربر ذخیره کرد:

INSERT INTO DynamicForms (UserID, FullName, Email, CreatedAt)  
VALUES (1, 'محمد رضایی', 'm.rezaei@example.com', GETDATE());

INSERT INTO DynamicForms (UserID, FullName, PhoneNumber, Address, CreatedAt)  
VALUES (2, 'علی کریمی', '09121234567', 'خیابان انقلاب، تهران', GETDATE());

INSERT INTO DynamicForms (UserID, FullName, DateOfBirth, CreatedAt)  
VALUES (3, 'سارا احمدی', '1992-05-15', GETDATE());

نتیجه

هر کاربر می‌تواند فقط داده‌های مربوط به خودش را پر کند، بدون این‌که فضای اضافی برای مقادیر NULL مصرف شود.
ستون‌های NULL هیچ فضایی را اشغال نمی‌کنند، بنابراین ذخیره‌سازی کارآمدتر می‌شود.

•  نمایش داده‌ها با Column Set

  می‌توان تمامی داده‌های پراکنده را در قالب XML مشاهده کرد:

SELECT FormID, UserID, FullName, AdditionalInfo  
FROM DynamicForms;

این کوئری تمامی داده‌های وارد شده را نمایش می‌دهد و مقادیر NULL در ستون‌های پراکنده حذف شده‌اند.