آموزش جامع SQL Server (جلسه ۱۹: ایندکس‌ها - بخش ۱)
1398/03/26 14:34 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۱۹: ایندکس‌ها - بخش ۱)

ایندکس‌ها ساختمان‌های داده‌ای خاص مرتبط با جداول یا viewها هستند که به افزایش سرعت query کمک می‌کنند. SQL Server دو نوع ایندکس دارد: ایندکس خوشه‌ای یا clustered و ایندکس غیر خوشه‌ای یا non-clustered.

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

در این دو جلسه، هر چیزی که لازم است راجع به ایندکس‌ها بدانید را خواهید آموخت، تا بتوانید یک استراتژی ایندکس خوب ایجاد کنید و queryهای خود را بهینه‌سازی کنید.

  • ایندکس‌های خوشه‌ای (clustered): معرفی ایندکس‌های خوشه‌ای و آموختن چگونگی ایجاد ایندکس‌های خوشه‌ای برای جداول.

  • ایجاد ایندکس‌های غیر خوشه‌ای (nonclustered): آموزش چگونگی ایجاد ایندکس‌های غیر خوشه‌ای با استفاده از دستور CREATE INDEX.

  • تغییر نام ایندکس‌ها: جایگزینی نام فعلی ایندکس با نام جدید با استفاده از یک stored procedure به نام sp_rename و SQL Server Management Studio.

  • غیر فعال کردن ایندکس‌ها: آموزش چگونگی غیر فعال کردن ایندکس‌های یک جدول برای بی‌تأثیر ساختن ایندکس‌ها.

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

  • ایندکس‌های یکتا: تقویت Unique یا یکتایی مقادیر در یک یا چند ستون.

  • ایندکس‌های دارای ستون: چگونگی اضافه کردن ستون‌های غیر کلید به ایندکس غیر خوشه‌ای برای بهبود سرعت queryها را توضیح می‌دهد.

  • ایندکس‌های فیلتر شده: آموزش چگونگی ایجاد یک ایندکس در بخشی از ردیف‌های یک جدول.

  • ایندکس‌ها در ستون‌های محاسبه‌ای: آموزش چگونگی شبیه‌سازی ایندکس‌های تابع محور با استفاده از ایندکس‌های ستون‌های محاسبه‌ای.

در ادامه به توضیح کامل تمام موارد بالا خواهیم پرداخت. با ما همراه باشید!

ایندکس‌های خوشه‌ای در SQL Server

کد زیر یک جدول جدید به نام production.parts ایجاد می‌کند که شامل دو ستون به نام‌های part_id و part_name ایجاد می‌کند:

CREATE TABLE production.parts(
    part_id   INT NOT NULL, 
    part_name VARCHAR(100)
);

و این دستور چند ردیف به جدول production.parts اضافه می‌کند:

INSERT INTO 
    production.parts(part_id, part_name)
VALUES
    (1,'Frame'),
    (2,'Head Tube'),
    (3,'Handlebar Grip'),
    (4,'Shock Absorber'),
    (5,'Fork');

جدول production.parts دارای کلید اصلی یا Primary Key نیست، بنابراین SQL Server ردیف‌هایش را در یک ساختار بی‌نظم به نام heapذخیره می‌کند.

وقتی برای به دست آوردن داده‌ها از جدول production.parts کد یا query می‌نویسید، بهینه‌ساز query باید کل جدول را اسکن کند تا داده‌های صحیح و مناسب را بیابد.

برای مثال، این دستور بخشی که part id آن برابر با ۵ است را می‌یابد.

SELECT 
    part_id, 
    part_name
FROM 
    production.parts
WHERE 
    part_id = 5;

اگر طرح اجرای تخمینی را در SQL Server Management Studio نمایش دهید، می‌توانید ببینید که SQL Server چگونه به طرح query زیر دست یافته است:

طرح اجرای تخمینی

توجه کنید که برای نمایش طرح اجرای تخمینی در SQL Server Management Studio، باید روی کلید زیر کلیک کنید یا query را انتخاب کرده و کلید‌های میانبر Ctrl+L را فشار دهید:

رای نمایش طرح اجرای تخمینی در SQL Server Management Studio، باید روی این کلید کلیک کنید

چون جدول production.parts فقط دارای ۵ ردیف است، query خیلی سریع اجرا خواهد شد. به هر حال، اگر جدول شامل تعداد زیادی جدول است، SQL Server برای جستجوی داده‌ها به زمان و منابع خیلی زیادی نیاز دارد.

برای حل این مشکل، SQL Server یک ساختار خاص برای افزایش سرعت بازگشت ردیف‌ها از یک جدول فراهم می‌کند که به آن ایندکس می‌گوییم.

SQL Server دارای دو نوع ایندکس است: ایندکس خوشه‌ای و ایندکس غیر خوشه‌ای. در این قسمت، روی ایندکس خوشه‌ای تمرکز خواهیم کرد.

یک ایندکس خوشه‌ای ردیف‌های داده را در یک ساختار منظم بر اساس مقادیر کلیدی‌اش ذخیره می‌کند. هر جدول فقط دارای یک ایندکس خوشه‌ای است، چون ردیف‌های داده فقط می‌توانند به یک ترتیب ذخیره شوند. جدولی که دارای ایندکس خوشه‌ای است، جدول خوشه‌ای نام دارد.

تصویر زیر ساختار یک ایندکس خوشه‌ای را نشان می‌دهد:

ساختار یک ایندکس خوشه‌ای

ایندکس خوشه‌ای، داده‌ها را با استفاده از یک ساختار خاص به نام B-tree (یا درخت متوازن) سازماندهی می‌کند که این درخت امکان جستجو، insert یا ورود، به روز رسانی‌ها و حذف‌ها در زمان استهلاک لگاریتمی (logarithmic amortized time) را امکان پذیر می‌سازد.

در این ساختار، گره بالایی درخت متوازن، گره ریشه (root node) نام دارد. گره‌ها در سطح پایین، گره‌های برگ (leaf node) نام دارند. هر سطح ایندکس بین ریشه و گره‌های برگ به عنوان سطوح میانه (interediate levels) شناخته می‌شوند.

در درخت متوازن، گره ریشه و گره‌های سطح میانه شامل صفحات ایندکسی هستند که ردیف‌های ایندکس را در خود نگه می‌دارند. گره‌های برگ حاوی صفحات داده از جدول مورد نظر هستند.

صفحات در هر سطح از ایندکس با استفاده از یک ساختار دیگر به نام لیست پیوندی دو طرفه (doubly-linked list) به هم لینک و متصل می‌شوند.

ایندکس خوشه‌ای در SQL Server و ویژگی کلید اصلی

وقتی یک جدول با یک کلید اصلی ایجاد می‌کنید، SQL Server به صورت خودکار یک ایندکس خوشه‌ای مشابه بر اساس ستون‌های دارای ویژگی کلید اصلی ایجاد می‌کند.

این دستور یک جدول جدید به نام production.part_prices با یک کلید اصلی ایجاد می‌کند که شامل دو ستون به نام‌های part_id و valid_form است.

CREATE TABLE production.part_prices(
    part_id int,
    valid_from date,
    price decimal(18,4) not null,
    PRIMARY KEY(part_id, valid_from) 
);

دو ستون به نام‌های part_id و valid_form

اگر به یک جدول موجود که از قبل دارای یک ایندکس خوشه‌ای است، یک ویژگی کلید اصلی اضافه کنیم، آنگاه SQL Server کلید اصلی را مجبور به استفاده از یک ایندکس غیر خوشه‌ای می‌کند:

این دستور یک کلید اصلی برای جدول production.parts تعریف می‌کند:

ALTER TABLE production.parts
ADD PRIMARY KEY(part_id);

SQL Server یک ایندکس غیر خوشه‌ای برای کلید اصلی ایجاد می‌کند.

SQL Server یک ایندکس غیر خوشه‌ای برای کلید اصلی ایجاد می‌کند.

استفاده از دستور CREATE CLUSTERED INDEX برای ایجاد یک ایندکس خوشه‌ای

در صورتی که یک جدول کلید اصلی نداشته باشد (که یک امر خیلی کمیاب است)، می‌توانید از دستور CREATE CLUSTERED INDEX برای تعریف یک ایندکس خوشه‌ای برای جدول استفاده کنید.

کد زیر یک ایندکس خوشه‌ای برای جدول production.parts ایجاد می‌کند:

CREATE CLUSTERED INDEX ix_parts_id
ON production.parts (part_id);  

اگر گره Indexes موجود در زیر نام جدول را باز کنید، نام جدید ایندکس (ix_parts_id) با نوع clustered را خواهید دید.

اگر گره Indexes موجود در زیر نام جدول را باز کنید، نام جدید ایندکس (ix_parts_id) با نوع clustered را خواهید دید.

وقتی کد زیر را اجرا می‌کنید، SQL Server در ایندکس جستجو می‌کند (جستجوی ایندکس خوشه‌ای) تا ردیف را بیابد، که این روش سریع‌تر از روش اسکن کل جدول عمل می‌کند.

SELECT 
    part_id, 
    part_name
FROM 
    production.parts
WHERE 
    part_id = 5;

طرح اجرای تخمینی

Syntax دستور CREATE CLUSTERED INDEX در SQL Server

Syntax برای ایجاد یک ایندکس خوشه‌ای به شکل زیر است:

CREATE CLUSTERED INDEX index_name
ON schema_name.table_name (column_list);  

در این syntax:

  • ابتدا، نام ایندکس خوشه‌ای پس از دستور CREATE CLUSTERED INDEX مشخص می‌کنید.

  • سپس، شما و نام جدولی که می‌خواهید ایندکس ایجاد کنند را مشخص می‌کنید.

  • بعد، یک یا تعداد بیشتری ستون در ایندکس وارد می‌کنید.

به این شکل، به صورت کامل راجع به ایندکس‌های خوشه‌ای و چگونگی استفاده از دستور CREATE CLUSTERED INDEX برای تعریف یک ایندکس خوشه‌ای جدید برای یک جدول را آموختید.

دستور CREATE INDEX در SQL Server

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

این ایندکس در واقع یک کپی از ستون‌های منتخب داده از یک جدول است که این ستون‌ها دارای لینک و ارتباط با جدول مربوطه هستند.

مشابه ایندکس خوشه‌ای، یک ایندکس غیر خوشه‌ای از ساختار درخت متوازن برای سازماندهی داده‌ها استفاده می‌کند.

یک جدول می‌تواند یک یا تعداد بیشتری ایندکس غیر خوشه‌ای داشته باشد و هر ایندکس غیر خوشه‌ای می‌تواند شامل یک یا تعداد بیشتری ستون از جدول باشد.

تصویر زیر ساختار ایندکس غیر خوشه‌ای را نشان می‌دهد:

ساختار ایندکس غیر خوشه‌ای

گره‌های برگ علاوه بر ذخیره مقادیر کلیدی ایندکس، همچنین اشاره‌گرهای ردیف به سمت ردیف‌های داده را که حاوی مقادیر کلیدی هستند را نیز ذخیره می‌کنند. این اشاره‌گرهای ردیف همچنین با نام یابنده‌های ردیف (row locators) نیز شناخته می‌شوند.

اگر جدول مورد نظر یک جدول خوشه‌ای است، اشاره‌گر ردیف نیز یک کلید ایندکس خوشه‌ای است. در صورتی که جدول مورد نظر یک heap باشد، اشاره‌گر ردیف به سمت ردیف جدول اشاره می‌کند.

دستور CREATE INDEX در SQL Server

برای ایجاد یک ایندکس غیر خوشه‌ای، می‌توانید از دستور CREATE INDEX استفاده کنید:

CREATE [NONCLUSTERED] INDEX index_name
ON table_name(column_list);

در این syntax:

  • ابتدا، نام شاخص بعد از دستور CREATE NONCLUSTERED INDEX مشخص می‌شود. توجه کنید که استفاده از کلیدواژه NONCLUSTERED اختیاری است.

  • سپس، نام جدولی که می‌خواهید در آن ایندکس ایجاد کنید و همچنین لیستی از ستون‌های آن جدول را به عنوان ستون‌های کلیدی ایندکس مشخص می‌کنید.

مثال‌های دستور CREATE INDEX در SQL Server

از جدول sales.customers از پایگاه داده نمونه Bikestores برای مثال‌ها استفاده می‌کنیم.

جدول sales.customers

جدول sales.customers یک جدول خوشه‌ای است، چون دارای کلید اصلی به نام custoer_id است.

الف) مثال استفاده از دستور CREATE INDEX برای ایجاد یک ایندکس غیر خوشه‌ای برای یک ستون

این دستور مشتریانی که در Atwater سکونت دارند را می‌یابد:

SELECT 
    customer_id, 
    city
FROM 
    sales.customers
WHERE 
    city = 'Atwater';

اگر طرح اجرای تخمینی را نمایش دهید، مشاهده خواهید کرد که بهینه‌سازی query ایندکس خوشه‌ای را برای یافت ردیف اسکن می‌کند. این بدان دلیل است که جدول sales.customers برای ستون city ایندکس ندارد.

طرح اجرای تخمینی

برای بهبود سرعت این query، می‌توانید یک ایندکس جدید به نام ix_customers_city برای ستون city ایجاد کنید:

CREATE INDEX ix_customers_city
ON sales.customers(city);

حالا، اگر طرح اجرای تخمینی query بالا را دوباره نمایش دهید، متوجه خواهید شد که بهینه‌ساز query از ایندکس غیر خوشه‌ای (ix_customers_city) استفاده می‌کند:

طرح اجرای تخمینی

ب) مثال استفاده از دستور CREATE INDEX برای ایجاد یک ایندکس غیر خوشه‌ای برای چند ستون

دستور زیر مشتری که نام خانوادگی او Berg و نام او Monika است را می‌یابد:

SELECT 
    customer_id, 
    first_name, 
    last_name
FROM 
    sales.customers
WHERE 
    last_name = 'Berg' AND 
    first_name = 'Monika';

طرح اجرای تخمینی

بهینه‌ساز query ایندکس خوشه‌ای را اسکن می‌کند تا مشتری را بیابد.

برای افزایش سرعت بازگرداندن داده‌ها، می‌توانید یک ایندکس غیر خوشه‌ای ایجاد کنید که شامل هر دو ستون‌های last_name و first_name باشد:

CREATE INDEX ix_customers_name 
ON sales.customers(last_name, first_name);

حالا، بهینه‌ساز query از ایندکس ix_customers_name برای یافتن مشتری استفاده می‌کند:

SELECT 
    customer_id, 
    first_name, 
    last_name
FROM 
    sales.customers
WHERE 
    last_name = 'Berg' AND 
    first_name = 'Monika';

طرح اجرای تخمینی

وقتی  یک ایندکس غیر خوشه‌ای ایجاد می‌کنید که شامل چند ستون است، ترتیب ستون‌ها در ایندکس بسیار مهم است. باید ستون‌هایی که گاهی از آن‌ها برای دریافت داده‌ها استفاده می‌کنید را در ابتدای لیست ستون قرار دهید.

برای مثال، دستور زیر مشتریانی که نام خانوادگی آن‌ها Albert است را می‌یابد. چون نام خانوادگی سمت چپ‌ترین ستون در ایندکس است، بهینه‌ساز query می‌تواند از قدرت ایندکس بهره برده و از روش جستجوی ایندکس برای جستجو استفاده کند:

SELECT 
    customer_id, 
    first_name, 
    last_name
FROM 
    sales.customers
WHERE 
    last_name = 'Albert';

طرح اجرای تخمینی

این کد مشتریانی که نام آن‌ها Adam است را می‌یابد. این کد نیز از قدرت ایندکس ix_customer_name بهره می‌برد. اما باید کل ایندکس را برای جستجو اسکن کند، که روش خیلی کندتری نسبت به روش جستجوی ایندکس است.

SELECT 
    customer_id, 
    first_name, 
    last_name
FROM 
    sales.customers
WHERE 
    first_name = 'Adam';

طرح اجرای تخمینی

در نتیجه، بهترین کار این است که ستون‌هایی که گاهی برای به دست آوردن داده‌ها استفاده می‌کنید را در ابتدای لیست ستون‌های ایندکس قرار دهید.

به این صورت، راجع به ایندکس‌های غیر خوشه‌ای و چگونگی استفاده از دستور CREATE INDEX برای ایجاد ایندکس‌های غیر خوشه‌ای برای جداول به منظور بهبود سرعت بازگرداندن داده‌ها را آموختید.

تغییر نام ایندکس در SQL Server

Sp_rename یک stored procedure سیستمی است که به شما امکان می‌دهد هر شیء ایجاد شده توسط کاربر در پایگاه داده فعلی (از جمله جدول، ایندکس و ستون) را تغییر نام دهید.

دستور تغییر نام یک ایندکس به این صورت است:

EXEC sp_rename 
    index_name, 
    new_index_name, 
    N'INDEX';  

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

EXEC sp_rename 
    @objname = N'index_name', 
    @newname = N'new_index_name',   
    @objtype = N'INDEX';

برای مثال، دستور زیر ایندکس ix_customers_city از جدول sales.customers را به ix_cust_city تغییر می‌دهد:

EXEC sp_rename 
        @objname = N'sales.customers.ix_customers_city',
        @newname = N'ix_cust_city' ,
        @objtype = N'INDEX';

یا به صورت خلاصه:

EXEC sp_rename 
        N'sales.customers.ix_customers_city',
        N'ix_cust_city' ,
        N'INDEX';

تغییر نام یک ایندکس با استفاده از (SQL Server Management Studio (SSMS

برای تغییر نام یک ایندکس به یک نام جدید با استفاده از SSMS، مراحل زیر را انجام دهید:

ابتدا، به آدرس پایگاه داده >>  نام جدول >> indexها:

سپس، روی ایندکسی که می‌خواهید نامش را تغییر دهید کلیک راست کنید و از آیتم‌های منو گزینه Rename را انتخاب کنید.

در تصویر زیر، ما نام ایندکس ix_customers_name از جدول sales.customers را تغییر داده‌ایم:

نام ایندکس ix_customers_name از جدول sales.customers را تغییر داده‌ایم

حالا، نام جدید را تایپ کرده و کلید enter را بزنید. تصویر زیر نشان می‌دهد که نام ایندکس ix_customers_name به ix_cust_fullname تغییر کرده است:

نام ایندکس ix_customers_name به ix_cust_fullname تغییر کرده است

در نتیجه، چگونگی تغییر نام یک ایندکس با استفاده از stored procedure به نام sp_rename و SQL Server Management Studio را آموختید.

غیرفعال کردن ایندکس‌ها در SQL Server

برای غیر فعال کردن یک ایندکس، می‌توانید از دستور ALTER INDEX به شکل زیر استفاده کنید:

ALTER INDEX index_name
ON table_name
DISABLE;

برای غیر فعال کردن تمام ایندکس‌های یک جدول، می‌توانید به شکل زیر از دستور ALTER INDEX استفاده کنید:

ALTER INDEX ALL ON table_name
DISABLE;

اگر یک ایندکس را غیر فعال کنید، بهینه‌ساز query آن ایندکس غیر فعال شده را برای ایجاد طرح‌های اجرای query در نظر نخواهد گرفت.

وقتی یک ایندکس را در یک جدول غیر فعال می‌کنید، SQL Server تعریف ایندکس را در متاداده و آمارهای ایندکس موجود در ایندکس‌ها غیر خوشه‌ای نگه می‌دارد.

به هر حال، اگر یک ایندکس غیر خوشه‌ای یا خوشه‌ای موجود در یک view را غیر فعال کنید SQL Server به صورت فیزیکی تمام داده‌های ایندکس را حذف می‌کند.

اگر یک ایندکس خوشه‌ای از یک جدول را غیر فعال کنید، نمی‌توانید با استفاده از زبان تغییر داده (یعنی همان دستورهای UPDATE، INSERT، SELECT و DELETE) به داده‌های جدول دسترسی داشته باشید و این روند تا زمانی که ایندکس را rebuild کنید یا آن را Drop کنید ادامه خواهد داشت.

مثال‌های غیرفعال کردن ایندکس در SQL Server

اجازه دهید چند مثال از غیر فعال کردن ایندکس‌ها حل کنید تا بهتر این موضوع را درک کنید.

الف) مثال غیر فعال کردن یک ایندکس

این مثال از دستور ALTER INDEX برای غیر فعال کردن ایندکس ix_cust_city در جدول sales.customers استفاده می‌کند:

ALTER INDEX ix_cust_city 
ON sales.customers 
DISABLE;

در نتیجه، query زیر که مشتریان ساکن در San Jose را می‌یابد، نمی‌تواند از مزیت ایندکس غیر فعال شده بهره ببرد:

SELECT    
    first_name, 
    last_name, 
    city
FROM    
    sales.customers
WHERE 
    city = 'San Jose';

در تصویر زیر، طرح اجرای query را مشاهده می‌کنید:

 طرح اجرای query

ب) مثال غیر فعال کردن تمام ایندکس‌های یک جدول

این دستور تمام ایندکس‌های جدول sales.customers را غیر فعال می‌کند:

ALTER INDEX ALL ON sales.customers
DISABLE;

بنابراین، دیگر نمی‌توانید به داده‌های جدول دسترسی داشته باشید.

SELECT * FROM sales.customers;

پیغام اروری که داده می‌شود این است:

The query processor is unable to produce a plan because the index 'PK__customer__CD65CB855363011F' on table or view 'customers' is disabled.

توجه کنید که در قسمت بعدی، فعال کردن ایندکس آموزش داده خواهد شد.

به این شکل، چگونگی استفاده از دستور ALTER INDEX برای غیر فعال کردن ایندکس‌های یک جدول را آموختید.

فعال کردن ایندکس‌ها در SQL Server

این کد از دستور ALTER INDEX برای فعال کردن یک ایندکس در یک جدول استفاده می‌کند:

ALTER INDEX index_name 
ON table_name  
REBUILD;

این کد از دستور CREATE INDEX برای فعال کردن ایندکس غیر فعال شده و ایجاد مجدد آن استفاده می‌کند:

CREATE INDEX index_name 
ON table_name(column_list)
WITH(DROP_EXISTING=ON)

کد زیر از دستور ALTER INDEX برای فعال کردن تمام ایندکس‌های غیر فعال شده در یک جدول استفاده می‌کند:

ALTER INDEX ALL ON table_name
REBUILD;

فعال کردن ایندکس‌ها با استفاده از دستور DBCC DBREINDEX

این کد از دستور DBCC DBREINDEX برای فعال کردن یک ایندکس در یک جدول استفاده می‌کند:

DBCC DBREINDEX (table_name, index_name);

این کد از دستور DBCC DBREINDEX برای فعال کردن تمام ایندکس‌ها در یک جدول استفاده می‌کند:

DBCC DBREINDEX (table_name, " ");  

مثال فعال کردن ایندکس‌ها

مثال زیر از دستور ALTER INDEX برای فعال کردن تمام ایندکس‌ها در جدول sales.customers از پایگاه‌ داده نمونه bikestores استفاده می‌کند:

ALTER INDEX ALL ON sales.customers
REBUILD;

در نتیجه، دستورهای مختلف از جمله CREATE INDEX، ALTER INDEX و DBCC DBREINDEX برای فعال کردن یک یا تمام ایندکس‌ها در یک جدول را آموختید.

ایندکس یکتا یا Uniaue در SQL Server

ایندکس یکتا یا Unique اطمینان می‌دهد که ستون‌های کلیدی ایندکس شامل هیچ مقدار تکراری نیستند.

یک ایندکس یکتا می‌تواند شامل یک یا چند ستون باشد. اگر یک ایندکس یکتا دارای یک ستون باشد، مقادیر این ستون یکتا خواهند بود. در صورتی که ایندکس یکتا دارای چند ستون باشد، ترکیب مقادیر در این ستون‌ها یکتا خواهد بود.

هر گونه تلاش برای insert یا update کردن داده‌ها در ستون‌های کلیدی ایندکس یکتا که منجر به ایجاد داده‌های تکراری شود، با ارور مواجه خواهد شد.

یک ایندکس یکتا می‌تواند خوشه‌ای یا غیر خوشه‌ای باشد.

برای ایجاد یک ایندکس یکتا، می‌توانید از دستور CREATE UNIQUE INDEX به شکل زیر استفاده کنید:

CREATE UNIQUE INDEX index_name
ON table_name(column_list);

در این syntax:

  • ابتدا، نام ایندکس یکتا پس از کلیدواژه‌های CREATE UNIQUE INDEX مشخص شده است.

  • سپس، نام جدولی که ایندکس به آن مرتبط است و لیستی از ستون‌هایی که در ایندکس وجود دارند آمده است.

مثال‌های ایندکس یکتا در SQL Server

اجازه دهید چند مثال استفاده از ایندکس‌های یکتا حل کنیم.

الف) مثال ایجاد یک ایندکس یکتا برای یک ستون

این query مشتری دارای ایمیل ‘caren.stephens@msn.com’ را می‌یابد:

SELECT
    customer_id, 
    email 
FROM
    sales.customers
WHERE 
    email = 'caren.stephens@msn.com';

طرح اجرای تخمینی

بهینه‌ساز query باید کل ایندکس خوشه‌ای را برای یافتن ردیف اسکن کند.

برای افزایش سرعت بازگرداندن query، می‌توانید یک ایندکس غیر خوشه‌ای به ستون Email اضافه کنید.

به هر حال، با فرض اینکه هر مشتری دارای یک ایمیل یکتا است، می‌توانید یک ایندکس یکتا برای ستون email ایجاد کنید.

چون جدول sales.customers از قبل دارای داده است، باید ابتدا بررسی کنید ببینید آیا مقادیر تکراری در ستون email وجود دارند یا خیر:

SELECT 
    email, 
    COUNT(email)
FROM 
    sales.customers
GROUP BY 
    email
HAVING 
    COUNT(email) > 1;

این query یک مجموعه نتیجه خالی بازمی‌گرداند. یعنی اینکه هیچ مقدار تکراری در ستون email وجود ندارد.

در نتیجه، می‌توانید به ایجاد یک ایندکس یکتا برای ستون email از جدول Sales.customers بپردازید:

CREATE UNIQUE INDEX ix_cust_email 
ON sales.customers(email);

از حالا به بعد، بهینه‌ساز Query از مزیت ایندکس ix_cust_email بهره می‌برد و از متد یا روش index seek (جستجوی ایندکس) برای جستجوی ردیف بر اساس ایمیل استفاده می‌کند.

ُرح اجرای تخمینی

ب) مثال ایجاد یک ایندکس یکتا برای چند ستون

ابتدا یک جدول به نام t1 ایجاد کنید که دارای دو ستون باشد:

CREATE TABLE t1 (
    a INT, 
    b INT
);

سپس، یک ایندکس یکتا ایجاد کنید که شامل هر دو ستون‌های a و b باشد:

CREATE UNIQUE INDEX ix_uniq_ab 
ON t1(a, b);

حالا، یک ردیف جدید به جدول t1 اضافه کنید:

INSERT INTO t1(a,b) VALUES(1,1);

سپس، یک ردیف دیگر به جدول t1 اضافه کنید. توجه کنید که مقدار 1 در ستون a تکرار شده است، اما ترکیب مقادیر در ستون‌های a و b تکراری نیست:

INSERT INTO t1(a,b) VALUES(1,2);

در آخر، یک ردیف که از قبل در جدول t1 وجود دارد را دوباره در جدول insert کنید:

INSERT INTO t1(a,b) VALUES(1,2);

SQL Server یک ارور می‌دهد:

Cannot insert duplicate key row in object 'dbo.t1' with unique index 'ix_ab'. The duplicate key value is (1, 2).

ایندکس یکتا و NULL در SQL Server

NULL خیلی خاص است. یک نشان‌گر یا علامت است که نشان دهنده اطلاعات مفقودی یا ناموجود است.

NULL حتی برابر با خودش (یعنی هیچ) نیست. به هر حال، وقتی پای ایندکس یکتا به میان می‌آید، SQL Server به صورت یکسان با مقادیر NULL برخورد می‌کند.

یعنی اینکه اگر یک ایندکس یکتا در یک ستون با قابلیت NULL شدن ایجاد کنید، فقط می‌توانید یک مقدار NULL در این ستون داشته باشید.

کد زیر یک جدول جدید به نام t2 ایجاد می‌کند و یک ایندکس یکتا در ستون a تعریف می‌کند:

CREATE TABLE t2(
    a INT
);
 
CREATE UNIQUE INDEX a_uniq_t2
ON t2(a);

این query، مقدار NULL را به ستون a از جدول t2 اضافه می‌کند:

INSERT INTO t2(a) VALUES(NULL);

به هر حال، با اجرای مجدد query بالا، SQL Server به خاطر وجود مقادیر NULL تکراری ارور می‌دهد:

INSERT INTO t2(a) VALUES(NULL);

ایندکس یکتا در برابر ویژگی UNIQUE

هم ایندکس یکتا و هم ویژگی UNIQUE یکتایی مقادیر در یک یا چند ستون را اطمینان می‌دهند. SQL Server مقادیر تکراری را برای هم ایندکس یکتا و هم ویژگی UNIQUE، به یک روش ارزیابی می‌کند.

وقتی یک ویژگی UNIQUE ایجاد می‌کنید، در پشت صحنه، SQL Server یک ایندکس یکتا مرتبط با این ویژگی ایجاد می‌کند.

به هر حال، ایجاد یک ویژگی UNIQUE در ستون‌ها، باعث می‌شد هدف ایندکس یکتا واضح و مشخص شود.

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

خلاصه

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

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

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات