بررسی تاثیر Unique بودن Clustered Index در عملکرد SQL Server

بررسی تاثیر Unique بودن Clustered Index، به طور خیلی ساده Clustered index  ترتیب فیزکی داده های موجود در جدول را مشخص می کند . برای مثال اگر شما جدولی به صورت زیر داشته باشد و بر روی آن یک کلاستر ایندکس به بر اساس فیلد Family ایجاد کنید چینش فیزیکی داده‌های موجود در این جدول بر اساس ستون Family خواهد بود. 

USE tempdb
GO
DROP TABLE IF EXISTS Person
GO
CREATE TABLE Person
(
Family varchar(100),
SecurityNumber varchar(20)
);
GO
CREATE CLUSTERED INDEX Cl_Person_family ON Person(Family);
Go

بررسی تاثیر Unique بودن Clustered Index

شباهت این نوع ایندکس بر اساس شماره صفحات کتاب می‌باشد. در واقع می‌توان گفت که شماره صفحه یک کتاب همان کلاستر ایندکس است که صفحات یک کتاب بر اساس آن مرتب شده است. هنگام ایجاد کلاستر ایندکس باید به این نکته توجه داشته باشید که هر جدول می تواند دارای یک clustered index باشد. دلیل این موضوع این است که چینش فیزیکی رکوردهای یک جدول فقط یک بار می‌تواند اتفاق بیافتد. تصویر زیر شکل یک کلاستر ایندکس را در SQL Server نمایش می‌دهد که در ساختار B-Tree ایجاد شده است.

معرفی NonClustered Indexes

در حالی که clustered index ترتیب فیزیکی رکوردها را مشخص می کنند ، Non-Clustered index ها ساختار مرتب شده مجزایی را برای یک یا چند ستون/فیلد ذخیره می کنند. پس اگر بخواهیم خیلی ساده تعریف کنیم. می‌توانیم بگوییم که SQL Server هنگام ایجاد Non-Clustered Index فیلدهایی که قرار است بر روی آن این نوع ایندکس را اعمال کند در فضای دیگری به صورت مرتب شده قرار داده و ارتباط هر کدام از رکوردهای موجود در این نوع ایندکس را با کلاستر ایندکس بر قرار می‌کند. پس باید به این نکته توجه داشته باشید که به کلید کلاستر ایندکس به صورت خودکار در Non-Clustered ایندکس‌ها مورد استفاده قرار می‌گیرد.

شباهت این نوع ایندکس شبیه به ایندکس موجود در انتهای کتاب است که  برای لیست واژگان مورد استفاده قرار می‌گیرد. این لیست بر اساس حروف الفبا مرتب شده است و به شماره صفحه حاوی این واژه اشاره می کند . اگر لیست واژگان در انتهای کتاب وجود نداشت ما باید کتاب را از ابتدا تا انتها ورق می زدیم. پیشنهاد میکنیم برای درک بهتر مفاهیم کوئری نویسی را مطالعه کنید.

و اما داستان اصلی

همه Clustered index  ها باید منحصر به فرد باشند تا Non-Clustered index ها بتوانند عیناً به سطر مربوطه (مانند شماره صفحه کتاب) مراجعه کنند. اما سوالی که امکان دارد پیش بیاید این است که امکان تعریف Clustered index غیر منحصر به فرد در SQL SERVER وجود دارد!  رفتار SQL SERVER در چنین وضعیتی چگونه خواهد بود؟

برای پاسخ به این سوال به مثال زیر توجه کنید.

فرض کنید جدولی دارید که Clustered index آن بر روی فیلد نام خانوادگی است و همچنین این جدول دارای یکNonclustered index بر روی شناسه امنیتی می باشد . (شناسه امنیتی منحصر به فرد است) اگر اطلاعات شخصی که دارای کد امنیتی ۲۲-۱۰ است را بخواهیم جستجو کنیم چه اتفاقی می افتد. این کد امنیتی به نام خانوادگی مزیدی اشاره می کند اما چند نام خانوادگی مزیدی در Cluster index موجود است. SQL SERVER اطلاعات کدام یک از آنها را نمایش دهد؟
پاسخ: SQL SERVER  فقط ۴ بایت اضافه تر به هر یک از کلید های تکراری اضافه می کند تا این مشکل حل شود .  توجه داشته باشید که SQL SERVER فقط زمانی که کلید تکراری اضافه می شود از ۴ بایت اضافه تر استفاده می کند. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.
ما می توانیم از طریق MetaData های موجود این رفتار را مشاهده کنیم . ستون keycnt در ویو sys.sysindexs  تعداد ستون هایی که برای تعریف ایندکس در نظر گرفته شده است را نشان می دهد . این ستون در کاتالوگ ویو sys.indexs موجود نیست و ما می توانیم آن را از طریق تابع indexproperty با تعیین آرگومان keycnt80  بدست آوریم . این آرگومان غیر مستند می باشد . در مثال زیر ما جدولی داریم که در آن ستون Family را به عنوان یک Clustered Index تعریف کرده‌ایم. اگر Clustered Index ما به صورت Unique نباشد.

USE tempdb
GO
DROP TABLE IF EXISTS Person
GO
CREATE TABLE Person
(
Family varchar(100),
SecurityNumber varchar(20)
);
GO
CREATE CLUSTERED INDEX Cl_Person_family ON Person(Family);
Go
SELECT
id,name,keycnt from sys.sysindexes
where id=OBJECT_ID ('Person')
GO
SELECT
index_id,name,
indexproperty(object_id, name, 'keycnt80') AS keycnt
FROM sys.indexes
WHERE object_id = OBJECT_ID ('Person');
GO

و در صورتیکه Clustered Index ما به صورت Unique باشد.

USE tempdb
GO
DROP TABLE IF EXISTS Person
GO
CREATE TABLE Person
(
Family varchar(100),
SecurityNumber varchar(20)
);
GO
CREATE UNIQUE CLUSTERED INDEX Cl_Person_family ON Person(Family);
Go
SELECT
id,name,keycnt from sys.sysindexes
where id=OBJECT_ID ('Person')
GO
SELECT
index_id,name,
indexproperty(object_id, name, 'keycnt80') AS keycnt
FROM sys.indexes
WHERE object_id = OBJECT_ID ('Person');
GO

دستور DROP TABLE IF EXISTS Person صرفاً در SQL Server 2016 قابل اجرا می باشد.

سخن پایانی

بررسی تاثیر Unique بودن Clustered Index، به طور مختصر مفهوم Clustered index  ترتیب قرارگیری داده های موجود در جدول را مشخص می کند. ما در این مقاله تاثیر Unique بودن Clustered Index در عملکرد SQL Server را بررسی کرده ایم. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.