Full text searching is the process of searching for words in a block of text. There are 2 aspects to full text indexers / searchers: Existence: means finding words that exist in the many blocks of texts stored (e.g. 'bob' is in the PDF documents stored). Oct 22, 2008 - Most full text search implementations use an 'inverted index'. This is an index where the keys are individual terms, and the associated values.
By: Updated: 2007-09-18 Related: ProblemI have heard about Full Text Search and I know it has been around for the last few versions of SQL Server. I have never really used it and I have seen some of the other tips related to the technology on your web site, so I assume someone must be using it. Can you provide any use cases for Full Text Search? I think I understand the general premise, but not sure if it is any quicker than just using a LIKE statement or how I would need to change my queries.SolutionYes - You are correct, Full Text Search has been an available feature in the last few versions of SQL Server and does provide another means of data access. In general, you need to setup a new catalog for table and column you would like to use and then change some of the syntax in your queries to use the Full Text Catalog.
Here is some information to get you started:Use Case for Full Text SearchOn a recent project, one of the needs was to search by a few different sets of criteria which were in a number of different tables with a number of one to many relationships. Most of the tables had millions of rows of data with the largest table having over 12 million rows. The data was primarily read-only and updated on a monthly basis. Unfortunately, all of the queries had to access the 12 million row table with 1 to 5 joins to the child tables and 1 to 5 WHERE clauses in the queries. Creation - To create a new Full Text catalog navigate to root Databases AdventureWorks (Database Name) Storage Full Text Catalogs.
To create the catalog, right click on the Full Text Catalogs folder and select the 'New Full-Text Catalog.' Option.On the New Full-Text Catalog interface the following settings should be configured:. Full-text catalog name. Catalog location. Filegroup.
Owner. Accent SensitivityCorresponding ScriptUSE AdventureWorksGOCREATE FULLTEXT CATALOG zProduction.ProductIN PATH N 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData'WITH ACCENTSENSITIVITY = ONAUTHORIZATION dboGOConfiguration - To configure the Full Text Catalog (tables, columns, schedule, etc) navigate to root Databases AdventureWorks (Database Name) Storage Full Text Catalogs Catalog Name (i.e. ZProduction.Product in our example) and select the 'Properties' option.On the Full Text Catalog Properties interface the following settings should be configured:. General configurations. Tablesviews with the corresponding columns.
Population ScheduleGeneral Page - This interface corresponds primarily to the catalog that was setup in the previous steps, with the ability to indicate if the catalog is the default, the catalog owner and if the catalog is accent sensitive.Tables/Views Page - This page provides the opportunity to determine the tables, columns to include in the Full Text Catalog. Although multiple tables and columns could be included. In the lower portion of the interface, it is important to note the unique index for each table, the language and the track changes setting. For more information about the track changes setting, reference the Rebuilding the Full Text Catalog section below.Population Schedule -Corresponding ScriptUSE AdventureWorksGOCREATE FULLTEXT INDEX ON Production.
Product KEY INDEX PKProductProductID ON zProduction.Product WITH CHANGETRACKING AUTOGOUSE AdventureWorksGOALTER FULLTEXT INDEX ON Production. Product ADD ( Name )GOUSE AdventureWorksGOALTER FULLTEXT INDEX ON Production. Product ENABLEGOUSE msdbGODECLARE @jobId BINARY ( 16 )EXEC msdb.
Spaddjob @jobname = N 'Start Optimize Catalog Population on AdventureWorks.zProduction.Product',@enabled = 1,@startstepid = 1,@description = N 'Scheduled full-text optimize catalog population for full-text catalog zProduction.Product in database AdventureWorks. This job was created by the Full-Text Catalog Scheduling dialog or Full-Text Indexing Wizard.' ,@categoryname = N 'Full-Text', @jobid = @jobId OUTPUTselect @jobIdGOEXEC msdb.
Spaddjobserver @jobname = N 'Start Optimize Catalog Population on AdventureWorks.zProduction.Product', @servername = N 'JTKLAPTOP'GOUSE msdbGODECLARE @scheduleid intEXEC msdb. Spaddjobschedule @jobname = N 'Start Optimize Catalog Population on AdventureWorks.zProduction.Product', @name = N 'zProduction.Product Full Text Catalog ',@enabled = 1,@freqtype = 4,@freqinterval = 1,@freqsubdaytype = 1,@freqsubdayinterval = 0,@freqrelativeinterval = 0,@freqrecurrencefactor = 1,@activestartdate = 20070924,@activeenddate = 99991231,@activestarttime = 20000,@activeendtime = 235959,@scheduleid = @scheduleid OUTPUTselect @scheduleidGOUSE msdbGOEXEC msdb. Product ID and Product Name SelectionUSE AdventureWorks;GOSELECT ProductID, NameFROM Production. ProductWHERE CONTAINS ( Name, '.washer.' OR '.ball.' );GODescription SelectionUSE AdventureWorks;GOSELECT ProductDescriptionID, DescriptionFROM Production.
ProductDescriptionWHERE CONTAINS ( Description, '.technology.' OR '.performance.' );GORebuilding the Full Text CatalogOne item to keep in mind is that the Full Text Catalogs need to be rebuilt automatically, on a schedule or on an as needed basis in order to include all of the updated data since the initial population. Please reference the options below to rebuild the Full Text Catalogs based on the Tables/Views Page interface shown above:. Automatic - The data in the full-text index is automatically updated as the data in the corresponding table is inserted, updated or deleted. Manual - In this scenario, when the indexed data is inserted, updated or deleted, SQL Server will track the change, but not update the index as is the case with the automatic option.
A SQL Server Agent job needs to run in order to update the index. Do not track changes - When the indexed data is inserted, updated or deleted, SQL Server will not track the change and the index must be rebuilt to reflect all of the underlying data changes.Each of these options need to be understood in terms of data availability versus performance. If the data in the Full Text catalog always needs to be up to date at any cost, then use the automatic setting. If the data changes need to be recorded, but not update the Full Text indexes then the manual setting should be used.
This setting should balance some of the performance needs by rebuilding the Full Text indexes during a low usage period. If the data is updated in bulk on a regular basis, then not tracking changes is probably appropriate with the practice that the Full Text index will need to be updated following the bulk loading of the data.Next Steps. The next time you are faced with some complex search criteria or high performance searching, consider Full Text Catalogs as an option to meet your needs. Validate that the Full Text Catalogs are the best solution as compared to the T-SQL alternatives available.
So be sure to build some test cases and conduct some testing to properly validate the results across all of the test cases. For additional information about Full Text Catalogs, check out the following:. Stay tuned for more tips on additional options to query the Full Text catalogs.Last Updated: 2007-09-18. Post a comment or let the author know this tip helped.All comments are reviewed, so stay on subject or we may delete your comment.
Note: your email address is not published. Required fields are marked with an asterisk (.).Name.EmailEmail me updates. NOTE. If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.Signup for our newsletterI agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the and understand I may unsubscribe at any time.