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
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
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
Through Wizard
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.
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
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.
This will result for all those records will also contain the records like new Yorker, new york in any of the columns
USE [FULLTEXT]
GO
CREATE FULLTEXT CATALOG [FullTextCatalog]
ON FILEGROUP [PRIMARY]
WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
AUTHORIZATION [dbo]
GO
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
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.
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.
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
No comments:
Post a Comment