PageViews Last Month

Thursday, 7 April 2016

Implementing Full-Text Indexes

In my previous blog we saw the Full-Text Index architecture. 

http://sqlandmorewithkruti.blogspot.in/2016/04/all-about-searching-full-text-indexes_7.html

In this blog we shall see its implementation. Before you proceed you need to ensure that this feature is added to your instance and the full-text services are running.





We shall start by creating a full text catalog. If multiple filegroups are available we may specify the one on which it has to be created by the FileGroup clause If multiple filegroups are available we may specify the one on which it has to be created by the FileGroup clause

Through T-SQL


USE [FULLTEXT]
GO
CREATE FULLTEXT CATALOG [FullTextCatalog]
ON FILEGROUP [PRIMARY]
WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
AUTHORIZATION [dbo]
GO

Through Wizard


























Next we shall create the Full Text Index on our books table
GO
CREATE FULLTEXT INDEX ON [dbo].[Books]
( [Author] LANGUAGE English,
  [Title] LANGUAGE English,
  [Synopsis] LANGUAGE English
  )
KEY INDEX [pk_id] ON ([FullTextCatalog])
WITH (CHANGE_TRACKING AUTO, STOPLIST=NewStopList)
GO

Steps for creating the Index via the Wizard are as follows











Unlike Standard indexes we aren’t giving it any name. As SQL server allows only one Full-Text index per table the naming is taken care by SQL server. The Key Index clause specifies the unique non-null column which is mandatory when create full-text indexes.
















In the previous blog I’ve mentioned how population of the index depends on the type of Change_Tracking option selected.
When Auto is selected the changes are automatically tracked and updated by SQL server for the full-text indexed columns. 
When Manual is select the changes are tracked but the indexes aren’t updated unless the ALTER FULLTEXT INDEX  START UPDATE POPULATION is fired. This may be called through a SQL agent job as well. There is one more option which is not commonly used. 
It’s the Change_Tracking Off option when the changes are not tracked at all by SQL server. It’s available when creating full-text indexes in static tables.

























Here im creating my own StopList. This option is available under Database-> Storage -> Full Text StopList. I shall be building it from the system stop list and adding the word “describes” to StopList.






























Now we are ready with the index let’s start the querying


https://msdn.microsoft.com/en-us/library/hh245284.aspx
https://msdn.microsoft.com/en-us/library/cc879306.aspx

So coming back to the same example that we discussed in the previous blog I can now search for all the books that contain the character “Jeremy” in the synopsis as follows

SELECT * FROM [FULLTEXT].[dbo].[Books] where contains(Synopsis,N'Jeremy')

The Contains Predicate will search for the exact words or phrases that I pass. It also searches for proximity words & inflecting forms of the word. If you want more matches in terms of meaning of the words and not just the exact words we use the FreeText Predicate. It also allows for pattern matching with wildcards.


SELECT * FROM [FULLTEXT].[dbo].[Books] where freetext(*,'"new York*"')

This will result for all those records will also contain the records like new Yorker, new york in any of the columns

I could go on for providing further examples but these are very well described in the below links. 

Highly recommend that you go through these examples prior to implementing it in queries.

Books 

https://books.google.co.in/books?id=PgqEEPWRBzAC&pg=PA173&lpg=PA173&dq=full+text+indexing+sql+server+books&source=bl&ots=shtKT9rsl1&sig=C9yRRb4jbLFzcOLqIaAPWWK11gE&hl=en&sa=X&ved=0ahUKEwi2trLHr_7LAhVDto4KHUC1CKwQ6AEIUDAI#v=onepage&q=full%20text%20indexing%20sql%20server%20books&f=false

No comments:

Post a comment