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

مقدمه

به طور خیلی ساده 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

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

معرفی NonClustered Indexes

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

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

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

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

فرض کنید جدولی دارید که Clustered index آن بر روی فیلد نام خانوادگی است و همچنین این جدول دارای یکNonclustered index بر روی شناسه امنیتی می باشد . (شناسه امنیتی منحصر به فرد است)
اگر اطلاعات شخصی که دارای کد امنیتی ۲۲-۱۰ است را بخواهیم جستجو کنیم چه اتفاقی می افتد. این کد امنیتی به نام خانوادگی مزیدی اشاره می کند اما چند نام خانوادگی مزیدی در Cluster index موجود است. 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 قابل اجرا می باشد