خانه SQL Server ایندکس گذاری بر روی ستون های VARCHAR و NVARCHAR SQL Server نوشته شده توسط: سید محمد حسینی ۱۶ خرداد ۱۳۹۵ زمان مطالعه: 5 دقیقه ۰ (۰) مقدمه همانطور که می دانیم، ستون های کلید مربوط به یک ایندکس در (SQL Server(Clustered Index or Non-Clustered Index دارای محدودیت اندازه ی حداکثر ۹۰۰ بایتی هستند. حال فرض کنید که قصد داریم SQL Server را محک زده و یک ایندکس بر روی ستونی از نوع (VARCHAR(8000 ایجاد کنیم و سپس رکوردهایی را با طول بیش از ۹۰۰ بایت برای کلید ایندکس درج کنیم. فکر می کنید SQL Server چه عکس العملی نشان می دهد. خوب بهتر است که ایzzن مسئله را امتحان کنیم. در کد زیر یک جدول ساده با یک ستون از نوع (VARCHAR(8000 ایجاد کرده و پس از آن یک ایندکس از نوع Non-Clustered بر روی این ستون ایجاد می کنیم. -- Create a simple table CREATE TABLE Foo ( Bar VARCHAR(8000) ) GO -- Create a simple Non-Clustered Index CREATE NONCLUSTERED INDEX idx_Bar ON Foo(Bar) GO با اجرای این کد SQL Server پیغام هشدار زیر را نمایش می دهد. .Warning! The maximum key length is 900 bytes. The index ‘idx_Bar’ has maximum length of 8000 bytes .For some combination of large values, the insert/update operation will fail به این معنا که برای مقادیر بزرگ ممکن است دستورات INSERT یا UPDATE با خطا مواجه شوند. و اما چیزی که اکنون اتفاق می افتد: قصد دارم یک رکورد با طول ۹۰۱ بایت را در این ستون درج کنم. -- Insert a too large index key column... INSERT INTO Foo VALUES(REPLICATE('2', 901)) GO بله، مثل این که SQL Server هم حواسش به همه چیز هست. اجرای دستور INSERT من با خطای زیر مواجه شد. SQL Server ابتدا اطمینان پیدا می کند که طول کلید در محدوده ۹۰۰ بایت هست یا نه. در ادمه، می بینیم که SQL Server اجازه می دهد رکوردی با طول کلید دقیقا ۹۰۰ بایت درج شود. -- Insert a too large index key column... INSERT INTO Foo VALUES(REPLICATE('2', 900)) GO کد فوق بدون هیچ خطایی اجرا می شود. حال در صورتی که این ستون را با مقداری بیش از ۹۰۰ بایت بروزرسانی کنیم چه اتفاقی رخ می دهد. -- This UPDATE will fail UPDATE Foo SET BAR = REPLICATE('x', 901) GO اجرای دستور UPDATE نیز با خطا مواجه شد! که البته علت را می دانید. SQL Server ابتدا طول کلید را مورد بررسی قرار می دهد تا اطمینان بابد که در محدوده ۹۰۰ بایت است یا خیر. و اما نتیجه گیری که می توان کرد این است که، مهم نیست شما چقدر تلاش می کنید، SQL Server را نمی توانید دور بزنید، چون SQL Server حواسش به همه چیز هست. نکته قابل توجه در اینجا این است که، با توجه به بررسی انجام شده، محدودیت ۹۰۰ بایت برای کلید ایندکس تا SQL Server 2014 پا بر جا بوده است ولی در SQL Server 2016 این محدودیت به ۱۷۰۰ بایت افزایش پیدا کرده است. در صورتی که جدول و ایندکس فوق را در SQL Server 2016 ایجاد کنیم(من در نسخه RC3 تست کردم) با پیغام هشدار زیر مواجه خواهیم شد. .Warning! The maximum key length for a nonclustered index is 1700 bytes. The index ‘idx_Bar’ has maximum length of 8000 bytes .For some combination of large values, the insert/update operation will fail در مورد INSERT و UPDATE نیز محدودیت ۱۷۰۰ بایت در نظر گرفته می شود و مشابه قبل خواهد بود. در مورد ستون های نوع NVARCHAR نیز قضیه کاملا مشابه ستون های VARCHAR است، با این تفاوت که تمامی اندازه ها و محدودیت ها نصف می شود، زیرا در NVARCHAR به ازای هر کاراکتر ۲ بایت اشغال می شود. بنابراین در صورتی که بخواهیم رکوردی را درج کنیم، کلید آن حداکثر می تواند دارای ۴۵۰ کاراکتر باشد(چون برای ذخیره شدن ۹۰۰ بایت اشغال می کند)، و در SQL Server 2016 کلید می تواند حداکثر دارای ۸۵۰ کاراکتر باشد(چون برای ذخیره شدن ۱۷۰۰ بایت اشغال می کند). در لینک زیر می توانید لیستی از حداکثر قابلیت های SQL Server که تا تاریخ ۲۵/۰۲/۲۰۱۶ منتشر شده است را مشاهده کنید. https://msdn.microsoft.com/en-us/library/ms143432.aspx بپ چه رتبه ای می‌دهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش برچسب ها # Clustered Index# Data Type# Index# NonClustered Index# SQL Server# SQL Server 2016 معرفی نویسنده مقالات 11 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده سید محمد حسینی پروفایل نویسنده معرفی محصول مهدی شیشه بری ، مسعود طاهری دوره کوئری‌نویسی پیشرفته [مباحث Window Function و ColumnStore Index] 900.000 تومان مقالات مرتبط ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی ۰۷ اردیبهشت SQL Server استفاده از Operator ها در SQL Server Agent حسن سلیمانی ۰۵ اردیبهشت SQL Server بررسی نحوه ایجاد Job در SQL Server حسن سلیمانی ۲۹ فروردین SQL Server آشنایی با بخش های مختلف SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ moh.daneshvar@gmail.com ۲۵ / ۰۷ / ۹۶ - ۰۶:۱۰ با تشکر از مطلب مفیدتون ، من یه جدولی دارم که فیلد تاریخ آن عدد بوده و فیلد ساعت ان از نوع رشته(char) می باشد. و بر روی فیلد date, time ایندکس تعریف شده هست. وقتی دستور select id from message order by date , time رو اجرا می کنم، با توجه به plan اس کیو ال از ایندکس موجود استفاده نمی کند ولی وقتی نوع داده فیلد ساعت رو به time تغییر می دهم ، از ایندکس تعریف شده استفاده می شود. آیا دلیل خاصی وجود دارد؟ ممنون می شم راهنمایی کنید. پاسخ به دیدگاه moh.daneshvar@gmail.com ۲۷ / ۰۷ / ۹۶ - ۰۵:۴۹ بالاخره بعد از یک روز درگیری تونستم مشکل فوق رو حل کنم، collation دیتابیس روی latin بود و با تغییر collation فیلد رشته ای مورد نظر به arabic مشکل حل شد. ولی این که علتش چی بود . دقیقا نتونستم متوجه بشم!!!! با تشکر پاسخ به دیدگاه moh.daneshvar@gmail.com ۲۵ / ۰۷ / ۹۶ - ۰۷:۵۵ توضیح این که در صورتی که Force کنیم اس کیو ال از ایندکس موجود استفاده کند. باز هم خودش عملیات Sort رو انجام می دهد. پاسخ به دیدگاه moh.daneshvar@gmail.com ۲۵ / ۰۷ / ۹۶ - ۱۲:۵۱ در ضمن این مشکل فقط تو یه دیتا بیس وجود دارد. و در دیتابیس جدید از ایندکس استفاده می کند. و plan کوئری cost عملیات sort هم ندارد پاسخ به دیدگاه moh.daneshvar@gmail.com ۲۵ / ۰۷ / ۹۶ - ۰۶:۱۰ با تشکر از مطلب مفیدتون ، من یه جدولی دارم که فیلد تاریخ آن عدد بوده و فیلد ساعت ان از نوع رشته(char) می باشد. و بر روی فیلد date, time ایندکس تعریف شده هست. وقتی دستور select id from message order by date , time رو اجرا می کنم، با توجه به plan اس کیو ال از ایندکس موجود استفاده نمی کند ولی وقتی نوع داده فیلد ساعت رو به time تغییر می دهم ، از ایندکس تعریف شده استفاده می شود. آیا دلیل خاصی وجود دارد؟ ممنون می شم راهنمایی کنید. پاسخ به دیدگاه moh.daneshvar@gmail.com ۲۷ / ۰۷ / ۹۶ - ۰۵:۴۹ بالاخره بعد از یک روز درگیری تونستم مشکل فوق رو حل کنم، collation دیتابیس روی latin بود و با تغییر collation فیلد رشته ای مورد نظر به arabic مشکل حل شد. ولی این که علتش چی بود . دقیقا نتونستم متوجه بشم!!!! با تشکر پاسخ به دیدگاه moh.daneshvar@gmail.com ۲۵ / ۰۷ / ۹۶ - ۰۷:۵۵ توضیح این که در صورتی که Force کنیم اس کیو ال از ایندکس موجود استفاده کند. باز هم خودش عملیات Sort رو انجام می دهد. پاسخ به دیدگاه moh.daneshvar@gmail.com ۲۵ / ۰۷ / ۹۶ - ۱۲:۵۱ در ضمن این مشکل فقط تو یه دیتا بیس وجود دارد. و در دیتابیس جدید از ایندکس استفاده می کند. و plan کوئری cost عملیات sort هم ندارد پاسخ به دیدگاه احسان زینلی ۲۳ / ۰۴ / ۹۵ - ۰۶:۳۲ مقاله مختصر و مفیدی بودممنون پاسخ به دیدگاه احسان زینلی ۲۳ / ۰۴ / ۹۵ - ۰۶:۳۲ مقاله مختصر و مفیدی بودممنون پاسخ به دیدگاه seyedmahdi ۱۴ / ۰۴ / ۹۵ - ۱۲:۲۷ با تشکر از مقاله خوب شما پاسخ به دیدگاه seyedmahdi ۱۴ / ۰۴ / ۹۵ - ۱۲:۲۷ با تشکر از مقاله خوب شما پاسخ به دیدگاه ha_zarabi_vb6@outlook.com ۳۱ / ۰۳ / ۹۵ - ۰۳:۴۵ با سلام و خسته نباشید ممنونم بابت این آموزشی که دادید جناب آقای مهندس سید محمد حسینی به نکات خیلی خوبی اشاره کردید. ممنونم از شما پاسخ به دیدگاه سیدمحمد حسینی ۰۲ / ۰۴ / ۹۵ - ۰۴:۳۲ سلام ممنون از توجه شما امیدوارم که مفید بوده باشه پاسخ به دیدگاه ha_zarabi_vb6@outlook.com ۳۱ / ۰۳ / ۹۵ - ۰۳:۴۵ با سلام و خسته نباشید ممنونم بابت این آموزشی که دادید جناب آقای مهندس سید محمد حسینی به نکات خیلی خوبی اشاره کردید. ممنونم از شما پاسخ به دیدگاه سیدمحمد حسینی ۰۲ / ۰۴ / ۹۵ - ۰۴:۳۲ سلام ممنون از توجه شما امیدوارم که مفید بوده باشه پاسخ به دیدگاه vahdani.d@gmail.com ۱۷ / ۰۳ / ۹۵ - ۰۲:۴۸ بسیار ممنون استفاده کردیم عالی بود و البته این قسمت منو یاد پلیس فتا میندازه که حواسش به همه چیز هست 🙂 و اما نتیجه گیری که می توان کرد این است که، مهم نیست شما چقدر تلاش می کنید، SQL Server را نمی توانید دور بزنید، چون SQL Server حواسش به همه چیز هست. پاسخ به دیدگاه vahdani.d@gmail.com ۱۷ / ۰۳ / ۹۵ - ۰۲:۴۸ بسیار ممنون استفاده کردیم عالی بود و البته این قسمت منو یاد پلیس فتا میندازه که حواسش به همه چیز هست 🙂 و اما نتیجه گیری که می توان کرد این است که، مهم نیست شما چقدر تلاش می کنید، SQL Server را نمی توانید دور بزنید، چون SQL Server حواسش به همه چیز هست. پاسخ به دیدگاه 1 2