خانه SQL Server بررسی تاثیر Unique بودن Clustered Index در عملکرد SQL Server SQL Server نوشته شده توسط: محمد مزیدی ۲۱ مرداد ۱۳۹۶ زمان مطالعه: 8 دقیقه ۱ (۱) مقدمه به طور خیلی ساده 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 قابل اجرا می باشد چه رتبه ای میدهید؟ میانگین ۱ / ۵. از مجموع ۱ اولین نفر باش برچسب ها # Clustered Index# Clustered Index چیست# indexهای یکتا# Non Clustered Index# NonClustered چیست# SQL Server 2016# آموزش SQL# آموزش SQL Server 2016# افزایش سرعت SQL Server# افزایش کارایی SQL Server# کلاستر ایندکس چیست معرفی نویسنده مقالات 1 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده محمد مزیدی پروفایل نویسنده معرفی محصول مسعود طاهری دوره آموزشی Performance Tuning در SQL Server 5.700.000 تومان مقالات مرتبط ۰۴ مهر SQL Server دستور Case در SQL Server – قسمت اول محمد سلیم آبادی ۳۰ شهریور SQL Server استور پروسیجر (Stored Procedure) چیست؟ تیم فنی نیک آموز ۰۶ تیر SQL Server معرفی Ledger در SQL Server 2022 مسعود طاهری ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ هورا ۰۵ / ۰۲ / ۹۹ - ۰۵:۵۰ خیلی گنگ توضیح دادید پاسخ به دیدگاه امیر ۱۶ / ۰۶ / ۰۰ - ۰۵:۰۱ منم واقعا چیزی متوجه نشدم برای فهماندن مطلب باید اولا عامیانه بنویسید و از مثال فیزیکی مثل فهرست کتاب که در مطلب اورده بودید استفاده کنید ضمنا مطلب رو به صورت لایه لایه توضیح بدید یعنی از کل به جزئ برید اول کلیات رو بگی بگید بعد یواش یواش وارد جزییات بشید پاسخ به دیدگاه هورا ۰۵ / ۰۲ / ۹۹ - ۰۵:۵۰ خیلی گنگ توضیح دادید پاسخ به دیدگاه امیر ۱۶ / ۰۶ / ۰۰ - ۰۵:۰۱ منم واقعا چیزی متوجه نشدم برای فهماندن مطلب باید اولا عامیانه بنویسید و از مثال فیزیکی مثل فهرست کتاب که در مطلب اورده بودید استفاده کنید ضمنا مطلب رو به صورت لایه لایه توضیح بدید یعنی از کل به جزئ برید اول کلیات رو بگی بگید بعد یواش یواش وارد جزییات بشید پاسخ به دیدگاه Mehdi ۲۸ / ۰۵ / ۹۶ - ۱۰:۵۴ میدانیم که NonClustered ایندکس ها شامل Clusterd key هستند، مثلا در بحث Key LookUp از این امکان استفاده می شود. حالا در DataWare house که شامل یک CCI و یک یا چند NonClustered ایندکس است، بحث Clusterd key برای NonClustered ایندکس ها به چه صورت است؟؟ ممنون پاسخ به دیدگاه مسعود طاهری ۲۸ / ۰۵ / ۹۶ - ۰۴:۰۸ سلام لطفا به این لینک مراجعه کنید http://www.nikoport.com/2015/09/06/columnstore-indexes-part-65-clustered-columnstore-improvements-in-sql-server-2016/ پاسخ به دیدگاه Mehdi ۲۸ / ۰۵ / ۹۶ - ۱۰:۵۴ میدانیم که NonClustered ایندکس ها شامل Clusterd key هستند، مثلا در بحث Key LookUp از این امکان استفاده می شود. حالا در DataWare house که شامل یک CCI و یک یا چند NonClustered ایندکس است، بحث Clusterd key برای NonClustered ایندکس ها به چه صورت است؟؟ ممنون پاسخ به دیدگاه مسعود طاهری ۲۸ / ۰۵ / ۹۶ - ۰۴:۰۸ سلام لطفا به این لینک مراجعه کنید http://www.nikoport.com/2015/09/06/columnstore-indexes-part-65-clustered-columnstore-improvements-in-sql-server-2016/ پاسخ به دیدگاه شعبانی ۲۲ / ۰۵ / ۹۶ - ۰۵:۲۴ لطفا دوره adminstrator Sql رو هم تولید کنید پاسخ به دیدگاه شعبانی ۲۲ / ۰۵ / ۹۶ - ۰۵:۲۴ لطفا دوره adminstrator Sql رو هم تولید کنید پاسخ به دیدگاه