در این مقاله مطلابی را در مورد Full Text Index ارائه خواهیم داد . جستجوی Full Text در SQL Server ، این اجازه را به کاربران و برنامه ها می‌دهد که Queryهای Full Text را برای داده های character-base در جداول SQL Server ، اجرا کنند . ایندکس‌ Full Text به ما برای انجام Queryهای پیچیده بر روی داده های Character-base ، کمک می‌کند .

این Queryها میتوانند شامل کلمه یا عبارتی برای جستجو باشند . قبل از این که ما قادر به اجرای Query های Full Text روی یک جدول باشیم ، ابتدا ما نیاز به ایجاد یک اندیس Full Text در جدول داریم . برای هر جدول فقط توانایی ایجاد یک ایندکس‌ Full Text را داریم ، که این اندیس میتواند بالای 1024 ستون داشته باشد . اندیس Full Text ، میتواند در جدول دارای یک یا چند ستون Chatacter-base باشد . این ستونها میتوانند شامل هر typeای از داده ها باشند : char, varchar, char, nvarchar, ntext, image, xml .

 در اندیس Full Text بوسیله انجام عملیات بر روی کلمات و عبارات بر مبنای قانون های یک زبان خاص همانند انگلیسی یا فارسی ، Queryهای Full Text جستجوهایی را روی text data انجام میدهند . Queryهای Full Text میتوانند شامل کلمات و عبارات ساده یا کلمات و عبارات چند وجهی باشند . یک کوئری Full-Text همه ی اسنادی که حداقل دارای یک کلمه match باشد را باز میگرداند ( که به hit معروف و شناخته می‌شود) . Match زمانی رخ میدهد که یک سوژه از اسناد دارای تمامی termهای مشخص شده در کوئری Full-Text باشد . .

با وجود کلمه کلیدی "like" در SQL ، به چه دلیل ما از  (Full Text Index (FTI ها استفاده میکنیم :

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

کوئری بالا برای سناریوی بالا کارآمد است ، اگر ما دنبال داده ای میگشتیم که دقیقا همخوانی نداشته باشد ، چه کاری انجام میدهیم . FTS الگوریتم بهتری برای تطابق نام نسبت FTI دارد . از این رو  ، در زمان جستجوی smith کارایی بهتری دارد و Smith, Smythe, Smithers و ... را نمایش میدهد . در این جور موارد FTS نتایج بهتری را نسبت به روش قدیمی استفاده از like باز میگرداند . 

چه زمانی از FTI به جای عبارت like استفاده میکنیم :
• یک کلمه یا عبارت نزدیک به کلمه یا عبارت جستجو شده را می‌خواهیم . 
• زمانی که اندازه نتایج به صدها و هزاران میرسد .
• میلیون ها سطر که هر کدام شامل یک دسته همانند "wordAwordBwordC..." هستند .
•هر کلمه مشتق شده از یک کلمه را بخواهیم برای مثال (run و ran و running) .

 ایجاد Full Text Index :
حال ، به سراغ چگونگی ایجاد Full Text Indx می‌رویم .با دو روش برای ایجاد Full Text Index آشنا خواهید شد . ایجاد بصورت دستی (manually) و استفاده از دستورات SQL .

ایجاد دستی  Full Text Index :

برای ایجاد manually مراحل زیر را باید دنبال کنیم :
1. ایجاد یک Full Text Catalog
2. ایجاد Full Text Index
3.  پر کردن Index 

مرحله 1 : ایجاد Full Text Catalog 

Full Text Catalog برای Full Text Index مورد استفاده قرار میگیرد . اگر ما Full Text Catalog را مشخص نکنیم ، آنگاه SQL Server از Catalog پیش فرض استفاده میکند . حال ما به سراغ چگونگی ایجاد full text catalog میرویم .

برای ایجاد Full Text Catalog ، ابتدا پایگاه داده خود را انتخاب کنید ، سپس به فولدر storage بروید و روی
Full Text Catalog  راست کلیک کرده و گزینه New Full-Text Catalog را انتخاب کنید :


 

حال ، یک نام برای full text catalog خود ، انتخاب کنید :

همانطور که مشاهده میکنید یک new catalog در فولدر storage ایحاد شده است .

مرحله دوم : ایجاد Full Text Index 

برای ایجاد Full Text Index روی جدول مورد نظر خود راست کلیک کرده و Define Full-Text Index را انتخاب کنید ، تصویر زیر را مشاهده کنید :

  
حال Unique Index را انتخاب کنید . این اجباری است که برای جدول “Full Text Index” حداقل باید یک 
 Unique Index داشته باشید . 

نام ستون را انتخاب کنید و نوع زبان مورد نظر را مشخص کنید . شما میتوانید فقط character-base و
image-base را انتخاب کنید .  

change tracking را انتخاب کنید :

حال full-text catalog را برای Index انتخاب کنید :

تصویر آخر بیان دارد که full text index با موفقیت ایجاد شده است . حال این Index را پر میکنیم .

مرحله سوم : پر کردن Index 

برای پر کردن Index ، روی جدول راست کلیک کرده و گزینه “Start Full Population” را انتخاب کنید . 

ایجاد Full Text Index با استفاده از دستورات SQL :
از دستورات زیر برای ایجاد Full Text Index اسفاده میکنیم .

 Syntax :

CREATE FULLTEXT INDEX ON table_name  
[ ( { column_name  
[ TYPE COLUMN type_column_name ]  
[ LANGUAGE language_term ]   
[ STATISTICAL_SEMANTICS ]  
} [ ,...n]   
) ]  
KEY INDEX index_name  
[ ON<catalog_filegroup_option> ]  
[ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]  
[;]  
  
<catalog_filegroup_option>::=  
{  
fulltext_catalog_name  
| ( fulltext_catalog_name, FILEGROUP filegroup_name )  
| ( FILEGROUP filegroup_name, fulltext_catalog_name )  
| ( FILEGROUP filegroup_name )  
}  
  
<with_option>::=  
{  
CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }   
| STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }  
| SEARCH PROPERTY LIST [ = ] property_list_name  
}  

مثال :

CREATE FULLTEXTCATALOG New_CatalogASDEFAULT;  
CREATE FULLTEXTINDEX ON dbo.Employee(EmployeeName TYPECOLUMN varchar LANGUAGE 1033,EmpSalary TYPECOLUMN varchar LANGUAGE 1033)  
KEY INDEX UN_Pankaj  
ON  
New_Catalog  

بعد از ایجاد full text query و full text index ، حال به سراغ آموزش چگونگی استفاده از اینها برای بالا بردن کارایی در هنگام جستجو ، میرویم . در T-SQL چهار عمل اصلی وجود دارد که اجازه فعل و انفعالات با اندیس های full text
شما را میدهد :
 
متد CONTAINS و FREETEXT :

اعمال CONTAINS و FREETEXT  یک مقدار bool را بازمیگردانند و این بدان معناست که ما میتوانیم بصورت مستقیم از اینها در WHERE استفاده میکنیم :

FREETEXT :
FREETEXT کلماتی که از نظر معنایی نیز با کلمه جستجو شده ، تطابق دارد را پیش بینی میکند . زمانی که FREETEXT مورد استفاده قرار میگیرد ، engine کوئری full-text این اعمال را بصورت داخلی بر روی freetext_string میدهد ، و به هر term وزنی را انتساب میدهد ، سپس matchها را پیدا می‌کند :

• جداسازی رشته ها به کلمات فردی 
• تولید فرم صرفی (inflectional) کلمات 
• شناسایی لیستی از توسعه گراها یا تعویض کنندها برای termها بر مبنای matchها  

مثال :

SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski  
WHERE  
FREE TEXT(Keyword_Text,'Hotel Above')

خروجی :


CONTAINS :

CONTAINS برای کلمات و عباراتی که دقیقا match هستند ، جستجو میکند . این شبیه freetext است فقط با این تفاوت که این یک کلمه کلیدی را با رکورد ها تطابق میدهد ، و اگر ما قصد ترکیب دیگر کلمات را در جستجوی خود  داشته باشیم ما باید در search از "and" و "or" استفاده کنیم .

CONTAINS  میتواند برای جستجوهای زیر مورد استفاده قرار بگیرد :

• یک کلمه یا عبارت 
• پیشوندی از یک کلمه یا یک عبارت 
• لغتی که نزدیک به کلمه دیگری است 
• ریشه کلمات و مشتقات کلمات دیگر
• یک کلمه که از نظر معنایی با کلمه مورد نظر مترادف باشد (برای مثال ، آهن و آلمینیوم هر دو فلز هستند)

مثال :

SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski  
WHERE  
CONTAINS(Keyword_Text,'Hotel OR Above')  

نتیجه گیری :

از جستجوی Full Text برای زمانی که سطح داده ها بزرگ است و شما قصد انجام جستجوی یک کلمه یا عبارت مشخص را دارید ، میتوانید استفاده کنید . Full Text Index نیز میتواند با استفاده از peratorهای 
FREETEXT (), CONTAINS () , “and” یا “or”  برای جستجوی کلمات و عبارات مورد استفاده قرار گیرد .

آموزش نصب sql server 2017